Performing positioned UPDATE and DELETE operations in an SQLJ application

As in database applications in other languages, performing positioned UPDATEs and DELETEs with SQLJ is an extension of retrieving rows from a result table.

Procedure

The basic steps are:

Declare the iterator.
The iterator can be positioned or named. For positioned UPDATE or DELETE operations, declare the iterator as updatable, using one or both of the following clauses:

implements sqlj.runtime.ForUpdate

This clause causes the generated iterator class to include methods for using updatable iterators. This clause is required for programs with positioned UPDATE or DELETE operations.

with (updateColumns="column-list")

This clause specifies a comma-separated list of the columns of the result table that the iterator will update. This clause is optional.

You need to declare the iterator as public, so you need to follow the rules for declaring and using public iterators in the same file or different files.

If you declare the iterator in a file by itself, any SQLJ source file that has addressability to the iterator and imports the generated class can retrieve data and execute positioned UPDATE or DELETE statements using the iterator.

The authorization ID under which a positioned UPDATE or DELETE statement executes depends on whether the statement executes statically or dynamically. If the statement executes statically, the authorization ID is the owner of the plan or package that includes the statement. If the statement executes dynamically the authorization ID is determined by the DYNAMICRULES behavior that is in effect. For the IBM® Data Server Driver for JDBC and SQLJ, the behavior is always DYNAMICRULES BIND.
Disable autocommit mode for the connection.
If autocommit mode is enabled, a COMMIT operation occurs every time the positioned UPDATE statement executes, which causes the iterator to be destroyed unless the iterator has the with (holdability=true) attribute. Therefore, you need to turn autocommit off to prevent COMMIT operations until you have finished using the iterator. If you want a COMMIT to occur after every update operation, an alternative way to keep the iterator from being destroyed after each COMMIT operation is to declare the iterator with (holdability=true).
Create an instance of the iterator class.
This is the same step as for a non-updatable iterator.
Assign the result table of a SELECT to an instance of the iterator.
This is the same step as for a non-updatable iterator. The SELECT statement must not include a FOR UPDATE clause.
Retrieve and update rows.
For a positioned iterator, do this by performing the following actions in a loop:
1. Execute a FETCH statement in an executable clause to obtain the current row.
2. Test whether the iterator is pointing to a row of the result table by invoking the PositionedIterator.endFetch method.
3. If the iterator is pointing to a row of the result table, execute an SQL UPDATE… WHERE CURRENT OF :iterator-object statement in an executable clause to update the columns in the current row. Execute an SQL DELETE… WHERE CURRENT OF :iterator-object statement in an executable clause to delete the current row.
For a named iterator, do this by performing the following actions in a loop:
1. Invoke the next method to move the iterator forward.
2. Test whether the iterator is pointing to a row of the result table by checking whether next returns true.
3. Execute an SQL UPDATE… WHERE CURRENT OF iterator-object statement in an executable clause to update the columns in the current row. Execute an SQL DELETE… WHERE CURRENT OF iterator-object statement in an executable clause to delete the current row.
Close the iterator.
Use the close method to do this.

Example

The following code shows how to declare a positioned iterator and use it for positioned UPDATEs. The numbers to the right of selected statements correspond to the previously described steps.

First, in one file, declare positioned iterator UpdByPos, specifying that you want to use the iterator to update column SALARY:

Figure 1. Example of declaring a positioned iterator for a positioned UPDATE

import java.math.*;    // Import this class for BigDecimal data type
#sql public iterator UpdByPos implements sqlj.runtime.ForUpdate       1 
  with(updateColumns="SALARY") (String, BigDecimal);

Then, in another file, use UpdByPos for a positioned UPDATE, as shown in the following code fragment:

Figure 2. Example of performing a positioned UPDATE with a positioned iterator

import sqlj.runtime.*;       // Import files for SQLJ and JDBC APIs
import java.sql.*;
import java.math.*;          // Import this class for BigDecimal data type
import UpdByPos;             // Import the generated iterator class that 
                             // was created by the iterator declaration clause
                             // for UpdByName in another file
#sql context HSCtx;          // Create a connection context class HSCtx
public static void main (String args[])
{
  try {
    Class.forName("com.ibm.db2.jcc.DB2Driver");
  }
  catch (ClassNotFoundException e) {
    e.printStackTrace();
          }
  Connection HSjdbccon=
  DriverManager.getConnection("jdbc:db2:SANJOSE");
                     // Create a JDBC connection object
  HSjdbccon.setAutoCommit(false);
                     // Set autocommit off so automatic commits       2 
                     // do not destroy the cursor between updates
  HSCtx myConnCtx=new HSCtx(HSjdbccon);  
                     // Create a connection context object
  UpdByPos upditer;  // Declare iterator object of UpdByPos class     3 
  String empnum;     // Declares host variable to receive EMPNO
  BigDecimal sal;    // and SALARY column values
  #sql [myConnCtx] 
    upditer = {SELECT EMPNO, SALARY FROM EMPLOYEE                     4 
      WHERE WORKDEPT='D11'};
                     // Assign result table to iterator object
  #sql {FETCH :upditer INTO :empnum,:sal};                            5a 
                             // Move cursor to next row
  while (!upditer.endFetch())                                         5b 
                             // Check if on a row
  {
    #sql [myConnCtx] {UPDATE EMPLOYEE SET SALARY=SALARY*1.05 
      WHERE CURRENT OF :upditer};                                     5c 
                             // Perform positioned update
    System.out.println("Updating row for " + empnum);
    #sql {FETCH :upditer INTO :empnum,:sal};
                             // Move cursor to next row
  }
  upditer.close();           // Close the iterator                    6 
  #sql [myConnCtx] {COMMIT};
                             // Commit the changes
  myConnCtx.close();         // Close the connection context
}

The following code shows how to declare a named iterator and use it for positioned UPDATEs. The numbers to the right of selected statements correspond to the previously described steps.

First, in one file, declare named iterator UpdByName, specifying that you want to use the iterator to update column SALARY:

Figure 3. Example of declaring a named iterator for a positioned UPDATE

import java.math.*;          // Import this class for BigDecimal data type
#sql public iterator UpdByName implements sqlj.runtime.ForUpdate     1 
  with(updateColumns="SALARY") (String EmpNo, BigDecimal Salary);

Then, in another file, use UpdByName for a positioned UPDATE, as shown in the following code fragment:

Figure 4. Example of performing a positioned UPDATE with a named iterator

import sqlj.runtime.*;       // Import files for SQLJ and JDBC APIs
import java.sql.*;
import java.math.*;          // Import this class for BigDecimal data type
import UpdByName;            // Import the generated iterator class that 
                             // was created by the iterator declaration clause
                             // for UpdByName in another file
#sql context HSCtx;          // Create a connection context class HSCtx
public static void main (String args[])
{
  try {
    Class.forName("com.ibm.db2.jcc.DB2Driver");
  }
  catch (ClassNotFoundException e) {
    e.printStackTrace();
          }
  Connection HSjdbccon=
  DriverManager.getConnection("jdbc:db2:SANJOSE");
                         // Create a JDBC connection object
  HSjdbccon.setAutoCommit(false);
                         // Set autocommit off so automatic commits  2 
                         // do not destroy the cursor between updates
  HSCtx myConnCtx=new HSCtx(HSjdbccon);  
                         // Create a connection context object
  UpdByName upditer;                                                 3 
                         // Declare iterator object of UpdByName class   
  String empnum;               // Declare host variable to receive EmpNo
                             // column values
  #sql [myConnCtx] 
    upditer = {SELECT EMPNO, SALARY FROM EMPLOYEE                    4 
      WHERE WORKDEPT='D11'};
                             // Assign result table to iterator object
  while (upditer.next())                                             5a,5b 
                             // Move cursor to next row and 
                             // check ifon a row
  {
    empnum = upditer.EmpNo();  // Get employee number from current row
    #sql [myConnCtx] 
      {UPDATE EMPLOYEE SET SALARY=SALARY*1.05 
        WHERE CURRENT OF :upditer};                                  5c 
                             // Perform positioned update
    System.out.println("Updating row for " + empnum);
  }
  upditer.close();           // Close the iterator                   6 
  #sql [myConnCtx] {COMMIT};
                             // Commit the changes
  myConnCtx.close();         // Close the connection context
}