SQL Programming Manual for TAL

Dynamic NonStop SQL Operations
HP NonStop SQL Programming Manual for TAL—527887-001
7-12
Overview of a Dynamic SQL Program
5. Loop through the SQLVAR array in the output SQLDA (loop n times, where n is the
number of columns from sqlsa.PREPARE.OUTPUT^NUM). On each iteration:
•
Check DATA^TYPE. If necessary, adjust the data type and reset DATA^LEN
and PRECISION accordingly.
•
Allocate a data buffer with size equal to DATA^LEN for the output column.
GETPOOL allocates the memory.
DATA^LEN is used differently for different data types. For example, for a binary
numeric item the upper byte contains the scale, so you must ignore the upper byte
if you are not handling scale. For more information, see Table 6-4
on page 6-17.
•
Set VAR^PTR to point to the memory.
If you are not allocating memory dynamically, you will have declared a variable for
each possible column value; therefore, put the address of the variable in
VAR^PTR.
If you know the number and data type of your output column values, you can
simply set DATA^TYPE, DATA^LEN, and VAR^PTR.
Some programs might check DATA^TYPE and DATA^LEN again when the actual
values are obtained.
•
If you are handling null values, check NULL^INFO and continue as follows
according to its value:
-1 Allocate two bytes of memory for the indicator variable.
0 Do not allocate any memory.
•
If necessary, set IND^PTR to point to the memory allocated in the previous
step. (If you are not allocating memory dynamically, you will define a variable
for the indicator and put its address in IND^PTR.)
6. To show column headings (as SQLCI does), loop through the names buffer to read
the corresponding name for each column and display the column names.
Performing the Database Request and Displaying the Values
If the statement is a SELECT (or if sqlsa.PREPARE.SQL^STATEMENT^TYPE is
SQL^STATEMENT^SELECT), perform Steps 1 through 8. If the statement is not a
SELECT statement, perform Steps 2, 6, and 7.
1. Declare a cursor to handle the SELECT statement.
! Using cursor and statement names:
EXEC SQL DECLARE c1 CURSOR FOR s1;
! Using cursor and statement host variables:
EXEC SQL DECLARE :cursor^hostvar CURSOR
FOR :statement^hostvar;