SQL Programming Manual for Pascal

Using Dynamic SQL
HP NonStop SQL Programming Manual for Pascal—528614-001
7-10
Overview of a Dynamic SQL Program
1. Get the number of input parameters and the length of the names buffer (for
parameter names) from the SQLSA structure (SQLSA.PREPARE.INPUT_NUM
and SQLSA.PREPARE.INPUT_NAMES_LEN).
2. Allocate memory for the input SQLDA (and names buffer, if needed). This example
uses the procedure ALLOCATE_SQLDA.
3. Initialize the SQLDA header fields (SQLDA_EYE_CATCHER is defined by the
Pascal compiler):
INPUT_SQLDA_PTR^.EYE_CATCHER := SQLDA_EYE_CATCHER;
INPUT_SQLDA_PTR^.NUM_ENTRIES := SQLSA.PREPARE.INPUT_NUM;
4. Specify a DESCRIBE INPUT statement to access input parameters:
Using a statement name:
EXEC SQL DESCRIBE INPUT S1
INTO :INPUT_SQLDA_PTR^
NAMES INTO :INPUT_NAMESBUF_PTR^;
Using a statement host variable:
EXEC SQL DESCRIBE INPUT :STATEMENT_HOSTVAR
INTO :INPUT_SQLDA_PTR^
NAMES INTO :INPUT_NAMESBUF_PTR^;
5. Loop through the SQLVAR array in the input SQLDA (loop n times, where n is the
number of parameters from SQLSA.PREPARE.INPUT_NUM). On each iteration:
•
Check SQLVAR.DATA_TYPE. If necessary, adjust the data type and reset
SQLVAR.DATA_LEN and SQLVAR.PRECISION accordingly.
•
Allocate an amount of memory equal to SQLVAR.DATA_LEN for the
parameter.
•
Set SQLVAR.VAR_PTR to point to the memory.
•
If you are not allocating memory dynamically, you will have declared a variable
for each input parameter value, and put the address of the variable in
VAR_PTR.
•
If you know the number and data type of your input parameter 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.)