Progress
SQL-92
Guide and Reference


Retrieving Data: the SQLCursor Class

Methods of the SQLCursor class let stored procedures retrieve rows of data. When stored procedures create an object from the SQLCursor class, they pass as an argument an SQL statement that generates a result set. The SQL statement is either a SELECT or CALL statement:

Either way, once the procedure creates an object from the SQLCursor class, the processing of result sets follows the same steps:

  1. Open the cursor with the SQLCursor.Open method.
  2. Check whether there are any records in the result set with the SQLCursor.Found method.
  3. If there are records in the result set, loop through the result set:
    • Try to fetch a record with the SQLCursor.Fetch method.
    • Check whether the fetch returned a record with the SQLCursor.Found method.
    • If the fetch operation returned a record, assign values from the result-set record’s fields to procedure variables or procedure output parameters with the SQLCursor.getValue method.
    • Process the data.
    • If the fetch operation did not return a record, exit the loop.
  4. Close the cursor with the SQLCursor.close method.
EXAMPLE

This example uses SQLCursor to process the result set returned by an SQL SELECT statement:

CREATE PROCEDURE get_sal ()
 
BEGIN
    String ename = new String (20) ;
    BigDecimal esal = new BigDecimal () ;
    SQLCursor empcursor = new SQLCursor (
        "SELECT name, sal FROM emp " ) ;
    
    empcursor.Open () ;
    empcursor.fetch ();
        while (empcursor.found ())
        {
            empcursor.getValue (1, ename);
            empcursor.getValue (2, esal);
            //  do something with the values here
        }
    empcursor.close () ;
END 

Stored procedures also use SQLCursor objects to process a result set returned by another stored procedure. Instead of a SELECT statement, the SQLCursor constructor includes a CALL statement that invokes the desired procedure.

EXAMPLE

The following example shows an excerpt from a stored procedure that processes the result set returned by another procedure, get_customers:

SQLCursor cust_cursor = new SQLCursor (
   "CALL get_customers (?) ") ;
    cust_cursor.setParam (1, "NE");
    cust_cursor.Open () ;
    for (;;)
    {
        cust_cursor.Fetch ();
        if (cust_cursor.Found ())
        {
            cust_number = (Integer) cust_cursor.getValue (1, INTEGER);
            cust_name = (String) cust_cursor.getValue (2, CHAR) ;
        }
        else
                break;
    }
    cust_cursor.close () ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095