Methods for Updating a Database from a Result Set

JDBC 2.0 specifies methods for updating or deleting rows in the database, based on the current values in a result set.

These methods are simpler in form than Statement.executeUpdate in JDBC 1.x and do not require a cursor name. They are implemented in SybCursorResultSet:

void updateRow() throws SQLException;
void deleteRow() throws SQLException;

Note: The concurrency of the result set must be CONCUR_UPDATABLE. Otherwise, the above methods raise exceptions. For insertRow, all table columns that require non-null entries must be specified. Methods provided on DatabaseMetaData dictate when these changes are visible.

Example

This example creates a single Statement object that returns a cursor result set. For each row in the result set, column values are updated in memory and the database is updated with the new column values for the row.

// Create a Statement object and set fetch size to 
// 25. This creates a cursor for the Statement 
// object Use the statement to return a cursor
// result set.
SybStatement syb_stmt = 
(SybStatement)conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_UPDATABLE);
syb_stmt.setFetchSize(25);
SybCursorResultSet syb_rs = 
(SybCursorResultSet)syb_stmt.executeQuery(
    "SELECT * from T1 WHERE ...")
 
// Update each row in the result set according to
// code in the following while loop. jConnect 
// fetches 25 rows at a time, until fewer than 25 
// rows are left. Its last fetch takes any 
// remaining rows.
while(syb_rs.next())
{
  // Update columns 2 and 3 of each row, where 
// column 2 is a varchar in the database and 
// column 3 is an integer.
  syb_rs.updateString(2, "xyz");
syb_rs.updateInt(3,100);
//Now, update the row in the database.
  syb_rs.updateRow();
}

// Create a Statement object using the
// JDBC 2.0 method implemented in jConnect 6.0
Statement stmt = conn.createStatement
(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

// In jConnect 6.0, downcasting to SybCursorResultSet is not
// necessary. Update each row in the ResultSet in the same
// manner as above
while (rs.next())
{
rs.updateString(2, “xyz”);
rs.updateInt(3,100);
  rs.updateRow();

// Use the Statement to return an updatable ResultSet
ResultSet rs = stmt.executeQuery(“SELECT * FROM T1 WHERE...”);