Search code examples
db2booleandb2-luwodbc

IBM DB2 LUW BOOLEAN and CLI SQLBindParameter for NULL

IBM DB2 LUW 11.1.1.1 introduced the BOOLEAN SQL data type.

Using IBM CLI, I want to pass an SQL parameter as NULL to insert a row in a table with BOOLEAN column, by using the SQLBindParameter() function.

I am using same ODBC calls, flags and buffers as with MS ODBC SQL Server and SAP HANA ODBC.

When using non-NULL values, I can insert TRUE/FALSE in the BOOLEAN column.

Inserting TRUE/FALSE values into my BOOLEAN col works fine when binding:

  • 1/0 with SQL_C_SHORT + SQL_SMALLINT

or:

  • '1'/'0' with SQL_C_CHAR + SQL_CHAR

But when setting the StrLen_or_IndPtr indicator to SQL_NULL_DATA, I get the following error at statement execution:

[IBM][CLI Driver] CLI0164E Nullable type out of range. SQLSTATE=HY099

I don't understand why...

Any clue?

Here some SQL to illustrate BOOLEAN type usage with DB2:

db2 => create table t1 ( pk int, bl boolean ) 
DB20000I  The SQL command completed successfully.
db2 => insert into t1 values ( 101, TRUE )
DB20000I  The SQL command completed successfully.
db2 => insert into t1 values ( 102, FALSE )
DB20000I  The SQL command completed successfully.
db2 => insert into t1 values ( 103, NULL )
DB20000I  The SQL command completed successfully.
db2 => select * from t1 where bl IS NULL
PK          BL
----------- --
        103 -
   1 record(s) selected.
db2 => select * from t1 where bl
PK          BL
----------- --
        101  1
  1 record(s) selected.
db2 => select * from t1 where bl is true
PK          BL
----------- --
        101  1
  1 record(s) selected.
db2 => select * from t1 where bl is false
PK          BL
----------- --
        102  0
  1 record(s) selected.
Solution

  • IBM support answered that the bug is known and is fixed in APAR IT30675 Problem solved.