Creating a cursor

To create a cursor using jConnect there are two methods:

• 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 cursor’s name, 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 will receive the kind of ResultSet that is most like the one you requested. See the JDBC 2.0 specification for more details on this method’s behavior.

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;

The basic steps for creating and using a cursor are:

1. Create the cursor using Statement.setCursorName( ) or SybStatement.setFetchSize( ).

2. Invoke Statement.executeQuery( ) to open the cursor for a statement and return a cursor result set.

3. 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 5.x, 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();
   

   ...
   

Copyright © 2003. Sybase Inc. All rights reserved.

View this book as PDF