SQL Programming Manual for Pascal

NonStop SQL Statements and Directives

HP NonStop SQL Programming Manual for Pascal—528614-001

3-9

Dynamic SQL Statements

Dynamic CONTROL directives are processed using a PREPARE statement and

executed using an EXECUTE statement or processed and executed using an

EXECUTE IMMEDIATE statement.

A dynamic CONTROL directive affects only dynamic SQL statements that are prepared

after the CONTROL directive in execution order.

Example

This example illustrates the rules for CONTROL directives with dynamic SQL

statements. In the following code, the EXECUTE statement is affected by the

CONTROL TABLE TIMEOUT directive but not by the CONTROL QUERY directive:

EXEC SQL PREPARE S FROM "SELECT * FROM =EMPLOYEE";

EXEC SQL CONTROL QUERY INTERACTIVE ACCESS ON;

EXEC SQL CONTROL TABLE =EMPLOYEE TIMEOUT 120 SECONDS;

EXEC SQL EXECUTE S;

DECLARE CURSOR

The DECLARE CURSOR statement defines a cursor and associates the cursor with a

SELECT statement. Through the cursor, a Pascal program can fetch, one by one, the

rows retrieved by the SELECT statement.

The DECLARE CURSOR statement follows these rules:

Static SQL Cursors

•

A static DECLARE CURSOR statement can be located in only the global

declarations part of a Pascal compilation unit.

•

All host variables referred to by the DECLARE CURSOR statement in the static

SQL program must have global scope and must be declared in listing order before

the DECLARE CURSOR statement that refers to them.

•

A static SQL cursor can be accessed only from the compilation unit in which its

DECLARE CURSOR statement occurs. This means that for static SQL programs,

the DECLARE CURSOR, OPEN, FETCH, DELETE WHERE CURRENT, UPDATE

WHERE CURRENT, and CLOSE statements must be in the same compilation unit.

If the program exits a procedure with an open cursor, procedures that execute later

and are within the same compilation unit can still refer to the cursor.

Note. One exception to this rule: the TIMEOUT option on a static or dynamic CONTROL

TABLE directive affects all dynamic SQL statements that follow in execution order, until another

static or dynamic CONTROL TABLE directive resets the option or the end of the program.