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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
// 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