|
| Problem | 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. | | | | Solution | 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:
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:
WebSphere's relational resource adapter for Informix wraps the underlying com.informix.jdbc.IfmxStatement object with the com.ibm.ws.rsadapter.jdbc.WSJdbcStatementobject, 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", newObject[0], newClass[0]); serial = ((Integer) obj).longValue();
// Alternatively, get value of the serial16 column using // the WSCallHelper Object obj = WSCallHelper.jdbcCall( null,
pStmt, "getSerial16", newObject[0], newClass[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.
| |
| | |
| |
|
Product categories: Software, Application Servers, Distributed Application & Web Servers, WebSphere Application Server, DB Connections/Connection Pooling Operating system(s): Multi-Platform Software version: 3.5, 4.0, 5.0, 5.1, 6.0 Software edition: Edition Independent Reference #: 1188293 IBM Group: Software Group Modified date: 2004-10-27
(C) Copyright IBM Corporation 2000, 2004. All Rights Reserved.
|