SQL Programming Manual for Pascal

Using Dynamic SQL
HP NonStop SQL Programming Manual for Pascal—528614-001
7-12
Overview of a Dynamic SQL Program
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 SQLVAR.NULL_INFO. If NULL_INFO is
0, do nothing. If NULL_INFO is -1, allocate 2 bytes of memory for the indicator
variable.
•
If NULL_INFO is -1, set SQLVAR.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 using the
XADDR function.)
If NULL_INFO is 0, it is good practice to set IND_PTR to an invalid address. A
recommended invalid address is %HFFFC0000%D, as shown in the detailed
dynamic SQL program in Appendix C, Examples of Dynamic NonStop SQL
Programs.
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
Perform Steps 1 through 6, Step 8, and Step 9 if SQLSA.PREPARE.OUTPUT_NUM >
0. Otherwise, perform Steps 3, 7, and 8.
1. Assign a name to the cursor host variable:
CURSOR_HOSTVAR:= C1;
2. 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;
3. Begin a TMF transaction (for both SELECT and non-SELECT statements):
EXEC SQL BEGIN WORK;
4. Open the cursor:
Using a cursor name:
EXEC SQL OPEN C1
USING DESCRIPTOR :INPUT_SQLDA_PTR^;