Procedure result sets

You can scroll IBM i SQL procedure result sets.

1. Create the procedure with the cursor defined as scrollable.
   1. This is done by adding the SCROLL keyword into the cursor declaration inside the procedure definition.
      In the following two examples, the stored procedure returns a scrollable result set while the second one does not.

      • CREATE PROCEDURE MYLIB.SCROLLSP ( ) RESULT SETS 1 LANGUAGE SQL
        sqlproc: begin
        DECLARE CUR1 SCROLL CURSOR FOR 
        SELECT * FROM QIWS.QCUSTCDT;
        OPEN CUR1;
        SET RESULT SETS CURSOR CUR1;
        end 

      • CREATE PROCEDURE MYLIB.NOSCROLLSP ( ) RESULT SETS 1 LANGUAGE SQL
        sqlproc: begin
        DECLARE CUR1 CURSOR FOR 
        SELECT * FROM QIWS.QCUSTCDT;
        OPEN CUR1;
        SET RESULT SETS CURSOR CUR1;
        end

2. Code the application using ODBC to ask for a scrollable cursor type.
   1. Call the SQLSetStmtAttr API.
   2. Set the SQL_ATTR_CURSOR_TYPE option to SQL_CURSOR_DYNAMIC.

      If an attempt is made to scroll backwards with a procedure that did not specify a scrollable cursor, several different problems can occur. In most cases an error is returned from the server indicating scrolling is invalid, and in some cases incorrect data is returned.

      Even if the procedure returns multiple result sets, you can only use one cursor type. ODBC either returns an error or ignores the cursor type when a different cursor type is specified for the second result set. To use a scrollable result set as one of the result sets, the application needs to set the cursor type to be scrollable as defined above.

      Any attempts to use a result set cursor as an updateable cursor will return an error or be ignored. Procedure result sets are read-only.

      Cursor sensitivity may not be honored with procedure result sets, since the cursor was opened when the procedure was run. Cursor sensitivity is controlled by the way the cursor is defined when creating the procedure.