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

[Martin Evans]

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