Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


Result Set Processing


There are a number of ways of returning a result set. Perhaps the simplest is as the result of executing an SQL statement using the executeQuery method, for example:

 Statement stmt;
 ResultSet rs;
 
 stmt = con.createStatement();
 
 rs = stmt.executeQuery("SELECT *"
                      + "   FROM mimer_store.currencies");
                      
 while (rs.next()) {
     System.out.println(rs.getString("CURRENCY"));
 

A ResultSet can be thought of as an array of rows. The 'current row' is the row being examined and manipulated at any given time, and the location in the ResultSet is the 'current row position'.

Information about the columns in a result set can be retrieved from the metadata, for example:

 Statement stmt;
 ResultSet rs;
 ResultSetMetaData rsmd;
 
 stmt = con.createStatement();
 
 rs = stmt.executeQuery("SELECT *"
                      + "   FROM mimer_store.currencies");
                      
 rsmd = rs.getMetaData();
 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
     System.out.println(rsmd.getColumnName(i));
     System.out.println(" Type: " + rsmd.getColumnTypeName(i));
     System.out.println(" Size: " + rsmd.getColumnDisplaySize(i));
 }

Scrolling in Result Sets

The previous examples used forward-only cursors (TYPE_FORWARD_ONLY), which means that they only support fetching rows serially from the start to the end of the cursor, this is the default cursor type.

In modern, screen-based applications, the user expects to be able to scroll backwards and forwards through the data. While it is possible to cache small result sets in memory on the client, this is not feasible when dealing with large result sets. Support for scrollable cursors in JDBC 2 provide the answer.

Scrollable cursors allow you to move forward and back as well as to a particular row within the ResultSet. With scrollable cursors it is possible to iterate through the result set many times.

The JDBC 2 specification included scrollable cursors and the Mimer JDBC 2 Driver supports TYPE_SCROLL_INSENSITIVE, which means that the result set is scrollable but also that the result set does not show changes that have been made to the underlying database by other users, i.e. the view of the database is consistent. To allow changes to be reflected may cause logically inconsistent results.

Result Set Capabilities

A ResultSet is created when a query is executed. The capabilities of the result set depend on the arguments used with the createStatement (or prepareStatement or prepareCall) method.

The first argument defines the type of the ResultSet, whether it is scrollable or non-scrollable, and the second argument defines the concurrency option, i.e. the update capabilities.

A ResultSet should only be made updateable if the functionality is going to be used, otherwise the option CONCUR_READ_ONLY should be used. If used, both the type and the concurrency option must be specified.

The following example creates a scrollable result set cursor that is also updateable:

 stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
 

Even if the options used specify that the result set will be scrollable and updateable, it is possible that the actual SQL query will return a ResultSet that is non-scrollable or non-updateable.

Positioning the Cursor

There are a number of methods provided to position the cursor:

There are also methods to determine the current position of the cursor:

The getRow method returns the current cursor position, starting from 1. This provides a simple means of finding the number of rows in a result set.

For example:

 Statement stmt;
 ResultSet rs;
 
 stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
                            
 rs = stmt.executeQuery("SELECT code, currency"
                      + "   FROM mimer_store.currencies"
                      + "   WHERE code LIKE 'A%'");
                      
 System.out.println("\nOriginal sort order");
 while (rs.next()) {
     System.out.println(rs.getString(1) + "  " + rs.getString(2));
 }
 
 System.out.println("\nReverse order");
 while (rs.previous()) {
     System.out.println(rs.getString(1) + "  " + rs.getString(2));
 }
 
 rs.last();
 System.out.println("\nThere are " + rs.getRow() + " rows");
 

The Mimer JDBC Driver will automatically perform a pre-fetch whenever a result set is created. This means that a number of rows are transferred to the client in a single communication across the network. If only a small number of rows are actually required use setMaxRows to limit the number of rows returned in the result set.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX