RESULT SETS EXPLORED in Font

Creator Code 39 in Font RESULT SETS EXPLORED

Within a scrollable result set, you can move your position using either relative or absolute positioning: Relative positioning allows you to move a given number of rows forward or backward from the current row. Absolute positioning allows you to move to a specified row number, counting from either the beginning or the end of the result set.

When creating a scrollable result set, you must also specify sensitivity: A scroll-insensitive result set is a scrollable result set that does not automatically detect certain changes made to the database while the result set is open, thus providing a static view of the underlying data. This is the default behavior. You would need to retrieve a new result set to see changes made to the database. Note that there are several limitations to the way this works in Oracle, as you will see in the section Result Set Limitations and Downgrade Rules. A scroll-sensitive result set is a scrollable result set with a limited ability to detect certain changes made to the underlying result set data in the database from the current session or a different session while the result set is open.

Updatability refers to the ability to insert, delete, or update rows in a result set and propagate these changes to the database. The updatability of a result set is determined by its concurrency type. Under JDBC 2.0, the following concurrency types are available: Updatable: Updates, inserts, and deletes can be performed on the result set and propagated to the database. Read-only: The result set cannot be modified in any way (the default behavior).

Note The updatability of a result set can be specified independently of the scrollability or sensitivity of

Table 7-7 lists six different combinations of result set categories available in Oracle, along with their capabilities. Table 7-7. Different Result Set Categories and Their Capabilities

Forward-only/read-only (default)

Not scrollable. Cannot perform insert, update, and delete operations on the ResultSet object. Cannot detect any changes made to the database while the ResultSet is open. Scrollable. Does not automatically detect certain changes made to the database while the ResultSet is open. Cannot perform insert, update, and delete operations on the ResultSet object. Scrollable. Limited ability to detect certain changes made to the underlying ResultSet data in the database. Cannot perform insert, update, and delete operations on the ResultSet object. Not scrollable. Can perform insert, update, and delete operations on the ResultSet object. Scrollable. Does not automatically detect certain changes made to the database while the ResultSet is open. Can perform insert, update, and delete operations on the ResultSet object. Scrollable. Limited ability to detect certain changes made to the underlying ResultSet data in the database. Cannot perform insert, update, and delete operations on the ResultSet object.

To create any of the six combinations of result sets, you can use one of the following methods in the Connection object: Statement createStatement (int resultSetType, int resultSetConcurrency) PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) As mentioned earlier, although the Oracle JDBC documentation states that the result set scrollability and updatability are implemented in CallableStatement, I discovered that the current implementation does not work for result sets returned via stored procedures. This is not a real problem since updatability, as implemented currently, can be easily accomplished using standard SQL statements to update, insert, and delete rows. As far as scrollable result sets go, their most common use is to display a subset of rows at a time for a given result set and allow the user to scroll through it, say, ten rows at a time. For this scenario, as explained in