IBM DB2 LUW BOOLEAN and CLI SQLBindParameter for NULL

Question

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.

Thanks! Seb

danny117, BOOLEAN columns in SQL can be NULL if you don't specify a NOT NULL constraint. — Seb
I have updated the question text with some SQL to illustrate this. — Seb

Seb Seb · Accepted Answer · 2020-01-07T17:28:47

We have 11.5.0.0 client and server installed.

I could reproduce with a simple CLI program.

It appears that it's related to the deferred prepare statement option!

I get the CLI0164E error when using SQL_DEFERRED_PREPARE_OFF:

rcode = SQLSetStmtAttr(m_hstmt, SQL_ATTR_DEFERRED_PREPARE, (SQLPOINTER) SQL_DEFERRED_PREPARE_OFF, 0);

When not using this option, the INSERT executes and NULL can be inserted.

Very strange.

So yes this looks like a DB2 client bug...

We'll open a support case at IBM.

IBM DB2 LUW BOOLEAN and CLI SQLBindParameter for NULL

2 Answers