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 a CALL statement:

  • A SELECT statement queries the database and returns data that meets the criteria specified by the query expression in the SELECT statement.
  • A CALL statement invokes another stored procedure that returns a result set specified by the RESULT clause of the CREATE PROCEDURE statement.

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

To process result sets:

  1. Open the cursor by using the SQLCursor.open method.
  2. Check whether there are any records in the result set by using the SQLCursor.found method.
  3. If there are records in the result set, loop through the result set to:
    • Fetch a record by using the SQLCursor.fetch method.
    • Check whether the fetch returned a record with the SQLCursor.found method.
    • Assign values from the result‑set record's fields to procedure variables or procedure output parameters by using the SQLCursor.getValue method.
    • Process the data, or
    • Exit the loop if the fetch operation did not return a record.
  4. Close the cursor by using the SQLCursor.close method.

The following example uses SQLCursor to process the result set returned by an SQL SELECT statement.


CREATE PROCEDURE get_sal ()
IMPORT
import java.math.*;
BEGIN
Integer eid = new Integer (1) ;
BigDecimal esal = new BigDecimal (2) ;
SQLCursor empcursor = new SQLCursor (
"SELECT empid, sal FROM emp " ) ;
empcursor.open () ;
empcursor.fetch ();
while (empcursor.found ())
{eid = (Integer) empcursor.getValue (1, INTEGER);
esal = (BigDecimal) empcursor.getValue (2, NUMERIC);
// 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.

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