Once you create a PreparedStatement object, you can use it multiple times with the same or different values for its input parameters. If you use a cursor with a PreparedStatement object, you need to close the cursor after each use and then reopen the cursor to use it again. A cursor is closed when you close its result set (ResultSet.close( )). It is opened when you execute its prepared statement (PreparedStatement.executeQuery( )).
The following example shows how to create a PreparedStatement object, assign it a cursor, and execute the PreparedStatement object twice, closing and then reopening the cursor.
// Create a prepared statement object with a
// parameterized query.
PreparedStatement prep_stmt =
conn.prepareStatement(
"SELECT au_id, au_lname, au_fname "+
"FROM authors WHERE city = ? "+
"FOR UPDATE OF au_lname");
//Create a cursor for the statement.
prep_stmt.setCursorName("author_cursor");
// Assign the parameter in the query a value.
// Execute the prepared statement to return a
// result set.
prep_stmt.setString(1, "Oakland");
ResultSet rs = prep_stmt.executeQuery();
//Do some processing on the result set.
while(rs.next())
{
...
}
// Close the result, which also closes the cursor.
rs.close();
// Execute the prepared statement again with a new
// parameter value. prep_stmt.setString(1,"San Francisco"); rs = prep_stmt.executeQuery();
// reopens cursor
| Copyright © 2003. Sybase Inc. All rights reserved. |
|
|
