Programmer's Reference jConnect for JDBC 6.0

Home

Programmer's Reference jConnect for JDBC 6.0

Chapter 2 Programming Information

Working with databases

Using cursors with result sets

jConnect 6.0 implements many JDBC 2.0 cursor and update methods. These methods make it easier to use cursors and to update rows in a table based on values in a result set.

In JDBC 2.0, ResultSets are characterized by their type and their concurrency. The type and concurrency values are part of the java.sql.ResultSet interface and are described in its javadocs.

Table 2-5 identifies the characteristics of java.sql.ResultSet that are available in jConnect 6.0.

Table 2-5: java.sql.ResultSet options available in jConnect 6.0

Concurrency	Type
Concurrency	TYPE_FORWARD_ ONLY	TYPE_SCROLL_ INSENSITIVE	TYPE_SCROLL_ SENSITIVE
CONCUR_READ_ONLY	Supported in 6.0	Supported in 6.0	Not available in 6.0
CONCUR_UPDATABLE	Supported in 6.0	Not available in 6.0	Not available in 6.0

This section includes the following topics:

Creating a cursor

There are two methods for creating a cursor using jConnect:

SybStatement.setCursorName
Use SybStatement.setCursorName, to explicitly assign the cursor a name. The signature for SybStatement.setCursorName is:
```
void setCursorName(String name) throws SQLException;
```
SybStatement.setFetchSize
Use SybStatement.setFetchSize to create a cursor and specify the number of rows returned from the database in each fetch. The signature for SybStatement.setFetchSize is:
```
void setFetchSize(int rows) throws SQLException;
```
When you use setFetchSize to create a cursor, the jConnect driver names the cursor. To get the name of the cursor, use ResultSet.getCursorName.

Another way you can create cursors is to specify the kind of ResultSet you want returned by the statement, using the following JDBC 2.0 method on the connection:

Statement createStatement(int resultSetType, int resultSetConcurrency)throws SQL Exception

The type and concurrencies correspond to the types and concurrencies found on the ResultSet interface listed in Table 2-5. If you request an unsupported ResultSet, a SQL warning is chained to the connection. When the returned Statement is executed, you receive the kind of ResultSet that is most like the one you requested. See the JDBC 2.0 specification for more details on the behavior of this method.

If you do not use createStatement, the default types of ResultSet are:

If you call only Statement.executeQuery, then the ResultSet returned is a SybResultSet that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
If you call setFetchSize or setCursorName, then the ResultSet returned from executeQuery is a SybCursorResultSet that is TYPE_FORWARD_ONLY and CONCUR_UPDATABLE.

To verify that the kind of ResultSet object is what you intended, use the following two ResultSet methods:

int getConcurrency() throws SQLException;

int getType() throws SQLException;

Creating and using a cursor

Create the cursor using Statement.setCursorName or SybStatement.setFetchSize.
Invoke Statement.executeQuery to open the cursor for a statement and return a cursor result set.

Invoke ResultSet.next to fetch rows and position the cursor in the result set.

The following example uses each of the two methods for creating cursors and returning a result set. It also uses ResultSet.getCursorName to get the name of the cursor created by SybStatement.setFetchSize.

// With conn as a Connection object, create a 
// Statement object and assign it a cursor using 
// Statement.setCursorName().
Statement stmt = conn.createStatement();
stmt.setCursorName("author_cursor");

// Use the statement to execute a query and return
// a cursor result set.
ResultSet rs = stmt.executeQuery("SELECT au_id,

      au_lname, au_fname FROM authors
      WHERE city = 'Oakland'");
while(rs.next())
{
...
}
 
// Create a second statement object and use
// SybStatement.setFetchSize()to create a cursor
// that returns 10 rows at a time. 
SybStatement syb_stmt = conn.createStatement();
syb_stmt.setFetchSize(10);
 
// Use the syb_stmt to execute a query and return
// a cursor result set.
SybCursorResultSet rs2 =
      (SybCursorResultSet)syb_stmt.executeQuery
      ("SELECT au_id, au_lname, au_fname FROM authors
       WHERE city = 'Pinole'");
while(rs2.next())
{
...
}
 
// Get the name of the cursor created through the 
// setFetchSize() method.
String cursor_name = rs2.getCursorName();
 ...

// For jConnect 6.0, create a third statement
// object using the new method on Connection, 
// and obtain a SCROLL_INSENSITIVE ResultSet.
// Note: you no longer have to downcast the
// Statement or the ResultSet.

Statement stmt = conn.createStatement(
                 ResultSet.TYPE_SCROLL_INSENSITIVE,
                 ResultSet.CONCUR_READ_ONLY);

ResultSet rs3 = stmt.executeQuery
   ("SELECT ... [whatever]");

// Execute any of the JDBC 2.0 methods that 
// are valid for read only ResultSets.

rs3.next();
rs3.previous();
rs3.relative(3);
rs3.afterLast();

...

Using JDBC 1.x methods for positioned updates and deletes

The following example shows how to use methods in JDBC 1.x to do a positioned update. The example creates two Statement objects, one for selecting rows into a cursor result set, and the other for updating the database from rows in the result set.

Although this manual provides sample code relating to JDBC 1.0 and 2.0 methods, Sybase strongly suggests that you use JDBC 2.0 for ease of use and portability.

// Create two statement objects and create a cursor
// for the result set returned by the first 
// statement, stmt1. Use stmt1 to execute a query 
// and return a cursor result set.
Statement stmt1 = conn.createStatement();
Statement stmt2 = conn.createStatement();
stmt1.setCursorName("author_cursor");
ResultSet rs = stmt1.executeQuery("SELECT
    au_id,au_lname, au_fname
    FROM authors WHERE city = 'Oakland'
    FOR UPDATE OF au_lname");
 
// Get the name of the cursor created for stmt1 so 
// that it can be used with stmt2.
String cursor = rs.getCursorName();
 
// Use stmt2 to update the database from the 
// result set returned by stmt1.
String last_name = new String("Smith");
while(rs.next())

{
    if (rs.getString(1).equals("274-80-9391"))
     {
       stmt2.executeUpdate("UPDATE authors "+
       "SET au_lname = "+last_name +
       "WHERE CURRENT OF " + cursor);
    }
}

Deletions in a result set

The following example uses Statement object stmt2, from the preceding code, to perform a positioned deletion:

stmt2.executeUpdate("DELETE FROM authors
          WHERE CURRENT OF " + cursor);

Using JDBC 2.0 methods for positioned updates and deletes

This section discusses JDBC 2.0 methods for updating columns in the current cursor row and updating the database from the current cursor row in a result set. Each is followed by an example.

Updating columns in a result set

JDBC 2.0 specifies a number of methods for updating column values from a result set in memory, on the client. The updated values can then be used to perform an update, insert, or delete operation on the underlying database. All of these methods are implemented in the SybCursorResultSet class.

Examples of some of the JDBC 2.0 update methods available in jConnect are:

void updateAsciiStream(String columnName, java.io.InputStream x, 
   int length) throws SQLException;

void updateBoolean(int columnIndex, boolean x) throws 
   SQLException;

void updateFloat(int columnIndex, float x) throws SQLException;

void updateInt(String columnName, int x) throws SQLException;

void updateInt(int columnIndex, int x) throws SQLException;

void updateObject(String columnName, Object x) throws 
   SQLException;

Methods for updating the database from a result set

JDBC 2.0 specifies two new methods for updating or deleting rows in the database, based on the current values in a result set. These methods are simpler in form than Statement.executeUpdate in JDBC 1.x and do not require a cursor name. They are implemented in SybCursorResultSet:

void updateRow() throws SQLException;
void deleteRow() throws SQLException;

The concurrency of the result set must be CONCUR_UPDATABLE. Otherwise, the above methods raise an exception. For insertRow, all table columns that require non-null entries must be specified. Methods provided on DatabaseMetaData dictate when these changes are visible.

Example

The following example creates a single Statement object that is used to return a cursor result set. For each row in the result set, column values are updated in memory and then the database is updated with the new column values for the row.

// Create a Statement object and set fetch size to 
// 25. This creates a cursor for the Statement 
// object Use the statement to return a cursor
// result set.
SybStatement syb_stmt = 
(SybStatement)conn.createStatement();
syb_stmt.setFetchSize(25);
SybCursorResultSet syb_rs = 
(SybCursorResultSet)syb_stmt.executeQuery(
    "SELECT * from T1 WHERE ...")
 
// Update each row in the result set according to
// code in the following while loop. jConnect 
// fetches 25 rows at a time, until fewer than 25 
// rows are left. Its last fetch takes any 
// remaining rows.
while(syb_rs.next())
{
  // Update columns 2 and 3 of each row, where 
// column 2 is a varchar in the database and 
// column 3 is an integer.
  syb_rs.updateString(2, "xyz");
syb_rs.updateInt(3,100);
//Now, update the row in the database.
  syb_rs.updateRow();
}

// Create a Statement object using the
// JDBC 2.0 method implemented in jConnect 6.0
Statement stmt = conn.createStatement
(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

// In jConnect 6.0, downcasting to SybCursorResultSet is not
// necessary. Update each row in the ResultSet in the same
// manner as above
while (rs.next())
{
rs.updateString(2, "xyz");
rs.updateInt(3,100);
  rs.updateRow();

// Use the Statement to return an updatable ResultSet
ResultSet rs = stmt.executeQuery("SELECT * FROM T1 WHERE...");

Deleting a row from a ResultSet

To delete a row from a cursor result set, you can use SybCursorResultSet.deleteRow as follows:

 while(syb_rs.next())
 {
     int col3 = getInt(3);
     if (col3 >100)
     {
     syb_rs.deleteRow();
     }
 }

Inserting a row into a ResultSet

The following example illustrates how to do inserts using the JDBC 2.0 API. There is no need to downcast to a SybCursorResultSet.

// prepare to insert
rs.moveToInsertRow();

// populate new row with column values
rs.updateString(1, "New entry for col 1");
rs.updateInt(2, 42);

// insert new row into db
rs.insertRow();

// return to current row in result set
rs.moveToCurrentRow();

Using a cursor with a PreparedStatement object

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 must 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

Using TYPE_SCROLL_INSENSITIVE result sets in jConnect

jConnect version 6.0 supports only TYPE_SCROLL_INSENSITIVE result sets.

jConnect uses the Tabular Data Stream (TDS)--the Sybase proprietary protocol--to communicate with Sybase database servers. As of jConnect 6.0, TDS does not support scrollable cursors. To support 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 when the result set is reasonably small.

When you use TYPE_SCROLL_INSENSITIVE ResultSets in jConnect 6.0, you can only call the isLast method after the last row of the ResultSet has been read. Calling isLast before the last row is reached causes an UnimplementedOperationException to be thrown.

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 new 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);

Figure 2-1 is a class diagram that shows the relationships between the new sample classes and the JDBC API.

Figure 2-1: Class diagram raster

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


	Expand Search