| SQL support / Result set processing | |
Associates a database cursor with an SQL statement producing a result set.
DECLARE cid [SCROLL] CURSOR [WITH HOLD] FOR select-statement
DECLARE cid [SCROLL] CURSOR [WITH HOLD] FOR sid
DECLARE cid [SCROLL] CURSOR [WITH HOLD] FROM expr
DECLARE cid [SCROLL] CURSOR [WITH HOLD] FOR SQL sql-statement END SQL
The DECLARE instruction allocates resources for an SQL statement handle, in the context of the current connection. The SQL text is sent to the database server for parsing, validation and to generate the execution plan.
After declaring the cursor, you can use the OPEN instruction to execute the SQL statement and produce the result set.
DECLARE must precede any other statement that refers to the cursor during program execution.
The scope of reference of the cid cursor identifier is local to the module where it is declared.
Resources allocated by the DECLARE cursor-name can be released later by the FREE cursor-name instruction.
The static select-statement used in the DECLARE can contain ? (question mark) parameter placeholders, that can be bound to program variables with the USING clause of the OPEN instruction.
The maximum number of declared cursors in a single program is limited by the database server and the available memory. Make sure that you free the resources when you no longer need the declared cursor.
When declaring a cursor with a static select-statement, the statement can include an INTO clause. However, to be consistent with prepared statements you better omit the INTO clause in the SQL text and use the INTO clause of the FETCH statement to retrieve the values from the result set.
You can add the FOR UPDATE clause in the SELECT statement to declare an update cursor. You can use the update cursor to modify (update or delete) the current row.
You should use the WITH HOLD option with care, because this feature is specific to IBM® Informix® servers. Other database servers do not behave as Informix does with this type of cursor. For example, if the SELECT is not declared FOR UPDATE, most database servers keep cursors open after the end of a transaction, but IBM DB2® automatically closes all cursors when the transaction is rolled back.
If you use only the DECLARE CURSOR keywords, you create a sequential cursor, which can fetch only the next row in sequence from the result set. The sequential cursor can read through the result set only once each time it is opened. If you are using a sequential cursor for a select cursor, on each execution of the FETCH statement, the database server returns the contents of the current row and locates the next row in the result set.
Cursors can be declare with a static SELECT statement:
MAIN DATABASE stores DECLARE c1 CURSOR FOR SELECT * FROM customer END MAIN
MAIN
DEFINE key INTEGER
DEFINE cust RECORD
num INTEGER,
name CHAR(50)
END RECORD
DATABASE stores
PREPARE s1
FROM "SELECT customer_num, cust_name FROM customer WHERE customer_num>?"
DECLARE c1 CURSOR FOR s1
LET key=101
FOREACH c1 USING key INTO cust.*
DISPLAY cust.*
END FOREACH
END MAIN
Use the DECLARE SCROLL CURSOR keywords to create a scrollable cursor, which can fetch rows of the result set in any sequence. Until the cursor is closed, the database server retains the result set of the cursor in a static data set (for example, in a temporary table like Informix). You can fetch the first, last, or any intermediate rows of the result set as well as fetch rows repeatedly without having to close and reopen the cursor. On a multiuser system, the rows in the tables from which the result set rows were derived might change after the cursor is opened and a copy of the row is made in the static data set. If you use a scroll cursor within a transaction, you can prevent copied rows from changing, either by setting the isolation level to REPEATABLE READ or by locking the entire table in share mode during the transaction. Scrollable cursors cannot be declared FOR UPDATE.
With most database servers, scrollable cursors take quite a few resources to hold a static copy of the result set. Therefore you should consider optimizing scrollable cursor usage by fetching only the primary keys of rows, and execute a secondary SELECT statement to fetch other fields for each row that must be displayed.
The DECLARE [SCROLL] CURSOR FROM syntax allows you to declare a cursor directly with a string expression, so that you do not have to use the PREPARE instruction. This simplifies the source code and speeds up the execution time for non-Informix databases, because the SQL statement is not parsed twice.
MAIN
DEFINE key INTEGER
DEFINE cust RECORD
num INTEGER,
name CHAR(50)
END RECORD
DATABASE stores
DECLARE c1 SCROLL CURSOR
FROM "SELECT customer_num, cust_name FROM customer WHERE customer_num>?"
LET key=101
FOREACH c1 USING key INTO cust.*
DISPLAY cust.*
END FOREACH
END MAIN
Use the WITH HOLD option with Informix databases to create a hold cursor. A hold cursor allows uninterrupted access to a set of rows across multiple transactions. Ordinarily, all cursors close at the end of a transaction. A hold cursor does not close; it remains open after a transaction ends. A hold cursor can be either a sequential cursor or a scrollable cursor. Hold cursors are only supported by Informix database engines.
You can use the ? question mark place holders with prepared or static SQL statements, and provide the parameters at execution time with the USING clause of the OPEN or FOREACH instructions.
MAIN
DEFINE key INTEGER
DEFINE cust RECORD
num INTEGER,
name CHAR(50)
END RECORD
DATABASE stores
DECLARE c1 CURSOR WITH HOLD
FOR SELECT customer_num, cust_name FROM customer WHERE customer_num > ?
LET key=101
FOREACH c1 USING key INTO cust.*
BEGIN WORK
UPDATE cust2 SET name=cust.cust_name WHERE num=cust.num
COMMIT WORK
END FOREACH
END MAIN