FairCom Java & .NET Stored Procedures

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 a FairCom DB SQL statement that generates a result set. The FairCom DB SQL statement is either a SELECT or 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 results 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 in some manner
   • If the fetch operation did not return a record, exit the loop
4. Close the cursor with the SQLCursor.close() method

The following example shows an example that uses SQLCursor() to process the result set returned by a FairCom DB SQL SELECT statement.

Processing a Result Set from a SELECT Statement

CREATE PROCEDURE get_sal ()

IMPORT

import java.math.BigDecimal;

BEGIN

StringBuffer ename = new StringBuffer (20) ;

BigDecimal esal = new BigDecimal () ;

SQLCursor empcursor = new SQLCursor (

"SELECT name, sal FROM emp " ) ;

empcursor.open () ;

empcursor.fetch ();

while (empcursor.found ())

{

ename = (StringBuffer)empcursor.getValue (1, CHAR);

esal = (BigDecimal)empcursor.getValue (2, DECIMAL);

empcursor.fetch();

}

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() customers which takes dept_no as input and returns two columns cust_number and cust_name as its result set.

Processing a Result Set from a CALL Statement

CREATE PROCEDURE get_customer()

IMPORT

import java.math.BigDecimal;

BEGIN

SQLCursor cust_cursor = new SQLCursor ("CALL get_customers (?) ") ;

Integer cust_number;

StringBuffer cust_name;

cust_cursor.setParam (1, new Integer(10));

cust_cursor.open ();

cust_cursor.fetch();

while (cust_cursor.found())

{

cust_number = (Integer)cust_cursor.getValue (1, INTEGER);

cust_name = (Stringz)cust_cursor.getValue (2, CHAR);

cust_cursor.fetch ();

}

cust_cursor.close () ;

END