Multiple ResultSet objects that are open simultaneously for identical SELECT statements

Keeping multiple ResultSet objects open at the same time for identical SELECT statements can cause problems under certain conditions when your non-pureQuery API application runs SQL statically.
Those conditions are:
  • The ResultSet objects were opened by a single connection.
  • The ResultSet objects were returned by identical SELECT statements that have identical cursor attributes.
  • The ResultSet objects remain open at the same time.
  • The ResultSet objects are modified by UPDATE or DELETE statements that contain the WHERE CURRENT OF clause.

For example, consider the following code:

ResultSet rs1, rs2;

Statement stmt1 = jdbcCon.createStatement(TYPE_FORWARD_ONLY, CONCUR_UPDATABLE);
rs1 = stmt1.executeQuery("SELECT * FROM SCH1.TBL1");
rs1.next();

Statement stmt2 = jdbcCon.createStatement(TYPE_FORWARD_ONLY, CONCUR_UPDATABLE);
rs2 = stmt2.executeQuery("SELECT * FROM SCH1.TBL1");
rs2.next();

PreparedStatement pStmt = jdbcCon.prepareStatement(
	"DELETE FROM SCH1.TBL1 WHERE CURRENT OF " +
	rs2.getCursorName());

If the SQL statements are run dynamically, this code presents no ambiguity. However, if they are run statically and the ResultSet objects were opened with the same connection, a problem arises. When pureQuery captures SQL statements for a non-pureQuery API application, it captures identical statements only once. After pureQuery captures the SQL statements in this example, the pureQueryXML file contains one instance of SELECT * FROM SCH1.TBL1. If pureQuery assigns the name DB_PDQ_SPC7 to the cursor, the pureQueryXML file also contains one instance of DELETE FROM SCH1.TBL1 WHERE CURRENT OF DB_PDQ_SPC7.

At runtime, if using only the cursor name in the WHERE CURRENT OF clause, pureQuery cannot determine which of the two ResultSet objects the cursor in the DELETE statement acts upon.

The same ambiguity can occur in less obvious situations. For example, the SELECT statements might be in methods that return ResultSet objects, and the UPDATE or DELETE WHERE CURRENT OF statements might be inside methods that receive ResultSet objects as input parameters.

The problem might not be obvious in an application's source code. For this reason, pureQuery logs a warning when it detects that more than one ResultSet object is currently open for a single SELECT statement.

Techniques for avoiding the problem

Here are techniques that you can try to resolve this ambiguity.

Avoid using the WHERE CURRENT OF clause
Use the updateRow(), deleteRow(), and insertRow() methods instead. This approach is more object-oriented than specifying statements that use WHERE CURRENT OF.
Statement stmt1 = jdbcCon.createStatement(TYPE_FORWARD_ONLY,CONCUR_UPDATABLE);
rs1 = stmt.executeQuery("SELECT * FROM SCH1.TBL1");
rs1.next();

Statement stmt2 = jdbcCon.createStatement(TYPE_FORWARD_ONLY,CONCUR_UPDATABLE);
rs2 = stmt.executeQuery("SELECT * FROM SCH1.TBL1"); 
rs2.next();

rs2.deleteRow(); 
If you must use the WHERE CURRENT OF clause in an UPDATE or DELETE statement, you can embed the call to ResultSet.getCursorName() when you prepare or execute the statement, as in this example:
PreparedStatement pStmt = jdbcCon.prepareStatement(
	"DELETE FROM SCH1.TBL1 WHERE CURRENT OF " +
	rs2.getCursorName());
At runtime, pureQuery can associate the getCursorName() method with the UPDATE or DELETE statement, and therefore associates the correct ResultSet object with the statement. This association is guaranteed only when the getCursorName() method appears in either of these two places:
  • Together with the SQL statement in the method call that creates a PreparedStatement
  • In a Statement Object's execute() or executeUpdate() methods
Never use one connection to manipulate multiple ResultSet objects that are open simultaneously for identical SELECT statements if your application manipulates those objects with UPDATE or DELETE statements that contain WHERE CURRENT OF clauses
The code at the beginning of this topic works if the ResultSet objects are opened by separate connections.

Property for allowing identical SQL SELECT statements to run on DB2 for z/OS

If you need to run identical, static SQL SELECT statements against DB2® for z/OS® and open multiple, identical ResultSet objects, you must first set the IBM® Data Server Driver for JDBC and SQLJ property db2.jcc.allowSqljDuplicateStaticQueries to YES or TRUE.


Feedback