CLI0165E

[Greig]

I'm using SPSS to query a DB2 database and I get this error:

SQLFetch (getting tables) failed :[IBM][CLI Driver] CLI0165E Error in
row. SQLSTATE=01S01

The weird thing is that it looks like I'm getting all the data that I
should be and that the query worked fine. I'm not quite sure why I'd
get this error if there were really nothing wrong.

The explanation I've found for this error is this:
An error occurred while fetching one or more rows. (Function returns
SQL_SUCCESS_WITH_INFO.)

Not very helpful. Can anyone help me figure out the possible causes
for this?

Thanks,
Greig

[Greig]

Nevermind. I just figured out that SPSS apparently throws that error
if a NULL value is returned. I would have never expected that. Seems
kinda silly actually. Oh well, at least I know now.

[Helmut Tessarek]

In this case this is a bug in SPSS. The DB2 CLI call is apparently missing the
pointer to the variable in the function call.

SQLRETURN SQLBindCol (
SQLHSTMT StatementHandle, /* hstmt */
SQLUSMALLINT ColumnNumber, /* icol */
SQLSMALLINT TargetType, /* fCType */
SQLPOINTER TargetValuePtr, /* rgbValue */
SQLLEN BufferLength, /* dbValueMax */
SQLLEN *StrLen_or_IndPtr); /* *pcbValue */

I assume that in the SPSS code, the call looks like this:
cliRC = SQLBindCol(hstmt, 1, SQL_C_SHORT, &deptnumb.val, 0, NULL);
instead of:
cliRC = SQLBindCol(hstmt, 1, SQL_C_SHORT, &deptnumb.val, 0, &deptnumb.ind);

Explanation:
The variable that the StrLen_or_IndPtr argument refers to is used for both
indicator and length information. If a fetch encounters a null value for the
column, it stores SQL_NULL_DATA in this variable; otherwise, it stores the
data length in this variable. Passing a null pointer as StrLen_or_IndPtr keeps
the fetch operation from returning the data length, but makes the fetch fail
if it encounters a null value and has no way to return SQL_NULL_DATA.

On 27.01.12 17:06 , Greig wrote:
> Nevermind. I just figured out that SPSS apparently throws that error
> if a NULL value is returned. I would have never expected that. Seems
> kinda silly actually. Oh well, at least I know now.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab