FairCom Java & .NET Stored Procedures

Returning a Procedure Result Set with SQLResultSet

The get_sal() procedure in the previous example used the SQLCursor.getValue() method to store the values of a database record in individual variables. But the procedure did not do anything with those values, and they would be overwritten in the next iteration of the loop that fetches records. The RESULT section defines the columns

SQLResultSet derives from a lower DhSQLResultSet class and provides a way for a procedure to store rows of data in a procedure result set to be returned to the calling application. There can only be one procedure result set in a stored procedure.

A stored procedure must explicitly process a result set to return it to the calling application:

• Declare the procedure result set through the RESULT clause of the procedure specification
• Populate the procedure result set in the body of the procedure using the methods of SQLResultSet

When FairCom DB SQL creates a Java class from a CREATE PROCEDURE statement that contains the RESULT clause, it implicitly instantiates a SQLResultSet() object. Invoke methods of SQLResultSet() to populate fields and rows of the procedure result set.

SQLResultSet provides three methods:

• SQLResultSet.set(col, val) - Set column col with value val in a result set row. Column positions are referred by numerical value with 1 as the first column
• SQLResultSet.makeNULL(col) - Set column col as null
• SQLResultSet.insert() - Insert a populated row into the result set

Limit: SQLResultSet is limited to 50 columns.

The following example processes a result set from a SELECT statement to return a procedure result set. For each row of the FairCom DB SQL result set assigned to procedure variables, the procedure:

• Assigns the current values in the procedure variables to corresponding fields in the procedure result set with the SQLResultSet.set() method
• Inserts a row into the procedure result set with the SQLResultSet.insert() method

Example Returning a Procedure Result Set From a Stored Procedure

CREATE PROCEDURE get_sal2 ()

RESULT (

empname CHAR(20),

empsal NUMERIC

)

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

do

{

empcursor.fetch ();

if (empcursor.found ())

{

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

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

SQLResultSet.set (1, ename);

SQLResultSet.set (2, esal);

SQLResultSet.insert ();

}

} while (empcursor.found ()) ;

empcursor.close () ;

END

Note: The resultset parameter empname in the procedure above should be of type CHAR in ANSI versions of FairCom DB SQL.