SQL Programming Manual for TAL

Dynamic NonStop SQL Operations
HP NonStop SQL Programming Manual for TAL—527887-001
7-2
Determining Uses for Dynamic SQL Operations
Determining Uses for Dynamic SQL
Operations
Programs that use dynamic SQL operations can be useful for a number of applications.
For example:
•
You can develop an interactive interface that is similar to SQLCI, but is designed
for an inexperienced user.
•
You want to switch between several copies of identical databases. For this
application, you use a dynamic SQL program with run-time TACL 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. Your
program 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 HP
NonStop 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.
You can also write a program for direct user input so that the user does not have to
know SQL syntax. In this case, the program prompts the user for the necessary values
(or displays a screen for the user to enter the values) and then constructs the SQL
statement by concatenating these values to known syntax elements.
For example, a program can handle any CREATE TABLE statement by concatenating
the string “CREATE TABLE” and punctuation (for example, commas, colons) to the
table name, column names, data types, and options entered by a user and stored in
local variables. The program user sees only a series of prompts, such as ENTER THE
TABLE NAME, ENTER THE FIRST COLUMN NAME, and so forth.