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

[Martin Evans]

Bartolomeo Nicolotti wrote:
> 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
> 

Just a couple more things I don't recollect you telling us yet. Is this 
a 64 bit platform? Have you overloaded your procedure?

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> 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
>>
>>
>