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:
-
Declare a cursor.
-
Open it (which conceptually 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.
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
