Connecting to Your Database
Using Embedded SQL with Microsoft SQL Server

In embedded SQL, statements that retrieve data can involve cursors. These statements are:

Note  UPDATE ... WHERE CURRENT OF cursor_name and DELETE ... WHERE CURRENT OF cursor_name are not supported in SQL Server.

Retrieval

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:

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:

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.

Scrolling and locking

Use the CursorScroll and CursorLock DBParm parameters to specify the scrolling and locking options.

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.

See also

Microsoft SQL Server Fetching rows

Microsoft SQL Server Closing the cursor