SQL Programming Manual for Pascal

Examples of Dynamic NonStop SQL Programs
HP NonStop SQL Programming Manual for Pascal—528614-001
C-7
Detailed Dynamic SQL Program
Detailed Dynamic SQL Program
This program allows the user to enter any statement. The program prepares and
executes the statement in a TMF transaction. The code is independent of any
database because the program does not reference database definitions; only the
entered statements reference a particular database.
The program performs the following operations, which characterize dynamic SQL
programs:
•
Declares an SQLSA to determine the number of input or output variables.
•
Declares an SQLDA to describe input parameters and another to describe output
variables.
•
Defines a buffer to store output variables, with storage for column values of
different data types.
•
Defines a buffer to store input parameters, with storage for parameter values of
different data types.
•
Prepares the SQL statement and assigns it a statement name.
•
Determines the data types of the input parameters and moves them to the host
variables of the corresponding data types.
•
Determines the data types of the output variables and moves them to the host
variables of the corresponding data types.
•
Sets up the SQLDA to point to the storage for the variables referenced by the
query.
•
Using the input SQLDA if there were parameters, either performs a cursor FETCH
for a SELECT statement or executes a non-SELECT statement.
Before running the program, command interpreter ADD DEFINE commands were
entered to associate tables ORDERS and ODETAIL with logical names =ORDERS and
=ODETAIL, respectively. The sample query shown selects order numbers and
customer numbers from the ORDERS table where the order includes part number
6400.
Sample output for the program is as follows:
33> run pdynobj
Enter a new SQL statement or REPEAT to use the previous one
or STOP to stop:
select ordernum, custnum from =orders where ordernum in