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:
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
