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

Bartolomeo Nicolotti bnicolotti at siapcn.it
Tue Feb 17 09:25:37 GMT 2009


Hi,

I've tried with a C program 
http://www.nabble.com/file/p22053497/helloODBCPrepStoredP.c
helloODBCPrepStoredP.c  that calls a stored procedure (better and RPG
program on iseries) and I got:

bart at LxPC37:~/download/software/mono/src/test$ ./helloODBCPrepStoredP Dunkel
siapbn siapbn75
Error code: -1
Failure because: prepare:

and from unixodbc log.

[ODBC][11389][SQLPrepare.c][189]
		Entry:            
			Statement = 0x80690e0            
			SQL = [CALL GIANLUCA.PROVACS(?)][length = 24 (SQL_NTS)]
[ODBC][11389][SQLPrepare.c][364]
		Exit:[SQL_ERROR]
		DIAG [S0002] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - PROVACS
in GIANLUCA di tipo *N non trovato.

The message, is the same as with mono, and in the iseries slang means that
it searches for a procedure with no parameters (*N), and it doesn't find it
as the procedure PROVACS has been built for test purpose only, has 1
parameter of type char and it's not overloaded.

I think that the driver lacks the possibility of doing prepared statements
that call stored procedures, or there's some other way of doing this.
My pc is 32 bit, the iseries system uses ebcdic and the number of bits on
iseries is not so easy to know, I think 48bits, yes the iseries is a strange
world...

Many thanks

Best regards. 


Martin Evans-4 wrote:
> 
> 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
>>>
>>>
>> 
> _______________________________________________
> 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-tp22002537p22053497.html
Sent from the unixODBC - Support mailing list archive at Nabble.com.



More information about the unixODBC-support mailing list