Microsoft SQL Server Cursor statements – PB Docs 126

Microsoft SQL Server Cursor statements

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

  • DECLARE cursor_name CURSOR
    FOR …

  • OPEN cursor_name

  • FETCH cursor_name INTO

  • CLOSE cursor_name

note.png

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:

However, when a 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
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:

See also


Document get from Powerbuilder help
Thank you for watching.
Was this article helpful?
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x