IBM Support

Tech Tip: IBM Universal JDBC Driver behavior when calling ResultSetMetaData.getColumnName and getColumnLabel against a DB2 LUW server

Technical Blog Post


Abstract

Tech Tip: IBM Universal JDBC Driver behavior when calling ResultSetMetaData.getColumnName and getColumnLabel against a DB2 LUW server

Body

For the IBM® Data Server Driver for JDBC and SQLJ version 4.0 and later, the default behavior of ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnLabel differs from the default behavior for earlier JDBC drivers. You can use the useJDBC4ColumnNameAndLabelSemantics property to change this behavior.

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_DIAGNOSTICS
 
Note: 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.

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141684