Concise guide to retrieving identity or serial column values in DB2, Sybase and Informix databases
 Technote (troubleshooting)
 
Problem(Abstract)
Most databases provide the ability to automatically generate a unique numeric value for each row in a table upon an insertion. For instance, with DB2® and Sybase, these columns are commonly called identity columns and with Informix®, these columns are called serial columns. This technote provides SQL and JDBC™ examples for obtaining the identity and serial columns from DB2, Sybase and Informix.
 
Resolving the problem
The following provides SQL and JDBC examples for obtaining the identity and serial columns from DB2, Sybase and Informix.
DB2
DB2 provides a simple query you can use to retrieve the value placed in the identity column.

The SQL:

select identity_val_local() as identity_val from sysibm.sysdummy1

The sample JDBC code:

// The identity value
long identity = 0;

// Get the connection from a previously looked up data
// source.
Connection conn = previousLookedUpDataSource.getConnection();

// Prepare the statement
PreparedStatement ps = conn.prepareStatement("insert into address values (?, ?, ?, ?)");

// Set the parameters in the prepared statement.
ps.setString(1, address.getStreet());
ps.setString(2, address.getCity());
ps.setString(3, address.getState());
ps.setString(4, address.getZip());

// Execute the prepared statement.
ps.execute();

// Close the prepared statement.
ps.close();

// Prepare the statement to retrieve the identity value
ps = conn.prepareStatement("select identity_val_local() as identity_val from sysibm.sysdummy1");

// Execute the query.
ResultSet rs = ps.executeQuery();

// Get the value of the identity.
if (rs.next()) {
identity = rs.getLong(1);
}

// Close result set, prepared statement and connection.
rs.close();
ps.close();
conn.close();


Sybase
Like DB2, Sybase provides a simple query that retrieves the value of the identity field of the last row inserted into the database.

The SQL:

select @@identity

The sample JDBC code:

// The identity value.
long identity = 0;

// Get the connection from a previously looked up data
// source.

Connection conn = previousLookedUpDataSource.getConnection();

// Prepare the statement.
PreparedStatement ps =
conn.prepareStatement("insert into address values (?, ?, ?, ?)");

// Set the parameters in the prepared statement.
ps.setString(1, address.getStreet());
ps.setString(2, address.getCity());
ps.setString(3, address.getState());
ps.setString(4, address.getZip());

// Execute the prepared statement
ps.execute();

// Close the prepared statement
ps.close();

// Prepare the statement to retrieve the identity value
ps = conn.prepareStatement("select @@identity");

// Execute the query
ResultSet rs = ps.executeQuery();

// Get the value of the identity
if (rs.next()) {
identity = rs.getLong(1);
}

// Close result set, prepared statement and connection.
rs.close();
ps.close();
conn.close();


Informix
Unlike DB2 and Sybase, retrieving the serial column (for example: identity column) value in Informix is more difficult because no SQL statement is provided. Instead, the Informix implementation of the JDBC Statement interface provides two methods to retrieve this information:
  • getSerial
  • getSerial16

WebSphere's relational resource adapter for Informix wraps the underlying com.informix.jdbc.IfmxStatement object with the com.ibm.ws.rsadapter.jdbc.WSJdbcStatement object, there the IfmxStatement is inaccessible directly (even using a cast). WebSphere® supplies the com.ibm.websphere.rsadapter.WSCallHelper class to provide access to the native implementation of java.sql.Statement as well as other interfaces.

The sample JDBC code:

// The identity value.
long serial = 0;

// Get the connection from a previously looked up data
// source.

Connection conn = previousLookedUpDataSource.getConnection();

// Prepare the statement.
PreparedStatement ps =
conn.prepareStatement("insert into address values (?, ?, ?, ?)");

// Set the parameters in the prepared statement.
ps.setString(1, address.getStreet());
ps.setString(2, address.getCity());
ps.setString(3, address.getState());
ps.setString(4, address.getZip());

// Execute the prepared statement
ps.execute();

// Get value of the serial column using the WSCallHelper
Object obj = WSCallHelper.jdbcCall(
null,
pStmt,
"getSerial",
new Object[0],
new Class[0]);
serial = ((Integer) obj).longValue();

// Alternatively, get value of the serial16 column using
// the WSCallHelper
Object obj = WSCallHelper.jdbcCall(
null,
pStmt,

"getSerial16",
new Object[0],
new Class[0]);
serial = ((Long) obj).longValue();

// Close result set, prepared statement and connection.
rs.close();
ps.close();
conn.close();

Notes:
  • In Informix, the serial column values must be retrieved prior to the statement being closed; otherwise, that information is lost.
  • Refer to the WebSphere Application Server V5.1 Information Center on Class WSCallHelper for more information.
 
 
Cross Reference information
Segment Product Component Platform Version Edition
Application Servers Runtimes for Java Technology Java SDK
 
 


Document Information


Product categories: Software > Application Servers > Distributed Application & Web Servers > WebSphere Application Server > DB Connections/Connection Pooling
Operating system(s): Windows
Software version: 6.0
Software edition:
Reference #: 1188293
IBM Group: Software Group
Modified date: Oct 27, 2004