Multiple ResultSet

Multiple ResultSet

PSM(Stored procedures and stored functions) for Altibase can return a multiple number of result sets to the client. Using an example that returns multiple result sets, this section offers instructions through a code example on how to use these result sets in JDBC applications.

The following is an example of a PSM which returns multiple result sets.

CREATE TYPESET my_type
AS
    TYPE my_cur IS REF CURSOR;
END;

CREATE PROCEDURE p1 (p1 OUT MY_TYPE.MY_CUR, p2 out MY_TYPE.MY_CUR)
AS
BEGIN
    OPEN p1 FOR 'SELECT * FROM t1';
    OPEN p1 FOR 'SELECT * FROM t2';
END;

The following is an example of a code which uses multiple result sets returned by a call to a PSM in a JDBC application.

CallableStatement sCallStmt = connection().prepareCall("{call p1()}");
sCallStmt.execute();
ResultSet sRs = null;
ResultSetMetaData sRsMd = null;


do{
    sRs = sCallStmt.getResultSet();
    sRsMd = sRs.getMetaData();

    if(sRsMd != null)
    {
        while(sRs.next())
        {
            // do something
            for(int i=1; i <= sRsMd.getColumnCount(); i++)
            {
                System.out.println(sRs.getString(i));
            }
        }
    }
}while(stmt.getMoreResults());
sCallStmt.close();