Enhanced JDBC functions for i5/OS Version 5 Release 2

Several JDBC functions were enhanced for i5/OS Version 5 Release 2.

Enhanced JDBC functions for i5/OS Version 5 Release 2 include:

Removal of the 'FOR UPDATE' restriction
You no longer need to specify FOR UPDATE on your SELECT statements in order to guarantee an updatable cursor. When connecting to V5R1 and later versions of i5/OS, IBM® Toolbox for Java™ honors whatever concurrency you pass in when you create statements. The default continues to be a read-only cursor if you do not specify a concurrency.
Data truncation throws exceptions only when truncated character data is written to the database
Data truncation rules for IBM Toolbox for Java now are the same as those for the IBM Developer Kit for Java JDBC driver. For more information, see IBM Toolbox for Java JDBC properties.
Get and modify columns and parameters by name
New methods allow you to get and update information by column name in ResultSet and to get and set information by parameter name in CallableStatement. For example, in ResultSet, where you previously used the following: 
     ResultSet rs = statement.executeQuery( SELECT * FROM MYCOLLECTION/MYTABLE );
     rs.getString(1);

You can now use:

     ResultSet rs = statement.executeQuery( SELECT * FROM MYCOLLECTION/MYTABLE );
     rs.getString( 'STUDENTS' );

Be aware that accessing parameters by their index results in better performance than accessing them by their name. You can also specify parameter names to set in CallableStatement. Where you might have used the following in CallableStatement:

     CallableStatement cs = connection.prepareCall( CALL MYPGM (?) );
     cs.setString( 1 );

You can now use:

     CallableStatement cs = connection.prepareCall( CALL MYPGM (?) );
     cs.setString( 'PARAM_1' );

To use these new methods, you need JDBC 3.0 or later and the Java 2 Platform, version 1.4 (either the Standard or the Enterprise Edition).

Retrieve auto-generated keys
The getGeneratedKeys() method on AS400JDBCStatement retrieves any auto-generated keys created as a result of executing that Statement object. When the Statement object does not generate any keys, an empty ResultSet object is returned. Currently the server supports returning only one auto-generated key (the key for the last inserted row). The following example shows how you might insert a value into a table then get the auto-generated key: 
     Statement s =
        statement.executeQuery("INSERT INTO MYSCHOOL/MYSTUDENTS (FIRSTNAME) VALUES ('JOHN'");
     ResultSet rs = s.getGeneratedKeys();
          // Currently the server supports returning only one auto-generated 
          // key -- the key for the last inserted row.
     rs.next();
     String autoGeneratedKey = rs.getString(1);
          // Use the auto-generated key, for example, as the primary key in another table

To retrieve auto-generated keys, you need JDBC 3.0 or later, and the Java 2 Platform, version 1.4 (either the Standard or the Enterprise Edition). Retrieving auto-generated keys also requires connecting to a V5R2 or later version of i5/OS.

Improved performance when running SQL insert statements in a batch
Performance of running SQL insert statements in a batch has been improved. Run SQL statements in a batch by using the different addBatch() methods available in AS400JDBCStatement, AS400JDBCPreparedStatement, and AS400JDBCCallableStatement. Enhanced batch support affects only insert requests. For example, using batch support to process several inserts involves only one pass to the server. However, using batch support to process an insert, and update, and a delete sends each request individually.

To use batch support, you need JDBC 2.0 or later and the Java 2 Platform, version 1.2 (either the Standard or the Enterprise Edition).

Enhanced support for ResultSet.getRow()
Previously, the IBM Toolbox for Java JDBC driver was limited in its support for the getRow() method in ResultSet. Specifically, using ResultSet.last(), ResultSet.afterLast(), and ResultSet.absolute() with a negative value made the current row number not available. The previous restrictions are lifted, making this method fully functional.
Using mixed case in column names
IBM Toolbox for Java methods must match either column names provided by the user or column names provided by the application with the names that are on the database table. In either case, when a column name is not enclosed in quotes, IBM Toolbox for Java changes the name to uppercase characters before matching it against the names on the server. When the column name is enclosed in quotes, it must exactly match the name on the server or IBM Toolbox for Java throws an exception.
Specify holdability in created Statements, CallableStatements, and PreparedStatements
New methods in AS400JDBCConnection allow you to specify the holdability for Statements, CallableStatements, and PreparedStatements that you create. Holdability determines whether cursors are held open or closed when committing the transaction. You can now have a statement that has a different holdability than its connection object. Also, connection objects can have multiple open statement objects, each with a different specified holdability. Calling commit causes each statement to be handled according to the holdability specified for that statement.

Holdability is derived in the following order of precedence:

  1. Holdability specified on statement creation by using the Connection class methods createStatement(), prepareCall(), or prepareStatement().
  2. Holdability specified by using Connection.setHoldability(int).
  3. Holdability specified by the IBM Toolbox for Java JDBC cursor hold property (when methods in 1. or 2. are not used)

To use these methods, you need JDBC 3.0 or later, and the Java 2 Platform, version 1.4 (either the Standard or the Enterprise Edition). Also, servers running a V5R1 or earlier version of i5/OS are able to use only the holdability specified by the JDBC cursor hold property.

Enhanced transaction isolation support
The IBM Toolbox for Java JDBC driver now features support for switching to a transaction isolation level of *NONE after a connection is made. Before V5R2, the IBM Toolbox for Java JDBC driver threw an exception when switching to *NONE after making a connection.
Parent topic: JDBC