Using TYPE_SCROLL_INSENSITIVE result sets in jConnect

jConnect supports only TYPE_SCROLL_INSENSITIVE result sets.

jConnect uses the Tabular Data Stream (TDS)—the Sybase proprietary protocol—to communicate with Sybase database servers. Adaptive Server 15.0 or later supports TDS scrollable cursors. For servers that do not support TDS scrollable cursors, jConnect caches the row data on demand, on the client, on each call to ResultSet.next. However, when the end of the result set is reached, the entire result set is stored in the client memory. Because this may cause a performance strain, Sybase recommends that you use TYPE_SCROLL_INSENSITIVE result sets only with Adaptive Server 15.0 or when the result set is reasonably small.

jConnect provides the ExtendResultSet in the sample2 directory; this sample provides a limited TYPE_SCROLL_INSENSITIVE ResultSet using JDBC 1.0 interfaces.

This implementation uses standard JDBC 1.0 methods to produce a scroll-insensitive, read-only result set, that is, a static view of the underlying data that is not sensitive to changes made while the result set is open. ExtendedResultSet caches all of the ResultSet rows on the client. Be cautious when you use this class with large result sets.

The sample.ScrollableResultSet interface:

• Is an extension of JDBC 1.0 java.sql.ResultSet.

• Defines additional methods that have the same signatures as the JDBC 2.0 java.sql.ResultSet.

• Does not contain all of the JDBC 2.0 methods. The missing methods deal with modifying the ResultSet.

The methods from the JDBC 2.0 API are:

boolean previous() throws SQLException;

boolean absolute(int row) throws SQLException;
boolean relative(int rows) throws SQLException;

boolean first() throws SQLException;
boolean last() throws SQLException;
void beforeFirst() throws SQLException;
void afterLast() throws SQLException;

boolean isFirst() throws SQLException;
boolean isLast() throws SQLException;
boolean isBeforeFirst() throws SQLException;
boolean isAfterLast() throws SQLException;

int getFetchSize() throws SQLException;
void setFetchSize(int rows) throws SQLException;
int getFetchDirection() throws SQLException;
void setFetchDirection(int direction) throws SQLException;

int getType() throws SQLException;
int getConcurrency() throws SQLException;
int getRow() throws SQLException;

To use the sample classes, create an ExtendedResultSet using any JDBC 1.0 java.sql.ResultSet. Below are the relevant pieces of code (assume a Java 1.1 environment):

// import the sample files
import sample.*;

//import the JDBC 1.0 classes
import java.sql.*;

// connect to some db using some driver;
// create a statement and a query;

// Get a reference to a JDBC 1.0 ResultSet
ResultSet rs = stmt.executeQuery(_query);

// Create a ScrollableResultSet with it
ScrollableResultSet srs = new ExtendedResultSet(rs);

// invoke methods from the JDBC 2.0 API
srs.beforeFirst();

// or invoke methods from the JDBC 1.0 API
if (srs.next())
  String column1 = srs.getString(1);

See the JDBC 2.0 API at http://java.sun.com/products/jdbc/jdbcse2.html for more details.