DECLARE CURSOR

Syntax, Format 1:

>>--EXEC SQL---.------------.------DECLARE cursor_name------>
               +-AT db_name-+

 >--.-------------.-----.---------.------.--------------.--->
    +-SENSITIVE---+     +-FORWARD-+      +-LOCK---------+
    +-INSENSITIVE-+     +-KEYSET--+      +-LOCKCC-------+
                        +-DYNAMIC-+      +-OPTIMISTIC---+
                        +-STATIC--+      +-OPTCC--------+
                        +-SCROLL--+      +-OPTCCVAL-----+
                        +-DATASET-+      +-READ ONLY----+
                                         +-READONLY-----+
                                         +-FASTFORWARD--+
                                         +-FAST FORWARD-+

 >--CURSOR-------.----------------.---------FOR------------->
                 +----WITH HOLD---+
                 +----WITH RETURN-.----------.-|
                                  +TO CALLER-+
        
      +-- ; ---------------------------------+
      V                                      |
 >----.----select_stmt-----------------------.-------------->
      +----stored_procedure_call_statement---+
      +----prepared_stmt_name----------------+
      +----OPTIMIZE FOR n ROWS---------------+
 >--.-------------------------------.----------------------->
    +-FOR READ ONLY-----------------+
    +-FOR UPDATE-.----------------.-+
                 +-OF column_list-+
 >------END-EXEC--------><

Syntax, Format 2:

Note: Format 2 is supported for SQL Server only.

>>--EXEC SQL---.------------.------DECLARE cursor_name------>
               +-AT db_name-+
 >--CURSOR FOR---result-set-generating-dml-statement-------->

 >------END-EXEC--------><

Parameters:

`db_name`	The name of a database that has been declared using DECLARE DATABASE.
`cursor_name`	Cursor name used to identify the cursor in subsequent statements. Cursor names can contain any legal filename character and be up to 30 characters in length. The first character must be a letter.
`select_stmt`	Any valid SQL SELECT statement, or a QUERY ODBC statement or a CALL statement for a stored procedure that returns a result set.
`prepared_stmt_name`	The name of a prepared SQL SELECT statement or QUERY ODBC statement.
`stored_procedure_call_stmt`	A valid stored procedure call which returns a result set.
`n`	The number of rows per block fetched when the cursor is opened. The value of `n` must be less than 1000.
`column_list`	A list of column-names, separated by commas.
`result-set-generating-dml-statement`	A SQL Server INSERT, non-positioned UPDATE, or DELETE statement with an OUTPUT clause.

Comments:

Two separately compiled programs cannot share the same cursor. All statements that reference a particular cursor must be compiled together.

The DECLARE CURSOR statement must appear before the first reference to the cursor. The SELECT statement runs when the cursor is opened. The following rules apply to the SELECT statement:

It cannot contain an INTO clause or parameter markers.
It can contain input host variables previously identified in a declaration section.
With some ODBC drivers, the SELECT statement must include a FOR UPDATE clause if positioned updates or deletions are to be performed.

If OPTIMIZE FOR is specified, OpenESQL uses n to override the setting of the PREFETCH directive for the cursor. This allows prefetch optimization for individual cursors.

You can specify WITH RETURN in a SQL CLR stored procedure compiled with the HCOSS DIALECT=MAINFRAME directive. If you do so, the client application must use the ALLOCATE CURSOR and ASSOCIATE LOCATOR statements to retrieve the result set(s).

You can specify multiple SELECT statements in a DECLARE CURSOR statement, signifying the return of multiple result sets from either of the following. In either case, the client application must use the GET NEXT RESULT SET statement to retrieve additional result sets.

A COBOL stored procedure for SQL Server not compiled with DIALECT=MAINFRAME
A standard OpenESQL application program

The following applies to the behavior of certain DECLARE CURSOR options:

SCROLL selects a scroll option, other than FORWARD, that is supported by the driver.
DATASET enables a cursor that can be used with any BEHAVIOR setting. DATASET is required for applications that use the underlying .NET objects for interoperability with other .NET languages, and is available only for the OpenESQL Managed Runtime.
LOCKCC and LOCK are equivalent.
READ ONLY and READONLY are equivalent.
OPTIMISTIC selects an optimistic concurrency mode (OPTCC or OPTCCVAL) that is supported by the driver.
If a HOLD cursor is requested and the current connection closes cursors at the end of transactions, the OPEN statement will return an error (SQLCODE = -19520).
If the database is Microsoft SQL Server and the NOANSI92 ENTRY directive setting has been used (this is the default setting), then a Microsoft SQL Server specific ODBC call will be made at connect time to request that cursors are not closed at the end of transactions. This is compatible with the Micro Focus Embedded SQL Toolkit for Microsoft SQL Server. The setting for USECURLIB must not be YES.
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.

Example:

EXEC SQL DECLARE C1 CURSOR FOR
   SELECT last_name, first_name FROM staff
END-EXEC

EXEC SQL DECLARE C2 CURSOR FOR
   QUERY ODBC COLUMNS TABLENAME 'staff'
END-EXEC