SQL Programming Manual for Pascal

Using Dynamic SQL
HP NonStop SQL Programming Manual for Pascal—528614-001
7-35
Using Statement and Cursor Host Variables
Using Cursors with an UPDATE WHERE CURRENT Clause
To use UPDATE WHERE CURRENT with a static cursor, you specify a FOR UPDATE
OF clause with a column list in the DECLARE CURSOR statement. In contrast, to use
UPDATE WHERE CURRENT with a dynamic SQL cursor you must specify a FOR
UPDATE OF clause in the SELECT statement associated with the cursor.
The following example shows an UPDATE WHERE CURRENT operation with a
dynamic SQL cursor. In the example, the host variable HOSTVAR contains the
SELECT statement to define the cursor. The host variable SALVAR receives the
selected values.
HOSTVAR := 'SELECT SALARY FROM =EMPLOYEE FOR UPDATE OF
SALARY';
EXEC SQL
PREPARE S1 FROM :HOSTVAR;
EXEC SQL
DECLARE C1 CURSOR FOR S1;
EXEC SQL
OPEN C1;
EXEC SQL
FETCH C1 INTO :SALVAR;
EXEC SQL
UPDATE =EMPLOYEE SET SALARY = SALARY * 1.20
WHERE CURRENT OF C1;
Using Statement and Cursor Host Variables
Pascal supports using statement and cursor host variables in dynamic SQL operations.
You can use host variables instead of statement and cursor names, with the DECLARE
CURSOR, PREPARE, OPEN, FETCH, and CLOSE statements. For each new
statement or cursor name, you store the name in the host variable before executing the
statements. Thus, you must code the statements only once.
Example of an Application
In a possible application for statement and cursor host variables, a server could use a
loop to initialize the arrays of statement and cursor host variable names and the array
of statements and to execute the PREPARE and DECLARE CURSOR statements. The
program could then use the cursors as follows:
{ Read $RECEIVE }
{ Examine a flag in the request message to determine }
{ which cursor to use }
CASE flag OF
BEGIN
{ Assign appropriate name to cursor-host-variable }