SQL Programming Manual for Pascal

Using Dynamic SQL
HP NonStop SQL Programming Manual for Pascal—528614-001
7-2
Developing a Dynamic SQL Application
•
You want to switch between several copies of identical databases. For this
application, you use a dynamic SQL program with run-time DEFINEs.
•
You want to restrict access to the data in a table. For example, the program might
code an UPDATE statement for certain columns in a table, but allow the user to
enter the selection criteria (WHERE clause) at run time.
•
Your program must communicate with other software that communicates with the
user. For example, an application exists on a personal computer, and the user
wants to manipulate data in a NonStop SQL database on a host system. This kind
of application cannot use SQLCI. The application allows the user to formulate an
SQL statement on the personal computer and send it to a server process on the
Tandem system over MULTILAN or some other communications protocol.
Developing a Dynamic SQL Application
A dynamic SQL application can accept statements directly from the user or through a
screen interface like Pathway, or the program can build the statements with little or no
user input. The application might process an entire SQL statement, or it might process
only part of a statement (such as the WHERE clause) and explicitly code the rest of the
statement in the program.
A program that uses dynamic SQL to process input directly from a user can be similar
to the NonStop SQL Conversational Interface (SQLCI), requiring the user to know SQL
syntax to formulate a complete SQL statement. The statement can contain input
parameters; if it does, the program can prompt the user for the parameter values.
A program constructed for direct user input can also be written so that the user need
not have to know SQL syntax. In this case, the program prompts the user for the
necessary values (or displays a screen on which the user enters the values) and then
constructs the SQL statement by concatenating values the user enters to whatever
syntax elements are already known. For example, in a program to handle any CREATE
TABLE statement, the code concatenates the string CREATE TABLE and punctuation
(commas, colons, and so forth) to the table name, column names, data types, and
options entered by the user and stored in local variables. The user sees only a series
of prompts, such as ENTER THE TABLE NAME, ENTER THE FIRST COLUMN
NAME, and so forth.
Writing a Dynamic SQL Pathway Server
If you are writing a Pascal server that interfaces with Pathway and uses dynamic SQL
statements, follow these guidelines:
1. Use the SOURCE directive to include procedure declarations in the PEXTDECS
for TYPES, GUARDIAN_OPEN, READUPDATE, and REPLY procedures:
?SOURCE $SYSTEM.SYSTEM.PEXTDECS (TYPES, GUARDIAN_OPEN,
READUPDATE, REPLY);