OLE DB Retrieval using cursors

Retrieval using cursors is conceptually similar to the singleton SELECT discussed earlier. The main difference is that since there can be multiple rows in a result set, you control when the next row is fetched into PowerScript variables.

For example, if you expect only a single row to exist in the employee table for each emp_id, use a singleton SELECT statement. 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, if the SELECT may return multiple rows, you must:

  1. Declare a cursor.

  2. Open it (which conceptually 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.

Declaring a cursor is similar to declaring a variable. A cursor declaration is a nonexecutable statement just like a variable declaration. The first step in declaring a cursor is to define how the result set looks. To do this, you need a SELECT statement,  and since you must refer to the result set in subsequent SQL statements, you must associate the result set with a logical name.

Example

Assume the SingleLineEdit sle_1 contains the state code for the retrieval:

// Declare cursor emp_curs for employee table 
// retrieval. 
DECLARE emp_curs CURSOR FOR 
   SELECT emp_id, emp_name FROM EMPLOYEE 
   WHERE emp_state = :sle_1.text;
// Declare local variables for retrieval.
string emp_id_var 
string emp_name_var
// Execute the SELECT statement with 
// the current value of sle_1.text. 
OPEN emp_curs;
// At this point, if there are no errors, 
// the cursor is available for further 
// processing.

Fetching rows

The PowerBuilder OLE DB interface supports FETCH statements.

See also

OLE DB FETCH NEXT