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.
Here are techniques that you can try to resolve this ambiguity.
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();
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: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.