HOW CAN THIS BE DONE IN JAVA??? THIS IS POSITIONED UPDATE


EXEC SQL DECLARE C1 CURSOR FOR
SELECT *
FROM EMPLOYEE
FOR UPDATE OF JOB;

EXEC SQL OPEN C1;

EXEC SQL FETCH C1 INTO ... ;
if ( strcmp (change, "YES") == 0 )
EXEC SQL UPDATE EMPLOYEE
SET JOB = :newjob
WHERE CURRENT OF C1;

EXEC SQL CLOSE C1;

In article <1148584348.635139.308560 (AT) 38g2000cwa (DOT) googlegroups.com>,
sumant.kalvala (AT) gmail (DOT) com says...
You can find an example in the SQLLIB\samples\java\jdbc\dbrshold.java

"technocrat" <sumant.kalvala (AT) gmail (DOT) com> wrote

Here's an example of a positioned update from one of my Java prototypes. It
uses Statements and I'd probably use PreparedStatements if I were writing it
today but this will work:

---------------------------------------------------------------------------------------------------------------------
/**
* Do a positioned update in the demonstration table.
*/
public void positionedUpdate() {

String METHOD_NAME = "positionedUpdate()";

String queryTableSQL = "select lastname, workdept, salary, bonus " +
"from " + demoTable
+ " " + "where workdept = 'D21' " + "for update";
String positionedUpdateSQL = null;
String newSalary = "50000.00";
BigDecimal minimumBonus = new BigDecimal(450.00);

/*
* The query will display the rows that will be changed by the
update.
* The update will set all the salaries for those rows to a single
* arbitrary amount but only where the bonus is above an arbitrary
* amount.
*/
Statement queryTableStmt = null;
Statement positionedUpdateStmt = null;
ResultSet rs01 = null;
try {
queryTableStmt = conn01.createStatement();
positionedUpdateStmt = conn01.createStatement();
rs01 = queryTableStmt.executeQuery(queryTableSQL);
String cursorName = rs01.getCursorName();
positionedUpdateSQL = "update " + demoTable + " " + "set salary
= " + newSalary + " "
+ "where current of " + cursorName;
} catch (SQLException excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while trying to get
information from "
+ demoTable + " table. Error: " + excp);
excp.printStackTrace();
System.exit(16);
}

System.out.println("Values before positioned update:");
String spaces = " ";

/* Initialize the host variables used for handling the result set.
*/
String lastname = null;
String workdept = null;
BigDecimal salary = null;
BigDecimal bonus = null;

/*
* Print each line of the result set.
*/
try {
while (rs01.next()) {
lastname = rs01.getString(1);
workdept = rs01.getString(2);
salary = rs01.getBigDecimal(3);
bonus = rs01.getBigDecimal(4);
System.out.println(lastname + spaces
+ workdept + spaces
+ salary.toString() + spaces
+ bonus.toString());
if (bonus.compareTo(minimumBonus) > 0) { //if individual's
// bonus is greater
// than minimum
bonus
positionedUpdateStmt.executeUpdate(positionedUpdat eSQL);
//do

// update
}
}
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while reading " +
demoTable
+ " table. Error: " + sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}

/* Close the result set, dispose of the statements, and commit. */
try {
rs01.close();
queryTableStmt.close();
positionedUpdateStmt.close();
conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while closing " +
demoTable
+ " result set, closing statements, or committing.
Error: " + sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}

---------------------------------------------------------------------------------------------------------------------

--
Rhino