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

Bartolomeo Nicolotti bnicolotti at siapcn.it
Mon Feb 16 16:59:02 GMT 2009


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;
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);
if ((sqe = SQLBindCol (statement, 2, SQL_CHAR, field2, 255, &retfld2)))
die ("Bind field 2", sqe);
if ((sqe = SQLBindCol (statement, 3, SQL_CHAR, field3, 255, &retfld3)))
die ("Bind field 3", sqe);
/* 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;
}

</pre>
Pages: 1 	
Go Back 	Back to Thread List
	Threads: [ Previous | Next ]
	

Point your RSS reader here for a feed of the latest messages in all forums

    * About IBM
    * Privacy
    * Contact
    * Terms of use




Nick Gorham-2 wrote:
> 
> Bartolomeo Nicolotti wrote:
> 
>>
>>  
>>
>>
>>[ODBC][11917][SQLPrepareW.c][126]
>>		Entry:            
>>			Statement = 0x83df158            
>>			SQL = [{CALL GIANLUCA.Provacs( ? )}][length = 28]
>>[ODBC][11917][SQLPrepareW.c][300]
>>		Exit:[SQL_ERROR]
>>		DIAG [42S02] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - PROVACS
>>in GIANLUCA di tipo *N non trovato.
>>
>>
>>the error means that the prepare is searching a PROVACS without parameters
>>
>>As I've already said the CALL GIANLUCA.PROVACS('AA') works.
>>
>>The problem is with the question mark ? that doesn't seem to be recognized
>>as a parameter. this is strange as from jdbc we use ? with no problem
>>
>>Do you know how can I use named parameters
>>
>>Many thanks 
>>
>>Best regards
>>
>>
>>  
>>
> Ok, I would suggest that the problem lies with the DB2 driver then, I 
> can't tell from the log if mono has previously bound a parameter, but 
> normally it should be allowed to call SQLPrepare before binding the 
> parameters. It may be a restriction of the driver you have. I would 
> suggest asking IBM if this is a restriction of their driver.
> 
> -- 
> Nick
> _______________________________________________
> 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-tp22002537p22041161.html
Sent from the unixODBC - Support mailing list archive at Nabble.com.



More information about the unixODBC-support mailing list