Informix retrieval using cursors
Retrieval using cursors is conceptually similar to the singleton
SELECT discussed earlier. The main difference is that there can
be multiple rows in a result set when you use a cursor and you control
when the next row is fetched into PowerScript variables.
If you expect only a single row to exist in the employee table
with the specified emp_id, use the singleton SELECT. In
a singleton SELECT, you specify the SELECT statement and destination
variables in one concise SQL statement:
|
1 |
SELECT emp_name, emp_salary<br> INTO :emp_name_var, :emp_salary_var<br> FROM employee WHERE emp_id = :emp_id_var; |
However, when a SELECT may return multiple rows, you must:
-
Declare a cursor.
-
Open it (which effectively executes the SELECT).
-
Fetch rows as needed.
-
Close the cursor.
Declaring and opening a cursor
Declaring a cursor is tightly coupled with the OPEN statement.
The DECLARE specifies the SELECT statement to be executed, and the
OPEN actually executes it.
Scroll cursors
When you fetch rows in an Informix database table, using a
scroll cursor allows you to fetch rows in the active set in any
sequence. That is, you can fetch the next row, previous row, last
row, or first row.
To specify that you want to use a scroll cursor when connecting
to an Informix database, set the Scroll DBParm parameter to 1. By
default, PowerBuilder does not use scroll cursors in an Informix
connection (the Scroll parameter is set to 0).
You cannot update scroll cursors
Scroll cursors are not updatable. If you try to declare a
scroll cursor and make it updatable, it will fail.