SQL Programming Manual for Pascal

Using Dynamic SQL
HP NonStop SQL Programming Manual for Pascal—528614-001
7-7
Dynamic SQL Programming Techniques
display column names for output to the user, you should declare one or more names
buffers.
You can generate an SQLDA template and allocate a names buffer by entering the
INCLUDE SQLDA directive. The generated SQLDA template has the format shown
with INCLUDE SQLDA in Section 6, Error and Status Processing.
Example. To process the SELECT statement
SELECT EMPNUM, DEPT FROM =EMPLOYEE WHERE SALARY > ?SAL
the program performs the following tasks:
•
Issues the PREPARE statement to dynamically compile the SELECT statement.
The size of the input and output SQLDAs can be determined from the SQLSA
returned by PREPARE.
•
Declares an input SQLDA and an output SQLDA. If needed, the program also
declares corresponding input and output names buffers.
•
Uses the DESCRIBE INPUT statement to retrieve the description for the input
parameter ?SAL into an input SQLDA. The input SQLDA will need at least one
SQLVAR entry for the parameter.
•
Uses the DESCRIBE statement to retrieve the EMPNUM and DEPT descriptions
for an output SQLDA. The output SQLDA will need at least two SQLVAR entries,
one for each column.
•
Sets the VAR_PTR fields in the SQLDAs to point to the input and output data
buffers. Sets the IND_PTR fields to the addresses of any associated indicator
variables or to a null address.
When you do not know the number of input parameters or output variables ahead of
time, you can allocate the SQLDA structures in one of two ways:
•
Issue the INCLUDE SQLDA statement and give large numbers for sqlvarcount
and name-string-size.
•
Allocate the SQLDA and names buffer at run time, as required by each dynamic
SQL statement that is processed.
Dynamic SQL Programming Techniques
The simplest dynamic SQL program does not have any input parameters or output
variables. This kind of program might process statements such as CREATE TABLE or
DROP INDEX, which do not require input parameters or output variables. A dynamic
SQL program that executes a dynamic SELECT statement is more complex; it does
not know the number of input parameters or SELECT columns until run time.
This subsection describes programming techniques for dynamic SQL programs that
include:
•
Input parameters and output variables