SQL Programming Manual for TAL

HP NonStop SQL Programming Manual for TAL—527887-001
C-1
C
Examples of Dynamic NonStop SQL
Programs
This appendix describes these sample dynamic SQL programs:
•
Simple program (TALDYNEZ)
TALDYNEZ processes a SELECT statement that is partially coded into the
program; the user supplies the WHERE clause. The SQLDA structures and data
buffers are allocated at compile time using the INCLUDE SQLDA directive.
•
Detailed program (TALDYN)
TALDYN allows the user to enter any SQL statement from a terminal. The SQLDA
structures and data buffers are allocated at run time.
These programs use the NonStop SQL sample database, which is described in
Appendix A, Sample NonStop SQL Database
.
Dynamic SQL Program
The TALDYNEZ program uses a SELECT statement to find the average salary for a
selection of rows in the employee table. The program prompts the user for the
selection criteria and then constructs the statement by adding a WHERE clause.
TALDYNEZ has no input parameters and only one output variable. The expression
AVG(SALARY) is the only element described in the output SQLDA structure, and the
average salary is the only value output. Because no parameter names or column
headings are required, names buffers are not necessary. TALDYNEZ also assumes
that the query will never return a null value.
TALDYNEZ allocates memory during compilation using the INCLUDE SQLDA directive
and specifying one output variable. You can specify one output variable because you
know you are only reporting data for one column. You must still assign the memory
location of the value to be output (in this case, the average) to the VAR^PTR field.
TALDYNEZ uses the TACL DEFINE name =EMPLOYEE for the EMPLOYEE table in
the sample database. The commands to add this DEFINE name are:
SET DEFMODE ON
SET DEFINE CLASS MAP
ADD DEFINE =EMPLOYEE, FILE PERSNL.EMPLOYEE
Figure C-1 shows the output for the TALDYNEZ program with the data entered by the
user shown in bold type. The object file name is TALDEZO.