ODBC Drivers: DB2 OUPUT parameter VARCHAR

[Jim Kosloskey]

All,

I am invoking a DB2 (OS/390) stored procedure that is returning a field defined as:

[odbc SQLBindParameter $hstmt $bind_num SQL_PARAM_OUTPUT SQL_C_VARCHAR SQL_VARCHAR 800 0 pdsntiar 800 NULL]

Upon return from invoking the Stored Procedure I am getting the following error:

[DataDirect][ODBC DB2 Wire Protocol driver]Invalid character value. Error in parameter 35

Parameter 35 is the parameter defined above.

The DBA says there is a 2 byte binary length (for 800 the maximum length of the  and no actual data which he claims is the way DB2 (Mainframe) sends VARCHAR data.

The definition of the parmameter in the COBOL program on the mainframe is VARCHAR.

I am guessing the binary value is being treated as data and I need to bind my parameter differently. The DBA is of little help – just says ‘maybe DataDirect is no good – this works with JDBC’.

Anybody have any suggestion?

Thanks,

email: jim.kosloskey@jim-kosloskey.com

Topic

[Jim Kosloskey]

I solved the issue.

For binding the paramter in question I had:

[odbc SQLBindParameter $hstmt $bind_num SQL_PARAM_OUTPUT SQL_C_VARCHAR SQL_VARCHAR 800 0 pdsntiar 800 NULL]

It should have been:

[odbc SQLBindParameter $hstmt $bind_num SQL_PARAM_OUTPUT SQL_C_CHAR SQL_VARCHAR 800 0 pdsntiar 800 NULL]

Note SQL_C_CHAR instead of SQL_C_VARCHAR.

When SQL_C_VARCHAR is used with SQL_VARCHAR the ODBC drivers attempt to treat the field as a VARCHARLONG field which then tries to treat the provided 2 byte length field as data and that is an invalid character.

I think this is only an issue with the DB2 Z/OS-OS-390 driver but I have not verified that.

email: jim.kosloskey@jim-kosloskey.com

[Author]

Replies