Abstract:
-----------
The getColumnType method returns wrong datatype value for
columns defined with user defined distinct datatypes.
Problem description
------------------------
The getColumnType API when used against a user-defined distinct
datatype, returns wrong type rather than the correct base
datatype value. The following error is received:
ERROR ->[IBM][JDBC Driver] CLI0606E The returned value from a
column is incompatible with the data type corresponding to the
get*** method. SQLSTATE=22005
The SQL_ATTR_RETURN_USER_DEFINED_TYPES attribute is set to
SQL_TRUE, so the descriptor field SQL_DESC_TYPE will no longer
return the "base" SQL type of the UDT, that is, the SQL type
that the UDT is based on.
Rootcause for this problem:
---------------------------------
The issue which is causing the error "CLI0606E - InCompatible
datatype" is due to SQL_ATTR_RETURN_USER_DEFINED_TYPES is set to
TRUE in JDBC code, which should not be set as it would not
return the base datatype value.
When the SQL_ATTR_RETURN_USER_DEFINED_TYPES attribute is set to
SQL_TRUE, the descriptor field SQL_DESC_TYPE will no longer
return the "base" SQL type of the UDT, that is, the SQL type
that the UDT is based on or transforms to.
Please refer:
(
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.i
bm.db2.luw.apdv.cli.doc/doc/c0007960.html )
Environment
---------------
This issue can be reproduced on Linux and AIX platforms. It
occurs both in DB2 v8, v91 and v95 builds. But not consistently
reproducible and happens when using both JDK version 1.4.0 and
1.4.2.
Diagnostic information
---------------------------
The JDBC trace which shows the error as follows,
jdbc.app.DB2ResultSetTrace -> getObject( 1 ) (2007-7-27
13:25:36.423)
| Statement handle = 1:1
| jdbc.app.DB2ResultSetTrace -> getColType( 1, true ) (2007-7-27
13:25:36.423)
| | Statement handle = 1:1
| | jdbc.app.DB2ResultSetTrace -> getColType( 1, true )
(2007-07-27 13:25:36.423)
| | | 10: sqlType = -450
| | | jdbc.app.DB2ResultSetMetaDataTrace -> getColumnType(1)
(2007-7-27 13:25:36.424)
| | | | Statement handle = 1:1
| | | | 30: DB2ColType = -450
| | | | 30: DB2ColBaseTypeNotAdjusted = -450
| | | | Return value = -450
| | | jdbc.app.DB2ResultSetMetaDataTrace <- getColumnType - Time
Elapsed = 0.0 (2007-7-27 13:25:36.424)
| | | 20: sqlType = -450
| | | 20: returnLen = 15
| | jdbc.app.DB2ResultSetTrace <- getColType() returns -450
[Time Elapsed = 0.0020] (2007-07-27 13:25:36.424)
| jdbc.app.DB2ResultSetTrace <- getColType - Time Elapsed =
0.0010 (2007-7-27 13:25:36.424)
| jdbc.app.DB2ResultSetTrace -> getObject( 1, -450 ) (2007-7-27
13:25:36.424)
| | Statement handle = 1:1
| | jdbc.app.DB2ResultSetTrace -> getObject( 1, -450, -1 )
(2007-7-27 13:25:36.425)
| | | Statement handle = 1:1
| | | jdbc.DB2Exception -> DB2Exception() (2007-07-27
13:25:36.425)
| | | | 10: SQLError = [IBM][JDBC Driver] CLI0606E The returned
value from a column is incompatible with the data type
corresponding to the get*** method. SQLSTATE=22005
| | | | SQLState = 22005
| | | | SQLNativeCode = -99999
| | | jdbc.DB2Exception <- DB2Exception() [Time Elapsed = 0.0]
(2007-07-27 13:25:36.425)
| | jdbc.app.DB2ResultSetTrace <- getObject - Time Elapsed = 0.0
(2007-7-27 13:25:36.425)
| jdbc.app.DB2ResultSetTrace <- getObject - Time Elapsed = 0.0
(2007-7-27 13:25:36.425)
jdbc.app.DB2ResultSetTrace <- getObject - Time Elapsed = 0.0020
(2007-7-27 13:25:36.425) |
