More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Extended Updatable Cursor Example
String URL = "jdbc:derby:sample";
// autocommit must be turned off for updatable cursors
conn.setAutoCommit(false);
Statement s3 = conn.createStatement();
// name the statement so we can reference the result set
// it generates
s3.setCursorName("UPDATABLESTATEMENT");
// Updatable statements have some requirements
// for example, select must be on a single table
ResultSet Updatable = s3.executeQuery(
"SELECT firstnme, lastname, workdept, bonus" +
"FROM employee FOR UPDATE of bonus");
// we need a separate statement to do the
// update PreparedStatement
PreparedStatement ps2 = conn.prepareStatement("UPDATE employee " +
// we could use the cursor name known by the system,
// as the following line shows
//"SET bonus = ? WHERE CURRENT OF " + Updatable.getCursorName());
// but we already know the cursor name
"SET bonus = ? WHERE CURRENT OF UPDATABLESTATEMENT");
String theDept="E21";
while (Updatable.next()) {
String firstnme = Updatable.getString("FIRSTNME");
String lastName = Updatable.getString("LASTNAME");
String workDept = Updatable.getString("WORKDEPT");
BigDecimal bonus = Updatable.getBigDecimal("BONUS");
if (workDept.equals(theDept)) {
// if the current row meets our criteria,
// update the updatable column in the row
ps2.setBigDecimal(1, bonus.add(new BigDecimal(250)));
ps2.executeUpdate();
System.out.println("Updating bonus in employee" +
" table for employee " + firstnme +
", department " + theDept );
}
}
Updatable.close();
s3.close();
ps2.close();
conn.commit();
(Continued on next question...)
