Technical Blog Post
Abstract
Tech Tip: IBM Universal JDBC Driver behavior when calling ResultSetMetaData.getColumnName and getColumnLabel against a DB2 LUW server
Body
The following examples show the values that are returned for IBM Data Server Driver for JDBC and SQLJ Version 4.0, and for previous JDBC drivers, when the useJDBC4ColumnNameAndLabelSemantics property is not set. The SELECT query contains an AS CLAUSE, which defines a label for a column in the result set. The column is a valid one and the same select query runs successfully from Database Server Command Line Processor (CLP)
1) Example of ResultsetMetaData.getColumnName behavior with JDBC 4.0 specification driver.
When an application using JDBC 4.0 driver issues a select statement using AS clause and getColumnName, -4460 is received as seen below:
<JCC trace snippet>
[jcc][Connection@51275127] Database product name: DB2/AIX64
[jcc][Connection@51275127] Database product version: SQL09074
:
:
[jcc][Time:2011-10-07-13:08:14.843][Thread:main][Connection@51275127] prepareStatement ( SELECT PO.DW_POSITION_ID AS DW_TRADE_HIER_ID, PO.DW_PRODUCT_ID AS DW_PRODUCT_ID FROM TARGET_Z.POSITION_D PO WHERE PO.SOURCE_SYSTEM_POSITION_ID =15117515) called
[jcc][Time:2011-10-07-13:08:14.863][Thread:main][Connection@51275127] prepareStatement () returned com.ibm.db2.jcc.am.nn@5cb15cb1
:
:
[jcc][Time:2011-10-07-13:08:16.530][Thread:main][ResultSet@79007900] next () returned true
[jcc][SystemMonitor:stop] core: 0.7829919999999999ms | network: 0.0ms | server: 0.0ms
[jcc][Time:2011-10-07-13:08:16.531][Thread:main][ResultSet@79007900] getInt (DW_POSITION_ID) called
[jcc] BEGIN TRACE_DIAGNOSTICS
[jcc][Thread:main][SQLException@42d442d4] java.sql.SQLException[jcc][Thread:main][SQLException@42d442d4] SQL state = null
[jcc][Thread:main][SQLException@42d442d4] Error code = -4460
[jcc][Thread:main][SQLException@42d442d4] Message = [jcc][10150][10300][4.12.55] Invalid parameter: Unknown column name DW_POSITION_ID. ERRORCODE=-4460, SQLSTATE=null
[jcc][Thread:main][SQLException@42d442d4] Stack trace follows
com.ibm.db2.jcc.am.SqlException: [jcc][10150][10300][4.12.55] Invalid parameter: Unknown column name DW_POSITION_ID. ERRORCODE=-4460, SQLSTATE=null
at com.ibm.db2.jcc.am.hd.a(hd.java:660)
at com.ibm.db2.jcc.am.hd.a(hd.java:60)
at com.ibm.db2.jcc.am.hd.a(hd.java:103)
at com.ibm.db2.jcc.am.ib.a(ib.java:1911)
at com.ibm.db2.jcc.am.bn.a(bn.java:1803)
at com.ibm.db2.jcc.am.bn.getInt(bn.java:1568)
at alias.main(alias.java:57)
[jcc] END TRACE_DIAGNOSTICSNote: From the above trace, we see
Columnname: DW_POSITION_ID
Columnlabel: DW_TRADE_HIER_ID
getInt(PO.DW_POSITION_ID) i.e getColumnName is called
2) Example of ResultsetMetaData.getColumnLabel with JDBC 3.0 specification driver
When an application using JDBC 3.0 driver issues a select statement using AS clause and getColumnLabelName, column label is returned, hence no error.
<JCC trace snippet>:
([jcc][Time:2011-10-07-13:08:14.843][Thread:main][Connection@51275127] prepareStatement ( SELECT PO.DW_POSITION_ID AS DW_TRADE_HIER_ID, PO.DW_PRODUCT_ID AS DW_PRODUCT_ID FROM TARGET_Z.POSITION_D PO WHERE PO.SOURCE_SYSTEM_POSITION_ID =15117515) called
[jcc][Time:2011-10-07-13:08:14.863][Thread:main][Connection@51275127] prepareStatement () returned com.ibm.db2.jcc.am.nn@5cb15cb1)
:
:
[jcc][Time:2011-10-07-13:07:35.062][Thread:main][ResultSet@74d274d2] getInt (DW_TRADE_HIER_ID) called
[jcc][Time:2011-10-07-13:07:35.062][Thread:main][ResultSet@74d274d2] getInt (1) called
[jcc][Time:2011-10-07-13:07:35.062][Thread:main][ResultSet@74d274d2] getInt () returned 201504
Note: From the above trace, we see that getInt(DW_TRADE_HIER_ID) i.e getColumnLabel is called.
Conclusion: From the above examples, we see that 3.62.56 uses the label name getInt (DW_TRADE_HIER_ID) and 4.12.55 actually uses the column name getInt (DW_POSITION_ID). It is expected that JCC throws an exception when using getColumnName with JDBC 4.0 driver. If users want to use getColumnName instead of getColumnLabel with JDBC 4.0 driver, useJDBC4ColumnNameAndLabelSemantics JDBC property need to be set to false, this should fix the -4460 error.
UID
ibm11141684