Using JDBC 2.0 methods for positioned updates and deletes

This section discusses JDBC 2.0 methods for updating columns in the current cursor row and updating the database from the current cursor row in a result set. Each is followed by an example.

JDBC 2.0 specifies a number of methods for updating column values from a result set in memory, on the client. The updated values can then be used to perform an update, insert, or delete operation on the underlying database. All of these methods are implemented in the SybCursorResultSet class.

Examples of some of the JDBC 2.0 update methods available in jConnect are:

void updateAsciiStream(String columnName, java.io.InputStream x, int length)
   throws SQLException;

void updateBoolean(int columnIndex, boolean x) throws SQLException;

void updateFloat(int columnIndex, float x) throws SQLException;

void updateInt(String columnName, int x) throws SQLException;

void updateInt(int columnIndex, int x) throws SQLException;

void updateObject(String columnName, Object x) throws SQLException;

JDBC 2.0 specifies two 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;

The following example creates a single Statement object that is used to return a cursor result set. For each row in the result set, column values are updated in memory and then 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();
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...”);

}

To delete a row from a cursor result set, you can use SybCursorResultSet.deleteRow as follows:

 while(syb_rs.next())
 {
     int col3 = getInt(3);
     if (col3 >100)
     {
     syb_rs.deleteRow();
     }
 }

The following example illustrates how to do inserts using the JDBC 2.0 API. There is no need to downcast to a SybCursorResultSet.

// prepare to insert
rs.moveToInsertRow();

// populate new row with column values
rs.updateString(1, "New entry for col 1");
rs.updateInt(2, 42);

// insert new row into db
rs.insertRow();

// return to current row in result set
rs.moveToCurrentRow();