SQL Programming Manual for Pascal

Using Dynamic SQL
HP NonStop SQL Programming Manual for Pascal—528614-001
7-5
Using the SQLDA and Names Buffer
The following sequence shows a typical context for input parameters and output
variables in dynamic SQL operations. If you know in advance which columns will be
selected, you can use this sequence.
HOSTVAR := 'SELECT EMPNUM, DEPT FROM =EMPLOYEE
WHERE SALARY > ?SAL';
{ ?SAL is the input parameter }
... Dynamically compile the statement, use DESCRIBE INPUT to
get information about input parameters, prompt the user and
read in the value for ?SAL, declare and open a cursor for the
statement ...
EXEC SQL
FETCH cursor INTO :ENUM, :DEPT;
{ :ENUM and :DEPT are the output variables }
If you do not know in advance which columns to select, you can dynamically compile
the statement, use DESCRIBE to find out which columns are being selected, and then
allocate data buffers to receive the column values. The SQLDA structure contains
pointers to the buffers. In this case, the FETCH statement would look like this:
EXEC SQL
FETCH cursor
USING DESCRIPTOR : sqlda;
{ sqlda contains pointers to output data buffers }
Internally, SQL execution is the same for both options.
Using the SQLDA and Names Buffer
To allocate storage for information about input parameters and output variables in a
dynamic SQL statement, the dynamic SQL program declares one or more instances of
the SQLDA and names buffer. A description of the SQLDA structure and its fields
appears in Section 6, Error and Status Processing.
The SQLDA holds the following information:
•
The number of input parameters or output variables the SQLDA can
accommodate, in the NUM_ENTRIES field
•
For each input parameter or output variable, an SQLVAR structure with the
following information:
°
The data type in the DATA_TYPE field (data type literals are available for
Pascal programs and are documented under Allocating Memory for the Values
on page 7-22)
°
The length and scale or date-time qualifier in the DATA_LEN field
°
The leading field precision and (if applicable) fraction precision for a datetime
or INTERVAL item, or the numeric precision for a FLOAT or binary numeric
item, in the PRECISION field