DECLARE/FETCH USING JAVA..(POSITION ED UPDATE)

[technocrat]

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;

[Gert van der Kooij]

In article <11************ **********@38g2 000cwa.googlegr oups.com>,
su************@ gmail.com says...

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;

You can find an example in the SQLLIB\samples\ java\jdbc\dbrsh old.java

[Rhino]

"technocrat " <su************ @gmail.com> wrote in message
news:11******** **************@ 38g2000cwa.goog legroups.com...

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;

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

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

String METHOD_NAME = "positionedUpda te()";

String queryTableSQL = "select lastname, workdept, salary, bonus " +
"from " + demoTable
+ " " + "where workdept = 'D21' " + "for update";
String positionedUpdat eSQL = 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 positionedUpdat eStmt = null;
ResultSet rs01 = null;
try {
queryTableStmt = conn01.createSt atement();
positionedUpdat eStmt = conn01.createSt atement();
rs01 = queryTableStmt. executeQuery(qu eryTableSQL);
String cursorName = rs01.getCursorN ame();
positionedUpdat eSQL = "update " + demoTable + " " + "set salary
= " + newSalary + " "
+ "where current of " + cursorName;
} catch (SQLException excp) {
System.err.prin tln(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while trying to get
information from "
+ demoTable + " table. Error: " + excp);
excp.printStack Trace();
System.exit(16) ;
}

System.out.prin tln("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.getBigDeci mal(3);
bonus = rs01.getBigDeci mal(4);
System.out.prin tln(lastname + spaces
+ workdept + spaces
+ salary.toString () + spaces
+ bonus.toString( ));
if (bonus.compareT o(minimumBonus) > 0) { //if individual's
// bonus is greater
// than minimum
bonus
positionedUpdat eStmt.executeUp date(positioned UpdateSQL);
//do

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

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

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

--
Rhino