>>--EXEC SQL--SET SCROLLOPTION--.-KEYSET----.--END-EXEC-><
+-DYNAMIC------+
+-FORWARD------+
+-STATIC-------+
+-FASTFORWARD--+
+-FAST FORWARD-+
| 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. |
The BEHAVIOR compiler directive option must be set to UNOPTIMIZED.
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.
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:
If you try to set an option which is not supported by the ODBC driver, you will get an error (-19512).