SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
9-1
9 Dynamic SQL
Using the dynamic SQL statements of NonStop SQL/MX, programs can construct,
compile, and execute an SQL statement at run time. With static SQL, you code the
actual SQL statement in the source file and compile the statement during explicit SQL
compilation. A static SQL program uses host variables to send and receive values.
A dynamic SQL program, however, uses a character host variable as a placeholder for
an SQL statement, which is usually unknown during explicit compilation. To construct
the dynamic SQL statement in the host variable, the program usually requires some
input from a user at a workstation. After receiving the input, the program constructs,
compiles, and executes the dynamic SQL statement at run time.
Dynamic SQL can be useful for:
•
New interfaces: Suppose that you need to develop an interactive interface similar
to MXCI but designed for specific users. A dynamic SQL program can prompt the
user for input, so the user does not need to know any SQL syntax. If the statement
requires input parameters, the program can also prompt the user for these values.
The program can then construct the SQL statement by concatenating these values
to form syntax elements.
•
Client-server support: Suppose that your program is a server that receives
requests from other programs. A program is created to manipulate data in a
database. The program formulates an SQL statement and sends it to your server.
Your server constructs, compiles, and executes the dynamic SQL statement and
sends the results back to the program.
The statements you use to construct, compile, and execute SQL statements during run
time are referred to as dynamic SQL.
The HP NonStop Connectivity Service (MXCS) uses dynamic SQL. For more
information, see the SQL/MX Connectivity Service Manual.
This section describes:
•
Statements for Dynamic SQL With Arguments on page 9-2
•
Input Parameters and Output Variables on page 9-2
•
Steps for Using Dynamic SQL With Argument Lists on page 9-3
•
Using EXECUTE IMMEDIATE on page 9-7
•
Setting Default Values Dynamically on page 9-8
For information on using dynamic SQL with descriptor areas, see Section 10, Dynamic
SQL With Descriptor Areas.
For information on using dynamic SQL cursors, see Section 11, Dynamic SQL
Cursors.