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

Martin Evans martin.evans at easysoft.com
Mon Feb 16 17:26:53 GMT 2009


Bartolomeo Nicolotti wrote:
> I've installed UnixODBC and iseriesAccess for linux (5.4) to connect to our
> iseries.
> 
> I've compiled a c program,  http://www.nabble.com/file/p22041161/helloODBC.c
> helloODBC.c ,  and it works.
> 
> When I modify it to use prepared statement, as follows, I get a segmentation
> fault.
> 
> 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
> 
> 
> /* helloODBCPrep
> Simple ODBC example for Linux
> Arguments: datasource (name of your System or User DSN)
> UserID
> password
> Example: ODBCexample datasource UserID password
> Rochester, MN, July 2005 */
> #include <sql.h>
> #include <sqlext.h>
> #include <sqltypes.h>
> #include <stdio.h>
> #include <stdlib.h>
> #include <string.h>
> 
> /* Simple query to DB table very likely to exist. Takes first i5/OS "member"
> */
> char querystring[] = "SELECT * FROM VIAGGIFAM.PWKCXM WHERE WKXSCX=?";
> char param1[]= "KU";
> 
> void
> die (char *reason, SQLRETURN code)
> {
> if (code != 0)
> {
> printf ("Error code: %d\n", code);
> }
> printf ("Failure because: %s\n", reason);
> exit (1);
> }
> int
> main (int argc, char *argv[])
> {
> #define MAX_CONNECTIONS 5
> char field1255; /*receives values from table row fetch */
> char field2255;
> char field3255;

Looks like these declarations are too small for the retrieved data which 
you later say are buffers of 255 chrs:

I'm a bit confused really as to how this code compiles as you later 
refer to field1, field2 and field3 but they are not defined. I presume 
this is something which has happened during cut and paste.

> SQLINTEGER retfld1;
> /* For completelness. */
> SQLINTEGER retfld2; /* For this example, NULL for SQLBindCol */
> SQLINTEGER retfld3; /* would be enough */
> SQLHENV henv;
> SQLHDBC hdbcMAX_CONNECTIONS; /* For multiple connections */
> SQLHSTMT statement;
> SQLRETURN sqe = 0;
> SQLRETURN res = 0;
> /* allocate environment */
> if (SQLAllocEnv (&henv))
> die ("Alloc Environment", 0);
> /* allocate connection */
> if ((sqe = SQLAllocConnect (henv, &hdbc[0])))
> die ("Allocate Connect", sqe);
> /* connect to data base */
> if ((sqe = SQLConnect (hdbc[0], argv[1], SQL_NTS,
> argv[2], SQL_NTS, argv[3], SQL_NTS)))
> die ("Connect", sqe);
> /* allocate statement(s) */
> if (SQLAllocStmt (hdbc[0], &statement))
> die ("Alloc stmt", 0);
> /*
> SQLRETURN SQLPrepare(
> SQLHSTMT StatementHandle,
> SQLCHAR * StatementText,
> SQLINTEGER TextLength
> 
> */
> if( sqe= SQLPrepare( /* SQLHSTMT StatementHandle*/ statement,
> /*SQLCHAR * StatementText*/ (SQLCHAR *) querystring,
> /*strlen( querystring )*/ SQL_NTS)){
> die ("prepare:", sqe);
> }
> 
> /*
> SQLRETURN SQLBindParameter(
> SQLHSTMT StatementHandle,
> SQLUSMALLINT ParameterNumber,
> SQLSMALLINT InputOutputType,
> SQLSMALLINT ValueType,
> SQLSMALLINT ParameterType,
> SQLULEN ColumnSize,
> SQLSMALLINT DecimalDigits,
> SQLPOINTER ParameterValuePtr,
> SQLINTEGER BufferLength,
> SQLLEN * StrLen_or_IndPtr);
> */
> 
> printf("Here1!!\n");
> 
> 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*/ 2,
> /*SQLSMALLINT DecimalDigits*/ 0,
> /*SQLPOINTER ParameterValuePtr*/ param1,
> /*SQLINTEGER BufferLength*/ 2,
> /*SQLLEN * StrLen_or_IndPtr*/ SQL_NTS)){
> die ("bind:", sqe);
> }
> 
> printf("Here2!!\n");
> 
> /* Do query */
> /*
> SQLRETURN SQLExecute(
> SQLHSTMT StatementHandle);
> */
> if ((sqe = SQLExecute (statement)))
> die ("exec direct", sqe);
> 
> printf("Here2!!\n");
> 
> /* Bind coming SQLFetch operations to fields */
> if ((sqe = SQLBindCol (statement, 1, SQL_CHAR, field1, 255, &retfld1)))
> die ("Bind field 1", sqe);

But field1 is a char1 not char 255.

> if ((sqe = SQLBindCol (statement, 2, SQL_CHAR, field2, 255, &retfld2)))
> die ("Bind field 2", sqe);

as above

> if ((sqe = SQLBindCol (statement, 3, SQL_CHAR, field3, 255, &retfld3)))
> die ("Bind field 3", sqe);

as above

> /* While loop to fetch all records */
> res = SQLFetch (statement);
> /* field1,2,3 take new values each time */
> while (SQL_SUCCEEDED (res))
> { /* Create tab separated values */
> printf ("%s\t%s\t%s\n", field1, field2, field3);
> res = SQLFetch (statement); /* next record */
> }
> /* end actual processing */
> /* free statement(s) pretty completely */
> if (SQLFreeStmt (statement, SQL_DROP))
> die ("Free Statement with Drop", 0);
> /* close connection */
> if ((sqe = SQLDisconnect (hdbc[0])))
> die ("Disconnect ", sqe);
> if ((sqe = SQLFreeConnect (hdbc[0])))
> die ("Connection closed", sqe);
> /* free environment */
> if (SQLFreeEnv (henv))
> die ("Free Environment", 0);
> printf ("Success!\n");
> return 0;
> }
>

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


More information about the unixODBC-support mailing list