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 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 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 setCursorName, then the ResultSet returned from executeQuery is a SybCursorResultSet that is TYPE_FORWARD_ONLY and CONCUR_UPDATABLE.
If you call setFetchSize, then the ResultSet returned from executeQuery is a SybCursorResultSet that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

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

...