Dynamic cursors

Cursors declared without a cursor description are referred to as dynamic cursors.

A dynamic cursor is declared with DECLARE CURSOR.
A dynamic cursor is associated with a dynamically compilable cursor description using PREPARE. As with a static cursor, the user can access the rows of a table with the OPEN, FETCH and CLOSE statements and update or delete rows in a table with the UPDATE CURRENT AND DELETE CURRENT statements.

As with dynamic SQL statements, dynamic cursors provide an increasing degree of flexibility, which is offset by the increased effort involved in programming:

SQL language resources	Number of parameters known at compilation time	Data types of the parameters known at compilation time	Values of parameters
Static cursor	yes	yes	Can be passed in host variables
Dynamic cursor DECLARE CURSOR PREPARE OPEN... USING FETCH ... INTO CLOSE with host variables	yes	yes	Placeholders as dummy input parameters
Dynamic cursor DECLARE CURSOR PREPARE OPEN... USING FETCH ... INTO CLOSE with descriptor areas	no	no	Placeholders as dummy input parameters

Table 38: Increased flexibility when using dynamic cursors

If a dynamically compilable cursor description contains placeholders, the corresponding values must be made available in the USING clause of the OPEN statement via host variables or a descriptor area previously supplied with values.

An SQL descriptor area allows the following operations to be performed for a dynamically compiled cursor description:

• define the data types of the placeholders with DESCRIBE INPUT

• define the number and data types of the derived columns and store them in a descriptor area with DESCRIBE OUTPUT

• derive the values for the placeholders from a host variable or a descriptor area using OPEN USING

• store the values of a row in the derived table in a descriptor area with FETCH INTO.

Dynamic block mode cursor

In the same way as for a static cursor (see "Data manipulation under SQL using cursors"), block mode can be defined for a dynamic cursor. This is done by specifying the PREFETCH pragma in the cursor description for the dynamic cursor.

If a FETCH statement is used with a descriptor area, this descriptor area must not be addressed in SET DESCRIPTOR, DESCRIBE or DEALLOCATE DESCRIPTOR statements as long as block mode is activated. In addition, the same restrictions apply for a static cursor as for a static block mode cursor.

Example

DECLARE dyn_cursor CURSOR FOR dynstmt

Read SOURCESTMT: 

'--%PRAGMA PREFETCH 10 SELECT cust_num, company FROM customers WHERE
country = 'D ''
PREPARE dynstmt FROM :SOURCESTMT
OPEN dyn_cursor

Loop until SQLSTATE = 00200:

FETCH NEXT FROM dyn_cursor INTO :CUST_NUM, :COMPANY

In the cursor description for the dynamic cursor DYN_CURSOR, the PREFETCH pragma is specified with a blocking factor of 10. On the first FETCH NEXT statement, the user receives the first row of the cursor table and the next 9 rows are read into the buffer at the same time. A block mode cursor must always be addressed with the same FETCH statement, i.e. the same FETCH statement in a loop or a subroutine. The next time this FETCH statement is executed, the second row of the cursor is read from the buffer far more quickly.