[unixODBC-support] inputOutput parameter problem in calling stored Procedure

[Bartolomeo Nicolotti]

Hi,

yeeeees, you were really right! It was this 

The only thing I see that looks wrong is the call to SQLBindParameter 
where you are passing SQL_NTS as the length but SQL_NTS is not a 
SQLLEN*. I think that should be:

SQLLEN param_length;
param_length = SQL_NTS;

now it works!!!!

bart at LxPC37:~/download/software/mono/src/test$ ./helloODBCPrep Dunkel siapbn
********
Here1!!
Here2!!
Here2!!
KU        	INFOTECH  	siap                
Success!


Now I'm going to try with the stored procedure...

many thanks

best regards


Martin Evans-4 wrote:
> 
> Bartolomeo Nicolotti wrote:
>> Sorry, but I'm also getting lost in this task of using unixodbc to
>> accesse
>> iseries db2. I'd really hope to be able to do prepared statemts with IBM
>> driver for linux and ODBC. Otherwise I'll have to install Visual Studio
>> and
>> Windows.
>> 
>> To understand where's the problem I wanted to try with plain C program.
>> In the previous message I attached  an helloODBC.c C program taken from
>> IBM
>> red book
>> 
>> http://www.redbooks.ibm.com/abstracts/sg246551.html
>> 
>> It works, doing the select
>> 
>> SELECT * FROM VIAGGIFAM.PWKCXM WHERE WKXSCX='KU'
>> 
>> which as you can see is has no parameters, and the method used is 
>> 
>>   /* Do query */
>>   if ((sqe = SQLExecDirect (statement, querystring, SQL_NTS)))
>>      die ("exec direct", sqe);
>> 
>>  and it fetches the results with no errors.
>> 
>> Then I saved the C source with a new name 
>> http://www.nabble.com/file/p22052714/helloODBCPrep.c helloODBCPrep.c  and
>> modified it to use a prepared statement to do the same select, i.e.
>> 
>> SELECT * FROM VIAGGIFAM.PWKCXM WHERE WKXSCX=?
>> 
>> with the parameter value set with
>> ...
>> char param1[10]= "KU";
>> ...
>>   if( sqe= SQLBindParameter(
>> 	     /*SQLHSTMT     StatementHandle*/ statement,
>> 	     /*SQLUSMALLINT     ParameterNumber*/ 1,
>> 	     /*SQLSMALLINT     InputOutputType*/ SQL_PARAM_INPUT,
>> 	     /*SQLSMALLINT     ValueType*/ SQL_C_CHAR,
>> 	     /*SQLSMALLINT     ParameterType*/ SQL_CHAR,
>> 		  /*SQLULEN     ColumnSize*/ 50,
>> 	     /*SQLSMALLINT     DecimalDigits*/ 0,
>> 	     /*SQLPOINTER     ParameterValuePtr*/ param1,
>> 	     /*SQLINTEGER     BufferLength*/ 0,
>> 		  /*SQLLEN *     StrLen_or_IndPtr*/ SQL_NTS)){
>>      die ("bind:", sqe);
>>   }
>> 
>> This new program has a segmentation fault in SQLExecute, as I can see
>> from
>> unixodbc log
>> 
>> bart at LxPC37:~/download/software/mono/src/test$ ./helloODBCPrep Dunkel
>> siapbn
>> *******
>> Here1!!
>> Here2!!
>> Segmentation fault
>> 
>> I've switched on unixodbc log and I got:
>> 
>> <pre>
>> 
>> ODBC14289http://SQLPrepare.c189
>> Entry:
>> Statement = 0x80690e0
>> SQL = http://SELECT * FROM VIAGGIFAM.PWKCXM WHERE WKXSCX=?length = 45
>> (SQL_NTS)
>> ODBC14289http://SQLPrepare.c364
>> Exit:SQL_SUCCESS
>> ODBC14289http://SQLBindParameter.c193
>> Entry:
>> Statement = 0x80690e0
>> Param Number = 1
>> Param Type = 1
>> C Type = 1 SQL_C_CHAR
>> SQL Type = 1 SQL_CHAR
>> Col Def = 2
>> Scale = 0
>> Rgb Value = 0x80491ae
>> Value Max = 2
>> StrLen Or Ind = 0xfffffffd
>> ODBC14289http://SQLBindParameter.c339
>> Exit:SQL_SUCCESS
>> ODBC14289http://SQLExecute.c183
>> Entry:
>> Statement = 0x80690e0
>> 
>> 
>> I think that there's something missing as I couldn't think that the
>> driver
>> is not able to do prepared statements, because we use prepared statements
>> with no proble from java/jdbc both for sql statement and stored
>> procedures.
> 
> No one is saying the driver cannot do prepare statements but just 
> because something works in JDBC does not mean it will work in ODBC - 
> they are 2 totally different drivers.
> 
>> If I can understand why i got a segmentation fault in SQLExecute maybe I
>> can
>> find a solution.
>> 
>> If you can give any help it would be really appreciated
>> 
>> many thanks 
>> 
>> Best regards
> 
> The only thing I see that looks wrong is the call to SQLBindParameter 
> where you are passing SQL_NTS as the length but SQL_NTS is not a 
> SQLLEN*. I think that should be:
> 
> SQLLEN param_length;
> param_length = SQL_NTS;
> 
>   if( sqe= SQLBindParameter(
> 	     /*SQLHSTMT     StatementHandle*/ statement,
> 	     /*SQLUSMALLINT     ParameterNumber*/ 1,
> 	     /*SQLSMALLINT     InputOutputType*/ SQL_PARAM_INPUT,
> 	     /*SQLSMALLINT     ValueType*/ SQL_C_CHAR,
> 	     /*SQLSMALLINT     ParameterType*/ SQL_CHAR,
> 		  /*SQLULEN     ColumnSize*/ 50,
> 	     /*SQLSMALLINT     DecimalDigits*/ 0,
> 	     /*SQLPOINTER     ParameterValuePtr*/ param1,
> 	     /*SQLINTEGER     BufferLength*/ 0,
> 		  /*SQLLEN *     StrLen_or_IndPtr*/ &param_length)){
>       die ("bind:", sqe);
>    }
> 
> IIRC, SQL_NTS is -3 so passing SQL_NTS as the last argument to 
> SQLBindParameter would cause the driver to read address -3 which could 
> cause a problem.
> 
> Martin
> -- 
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
> _______________________________________________
> unixODBC-support mailing list
> unixODBC-support at mailman.unixodbc.org
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
> 
> 

-- 
View this message in context: http://www.nabble.com/inputOutput-parameter-problem-in-calling-stored-Procedure-tp22002537p22053147.html
Sent from the unixODBC - Support mailing list archive at Nabble.com.