Progress
SQL-92
Guide and Reference


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. 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 DhSQLResultSet class provides a way for a procedure to store rows of data in a procedure result set so the rows can be returned to the application that calls it. 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:

When the SQL server 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.

EXAMPLE

The next example extends the get_sal procedure to return a procedure result set. For each row of the SQL result set assigned to procedure variables, the procedure:

CREATE PROCEDURE get_sal2 ()
RESULT (
    empname CHAR(20),
    empsal   NUMERIC
)
BEGIN
    String ename = new String (20) ;
    BigDecimal esal = new BigDecimal () ;
    SQLCursor empcursor = new SQLCursor (
        "SELECT name, sal FROM emp " ) ;
 
    empcursor.Open () ;
    do
    {
        empcursor.Fetch ();
        if (empcursor.found ())
        {
               ename = (String) 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 

\


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