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 2 3 |
SELECT emp_name, emp_salary INTO :emp_name_var, :emp_salary_var 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.
See also