Updating a database from a result set of a stored procedure

jConnect includes update and delete methods that allow you to get a cursor on the result set returned by a stored procedure. You can then use the position of the cursor to update or delete rows in the underlying table that provided the result set. The methods are in SybCursorResultSet:

void updateRow(String tableName) throws SQLException;

void deleteRow(String tableName) throws SQLException;

The tableName parameter identifies the database table that provided the result set.

To get a cursor on the result set returned by a stored procedure, you need to use either SybCallableStatement.setCursorName or SybCallableStatement.setFetchSize before you execute the callable statement that contains the procedure. The following example shows how to create a cursor on the result set of a stored procedure, update values in the result set, and then update the underlying table using the SybCursorResultSet.update method:

// Create a CallableStatement object for executing the stored 
// procedure. 
CallableStatement sproc_stmt = 
   conn.prepareCall("{call update_titles}", 
     ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
 
// Set the number of rows to be returned from the database with
// each fetch. This creates a cursor on the result set.
(SybCallableStatement)sproc_stmt.setFetchSize(10);
 
//Execute the stored procedure and get a result set from it.
SybCursorResultSet sproc_result = (SybCursorResultSet) 
   sproc_stmt.executeQuery();
 
// Move through the result set row by row, updating values in the
// cursor’s current row and updating the underlying titles table
// with the modified row values. 
while(sproc_result.next())
{
   sproc_result.updateString(...);
   sproc_result.updateInt(...);
   ...
   sproc_result.updateRow(titles);
}