Returning a procedure result set to applications: the RESULT clause and DhSQLResultSet
The get_sal procedure in the previous example with a CREATE PROCEDURE uses the SQLCursor.getValue method to store the values of a database record in individual variables. The procedure did not, however, do anything with those values and they will be overwritten in the next iteration of the loop that fetches records.
The DhSQLResultSet class provides a way for a procedure to store rows of data in a procedure result set so that the rows can 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. For example:
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 the DhSQLResultSet class.
When the SQL engine creates a Java class from a CREATE PROCEDURE statement that contains the RESULT clause, it implicitly instantiates an object of type DhSQLResultSet, and calls it SQLResultSet. Invoke methods of the SQLResultSet instance to populate fields and rows of the procedure result set.
This example extends the get_sal procedure to return a procedure result set:
CREATE PROCEDURE get_sal2 ()
RESULT (
empname CHAR(20),
empsal NUMERIC
)IMPORT
import java.math.*;
BEGIN
StringBuffer ename = new StringBuffer (20) ;
BigDecimal esal = new BigDecimal (2) ;
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, NUMERIC);
// NUMERIC and DECIMAL are synonyms
SQLResultSet.set (1, ename);
SQLResultSet.set (2, esal);
SQLResultSet.insert ();
}} while (empcursor.found ()) ;
empcursor.close () ;
END
For each row of the 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 DhSQLResultSet.Set method
Inserts a row into the procedure result set with the DhSQLResultSet.Insert method
Declare the procedure result set through the RESULT clause of the procedure specification.