英文:
IBM DB2 LUW BOOLEAN and CLI SQLBindParameter for NULL
问题
IBM DB2 LUW 11.1.1.1引入了BOOLEAN SQL数据类型。
使用IBM CLI，我想要将一个SQL参数传递为NULL，以在具有BOOLEAN列的表中插入一行，使用SQLBindParameter()函数。
我正在使用与MS ODBC SQL Server和SAP HANA ODBC相同的ODBC调用、标志和缓冲区。
当使用非NULL值时，我可以在BOOLEAN列中插入TRUE/FALSE。
在绑定时，将TRUE/FALSE值插入我的BOOLEAN列正常运行：
- 使用SQL_C_SHORT + SQL_SMALLINT的1/0
或：
- 使用SQL_C_CHAR + SQL_CHAR的'1'/'0'
但是，当将StrLen_or_IndPtr指示器设置为SQL_NULL_DATA时，在执行语句时出现以下错误：
[IBM][CLI Driver] CLI0164E 可空类型超出范围。SQLSTATE=HY099
我不明白为什么会出现这个错误...
有什么线索吗？
这里是一些SQL示例，说明了在DB2中使用BOOLEAN类型：
db2 => create table t1 ( pk int, bl boolean )
DB20000I SQL命令成功完成。
db2 => insert into t1 values ( 101, TRUE )
DB20000I SQL命令成功完成。
db2 => insert into t1 values ( 102, FALSE )
DB20000I SQL命令成功完成。
db2 => insert into t1 values ( 103, NULL )
DB20000I SQL命令成功完成。
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.
英文:
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.
答案1
得分: 0
我们已经安装了11.5.0.0版本的客户端和服务器。
我可以用一个简单的CLI程序重现这个问题。
似乎与延迟准备语句选项有关！
在使用SQL_DEFERRED_PREPARE_OFF时，我收到了CLI0164E错误：
rcode = SQLSetStmtAttr(m_hstmt, SQL_ATTR_DEFERRED_PREPARE,
(SQLPOINTER) SQL_DEFERRED_PREPARE_OFF, 0);
当不使用这个选项时，INSERT语句会执行，可以插入NULL。
非常奇怪。
所以是的，这看起来像是一个DB2客户端的bug...
我们会在IBM开启一个支持案例。
英文:
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.
答案2
得分: 0
IBM支持回答说这个错误已知，并已在APAR IT30675中修复
问题已解决。
英文:
IBM support answered that the bug is known and is fixed in APAR IT30675
Problem solved.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论