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:
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 connnection 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
}
Figure 33. Example of performing a positioned UPDATE with a positioned iterator
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);
Figure 34. Example of declaring a named iterator for a positioned UPDATE
116 Application Programming Guide and Reference for Java
™
To Next Page
Loading...
Need help?
General