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