SQL Programming Manual for Pascal

HP NonStop SQL Programming Manual for Pascal—528614-001
C-1
C
Examples of Dynamic NonStop SQL
Programs
This appendix contains these two complete dynamic SQL programs:
•
Simple program. The first program processes a SELECT statement that is partially
coded into the program; the user supplies the WHERE clause. The SQLDAs and
data buffers are allocated at compile time using the INCLUDE SQLDA directive.
•
Detailed program. The second program allows the user to enter any SQL
statement. The SQLDAs and data buffers are allocated at run time.
Simple Dynamic SQL Program
This program contains 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 constructs the statement by adding a WHERE clause.
This program is an elementary one, because there are no input parameters and there
is only one output variable (the SALARY column is the only column described in the
output SQLDA, and the average salary is the only value output to the user). Because
no parameter names or column headings are required, no names buffers are
necessary.
In this application, you know in advance that you are only reporting data for one
column; thus, you specify INCLUDE SQLDA with one output variable. You assign the
memory location of the value to be output (in this case, the average) to the VAR_PTR
field.
To run this program, you need a DEFINE that points to an EMPLOYEE table similar to
the one in the sample database described in Appendix A, Sample NonStop SQL
Database. A complete set of DEFINEs might look like this:
SET DEFMODE ON
ALTER DEFINE =_DEFAULTS, CATALOG \SYS1.$VOL1.TESTCAT
ALTER DEFINE =_DEFAULTS, VOLUME \SYS1.$VOL1.TESTVOL
SET DEFINE CLASS MAP
ADD DEFINE =EMPLOYEE, FILE PERSNL.EMPLOYEE