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:

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:

  1. Declare a cursor.

  2. Open it (which effectively executes the SELECT).

  3. Fetch rows as needed.

  4. 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

Informix nonupdatable cursors

Informix updatable cursors