SAP Adaptive
Server Enterprise Retrieval Using Cursors
Retrieval using cursors is conceptually similar to retrieval in the
singleton SELECT. The main difference is that since there can be multiple
rows in a result set, you control when the next row is fetched into the
PowerScript data 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:
|
1 2 3 |
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 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 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. Since you must refer to the result set in
subsequent SQL statements, you must associate the result set with a
logical name.
Multiple cursors
The CT-Lib API lets you declare and open multiple cursors without
having to open additional database connections.
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 15 16 |
// 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
In the singleton SELECT, you specify variables to hold the values
for the columns within the selected row. The FETCH statement syntax is
similar to the syntax of the singleton SELECT. Values are returned INTO a
specified list of variables.
This example continues the previous example by retrieving some
data:
|
1 2 |
// Go get the first row from the result set. FETCH emp_curs INTO :emp_id_var, :emp_name_var; |
If at least one row can be retrieved, this FETCH places the values
of the emp_id and emp_name columns from the first row in the result set
into the PowerScript data variables emp_id_var and emp_name_var. Executing
another FETCH statement will place the variables from the next row into
specified variables.
FETCH statements typically occur in a loop that processes several
rows from a result set (one row at a time): fetch the row, process the
variables, and then fetch the next row.
What happens when the result set is exhausted?
FETCH returns +100 (not found) in the SQLCode property within the
referenced transaction object. This is an informational return code; -1
in SQLCode indicates an error.
Example
This cursor example illustrates how you can loop through a result
set. Assume the default transaction object (SQLCA) has been assigned valid
values and a successful CONNECT has been executed.
The statements retrieve rows from the employee table and then
display a message box with the employee name in each row that is
found.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
// Declare the emp_curs. DECLARE emp_curs CURSOR FOR SELECT emp_name FROM EMPLOYEE WHERE emp_state = :sle_1.text; // Declare a destination variable for employee // names. string emp_name_var // Execute the SELECT statement with the // current value of sle_1.text. OPEN emp_curs; // Fetch the first row from the result set. FETCH emp_curs INTO :emp_name_var; // Loop through result set until exhausted. DO WHILE SQLCA.sqlcode = 0 // Pop up a message box with the employee name. MessageBox("Found an employee!",emp_name_var) // Fetch the next row from the result set. FETCH emp_curs INTO :emp_name_var; LOOP |
Error checking
Although you should test the SQLCode after every SQL statement,
these examples show statements to test the SQLCode only to illustrate a
specific point.