Micro Focus Enterprise Developer 3.0 for Visual Studio 2013 > Programming > Data Access > Database Access > Reference > OpenESQL > Embedded SQL Statements > SET SCROLLOPTION

SET SCROLLOPTION

Enables you to set cursor scrolling options.

Syntax:

>>--EXEC SQL--SET SCROLLOPTION--.-KEYSET----.--END-EXEC-><
                                +-DYNAMIC------+
                                +-FORWARD------+
                                +-STATIC-------+
                                +-FASTFORWARD--+
                                +-FAST FORWARD-+

Parameters:

`KEYSET`	In a keyset cursor, the membership and order of rows in the cursor result set is determined when the cursor is opened. A row will not be fetched if it is deleted or if it is updated such that it no longer meets the WHERE clause criteria. A row appears in the cursor result set only if it is inserted through a cursor based on a single table. Any updates made by the cursor owner and committed changes made by other users to any of the rows in the results set are visible.
`DYNAMIC`	With a dynamic cursor, membership of rows in the cursor result set is determined at fetch time and it can change between each fetch. A row will disappear from the cursor result set if it is deleted or if it is updated such that it no longer meets the WHERE clause criteria. A row appears in the result set if it is inserted or updated such that it meets the WHERE clause criteria. Any updates made by the cursor owner and committed changes made by other users to any of the rows in the result set are visible.
`FORWARD`	Equivalent to DYNAMIC, but the application can only move forward through the result set.
`STATIC`	In a static cursor, the result set appears to be static. Changes to the membership, order or values of the result set after the cursor is opened are not usually detected.
`FASTFORWARD` and `FAST FORWARD`	FAST FORWARD and FASTFORWARD are equivalent. This is a performance optimization parameter that applies only to FORWARD, READ-ONLY cursors. You can obtain even greater performance gains by also compiling the program with the AUTOFETCH directive; this is the most efficient method of getting a results set into an application. The AUTOFETCH directive enables two optimizations that can significantly reduce network traffic. The most dramatic improvement is seen when processing cursors with relatively small result sets that can be cached in the memory of an application. FASTFORWARD cursors work only with Microsoft SQL Server 2000 or later servers.

Dependencies:

The BEHAVIOR compiler directive option must be set to UNOPTIMIZED.

Comments:

The SET SCROLLOPTION statement is not supported by all ODBC drivers.

You should establish a connection before you use SET SCROLLOPTION.

The default is DYNAMIC unless:

The ESQLVERSION option of the SQL Compiler directive is set to 2.0, in which case the default is KEYSET.
The ODBC driver does not support DYNAMIC, in which case SET SCROLLOPTION is set to FORWARD (all ODBC drivers should support this setting).
FAST FORWARD works only with Microsoft SQL Server data sources.

If you try to set an option which is not supported by the ODBC driver, you will get an error (-19512).

Example:

 PROGRAM-ID. progname.
 WORKING-STORAGE SECTION.
 EXEC SQL INCLUDE SQLCA END-EXEC
 01      buffer      PIC x(32).
 01      cnt         PIC 9 COMP-5.

 PROCEDURE DIVISION.
     EXEC SQL CONNECT TO 'srv1' USER 'sa' END-EXEC
     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not connect to database.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
     END-IF
* Any cursors declared hereafter can be updated dynamically
     EXEC SQL SET SCROLLOPTION DYNAMIC END-EXEC
     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not set scroll option.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Ensure multiple tables are not created ...
     EXEC SQL DROP TABLE phil1 END-EXEC

* Create a table...
     EXEC SQL
        CREATE TABLE phil1
             (ident      char(3)
             ,textbit    char(3))
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not create table.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Insert some values into it...
     EXEC SQL
        INSERT INTO phil1
            (ident
            ,textbit)
        VALUES
            ('AAA'
            ,'BBB')
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not insert values.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

     EXEC SQL
        INSERT INTO phil1
            (ident
           ,textbit)
         VALUES
           ('CCC'
            ,'DDD')
    END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not insert values.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Declare a cursor...
     EXEC SQL
        DECLARE C1 CURSOR FOR
           SELECT ident
           FROM phil1
           WHERE textbit = 'BBB'
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not declare cursor.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Open it...
     EXEC SQL
        OPEN C1
     END-EXEC
     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not open cursor.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Update one of the rows in the table such that it now meets the
* cursor requirements...
     EXEC SQL
        UPDATE phil1
        SET textbit = 'BBB'
        WHERE ident = 'CCC'
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not update row.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Despite the row being updated to meet cursor requirement after
* the cursor was opened, SET SCROLLOPTION DYNAMIC should ensure
* that it is pointed to by the cursor. Check the displayed output
* to be sure...
    MOVE 0 TO cnt
    PERFORM UNTIL SQLCODE NOT = ZERO
       EXEC SQL
          FETCH C1 INTO :buffer
       END-EXEC
       IF SQLCODE = ZERO
          DISPLAY buffer
       END-IF
    END-PERFORM

    EXEC SQL
       CLOSE C1
    END-EXEC

    IF SQLCODE NOT = ZERO
       DISPLAY 'Error: Could not close cursor.'
       DISPLAY SQLERRMC
       DISPLAY SQLERRML
       EXEC SQL DISCONNECT ALL END-EXEC
       STOP RUN
    END-IF

    EXEC SQL DROP TABLE phil1 END-EXEC

    IF SQLCODE NOT = ZERO
       DISPLAY 'Error: Could not drop table.'
       DISPLAY SQLERRMC
       DISPLAY SQLERRML
       EXEC SQL DISCONNECT ALL END-EXEC
       STOP RUN
    END-IF

    EXEC SQL DISCONNECT CURRENT END-EXEC
    STOP RUN.