There are two methods for dynamic preprocessing of a non-query:
Using PREPARE and EXECUTE.
The first method can be used with any non-query; the second is only for
those non-query commands that use sections at execution time.
Using PREPARE and EXECUTE |
 |
Use the PREPARE command to create and
store a temporary section for the dynamic command, as shown
in the following syntax:
PREPARE CommandName FROM CommandSource
|
Because the PREPARE command operates only on sections, it can be used
to dynamically preprocess only SQL commands executed by using sections.
The DBE session management and transaction management commands
can only be dynamically preprocessed by using EXECUTE
IMMEDIATE.
With PREPARE, ALLBASE/SQL creates a temporary section for the command that
you can execute one or more times
in the same transaction by using the EXECUTE command:
EXEC SQL PREPARE MyNonQuery FROM :DynamicCommand;
PERFORM CMD-BEGIN THRU CMD-END UNTIL CMD-DONE.
CMD-BEGIN.
EXEC SQL EXECUTE MyNonQuery; END-EXEC.
CMD-END.
|
CMD-DONE would be initialized at the start, and set to end the PERFORM command
as desired.
As soon as you process a COMMIT WORK or ROLLBACK WORK command, the
temporary section is deleted.
Defining SQL Commands at Run Time |
|
In some applications, a dynamic command may be
completely definable at programming time. To handle such a
command, you enclose it within single quotation marks in
the PREPARE or EXECUTE IMMEDIATE command:
EXEC SQL PREPARE DynamicCommand
FROM 'UPDATE STATISTICS FOR TABLE SYSTEM.TABLE;'
END-EXEC.
|
Applications such as generalized utilities do not
have available at programming time all the information required to preprocess some SQL commands. Sometimes the entire command is unknown. Sometimes parts of a command
are unknown.
Whether known or unknown at programming time,
the dynamic command must be terminated with a semicolon.
If you specify the command as a literal, the command cannot
exceed 2048 bytes.
To handle a command entirely unknown at programming time, you
accept the command into a host
variable that can hold CHAR or VARCHAR data. In the
following example, an SQL command is accepted into a host variable
named DYNAMICCOMMAND, declared large enough to accommodate
the maximum size dynamic command. User input is accepted into
DYNAMICCLAUSE and concatenated in DYNAMICCOMMAND until
the user enters only a semicolon in response to the input prompt.
|
WORKING-STORAGE SECTION.
.
.
.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 DYNAMICCOMMAND PIC X(2048).
EXEC SQL END DECLARE SECTION END-EXEC.
01 DYNAMICCLAUSE.
05 CLAUSE-PREFIX PIC X(1) VALUE SPACE.
05 FILLER PIC X(79) VALUE SPACES.
01 INDEXER PIC S9(4) COMP.
77 COMMAND-DONE-FLAG PIC X VALUE SPACE.
88 CMD-NOT-DONE VALUE SPACE.
88 CMD-DONE VALUE 'X'.
.
.
.
PROCEDURE DIVISION.
.
.
.
PERFORM ACCEPT-COMMAND THRU ACCEPT-COMMAND-EXIT
UNTIL CMD-DONE.
STRING ";" DELIMITED BY SIZE INTO DYNAMICCOMMAND
WITH POINTER INDEXER.
EXEC SQL EXECUTE IMMEDIATE :DYNAMICCOMMAND END-EXEC.
.
.
.
ACCEPT-COMMAND.
MOVE "> " TO PROMPT.
WRITE PROMPT AFTER ADVANCING 1 LINE.
ACCEPT DYNAMICCLAUSE.
IF CLAUSE-PREFIX = ";" THEN
MOVE "X" TO COMMAND-DONE-FLAG
GO TO ACCEPT-COMMAND-EXIT.
STRING DYNAMICCLAUSE DELIMITED BY " "
INTO DYNAMICCOMMAND WITH POINTER INDEXER.
MOVE SPACES TO DYNAMICCLAUSE.
ADD 1 TO INDEXER.
ACCEPT-COMMAND-EXIT.
EXIT.
|
|
To handle a command partially known at programming time, you prompt
the user for information to complete the command. Then you
concatenate this information with the predefined part of
the command:
DATA DIVISION.
FILE SECTION.
FD CRT.
01 PROMPT PIC X(35).
.
.
.
WORKING-STORAGE SECTION.
.
.
.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 CMDLINE PIC X(80).
EXEC SQL END DECLARE SECTION END-EXEC.
01 CMDLITERAL PIC X(28).
01 TABLENAME PIC X(42).
01 SQL-TERMINATOR PIC X(2) VALUE "; ".
.
.
.
PROCEDURE DIVISION.
.
.
.
MOVE "Enter table name> " TO PROMPT.
WRITE PROMPT AFTER ADVANCING 1 LINE.
ACCEPT TABLENAME.
.
.
.
MOVE "UPDATE STATISTICS FOR TABLE "
TO CMDLITERAL.
STRING CMDLITERAL, TABLENAME, SQL-TERMINATOR
DELIMITED BY SIZE INTO CMDLINE.
EXEC SQL EXECUTE IMMEDIATE :CMDLINE END-EXEC.
|
Sample Program Using EXECUTE IMMEDIATE |
|
To preprocess and execute a dynamic command in only one step, you
use the EXECUTE IMMEDIATE command:
EXEC SQL EXECUTE IMMEDIATE :DynamicCommand END-EXEC.
|
|
Program COBEX10A, whose runtime dialog is shown in Figure
10-3 and whose source code is given in Figure 10-4, can be used to execute
the UPDATE STATISTICS command in any DBEnvironment. This
program prompts for both the DBEnvironment name and the name of tables
for which to execute the UPDATE STATISTICS command. The UPDATE
STATISTICS command is handled by using the EXECUTE IMMEDIATE command.
Program COBEX10A performs paragraph A200-CONNECT-DBENVIRONMENT 3 to
start a DBE session. Paragraph A200-CONNECT-DBENVIRONMENT 7
prompts for a DBEnvironment name 8 .
A CONNECT command that references the name entered 1
is executed 9 .
The program then performs paragraph A300-BEGIN-TRANSACTION 4 ,
which executes a BEGIN WORK command 10 . Paragraph
B100-EXECUTE-IMMEDIATE is then performed 5 until the
DONE-FLAG 2 is set to X.
Paragraph B100-EXECUTE-IMMEDIATE prompts for the name of a
table 13 . The table name is concatenated with the rest of the
UPDATE STATISTICS command in CMDLINE 15 . Then the UPDATE
statistics command is preprocessed and executed with the
EXECUTE IMMEDIATE command 16 . After paragraph A400-END-TRANSACTION
17 terminates the transaction with
a COMMIT WORK command 11 , the program prompts for another
table name 13 . Paragraph B100-EXECUTE-IMMEDIATE terminates when the
user enters a slash in response to the table name prompt 14 .
The paragraph named A500-TERMINATE-PROGRAM is performed 6
in order to terminate the DBE session 12 .
When ALLBASE/SQL returns a negative SQLCODE
following the execution of the embedded SQL commands, paragraph
S100-SQL-STATUS-CHECK 18 is performed. This paragraph
performs paragraph S200-SQLEXPLAIN 21 to display one or more
messages. If an error is very serious (SQLCODE < -14024), a flag named
ABORT is set 19 , and paragraph A500-TERMINATE-PROGRAM
is performed 20 .
When an error occurs during the execution of the CONNECT, BEGIN WORK, or
COMMIT WORK commands, the program terminates after paragraph
SQL-STATUS-CHECK has been performed. Otherwise, the program continues
after warning or error messages are displayed.
Using PREPARE and EXECUTE |
|
You use the PREPARE command to prepare a dynamic command for
execution later during the current transaction.
ALLBASE/SQL creates a temporary section for the command that
you can execute one or more times in the same transaction by using
the EXECUTE command:
EXEC SQL PREPARE MyCommand FROM :DynamicCommand END-EXEC.
.
.
EXEC SQL EXECUTE :DynamicCommand END-EXEC.
|
|
As soon as you process a COMMIT WORK or ROLLBACK WORK command,
the temporary section is deleted.
Figure 10-5 illustrates the runtime dialog for a program that
uses the PREPARE and EXECUTE commands, program COBEX10B.
The program starts a DBE session in the DBEnvironment named
PartsDBE, then prompts for entry of an SQL command or
clause. As the user enters information, the program displays the
SQL command as it grows. When the program user enters only
a semicolon in response to the prompt, the command is
dynamically preprocessed and executed. Note what happens when
a SELECT command is entered.
As illustrated in Figure 10-6,
Program COBEX10B performs a paragraph
named A200-CONNECT-DBENVIRONMENT 3 to start a DBE session. The
CONNECT command 6 starts a DBE session in the DBEnvironment
named PartsDBE.
The program then performs paragraph A300-BEGIN-TRANSACTION 4
to start a transaction with the BEGIN WORK command 7 .
Once a transaction is started, B100-PREPARE-EXECUTE
is performed 5 until the DONE-FLAG 1 is set to X.
Paragraph B100-PREPARE-EXECUTE first performs paragraph
C100-INITIALIZE-VARIABLES 8 to initialize the variables
used to handle the building and display of each SQL command:
DYNAMICCMD 14 is a host variable that holds the
fully-assembled SQL command.
INPUT-CLAUSES 15 holds the SQL command as it is being
built.
RESPONSE 16 holds the SQL command clauses entered by
the program user.
INDEXER 17 contains a number identifying the location
in INPUT-CLAUSES to store user input.
The program then performs paragraph C200-ACCEPT-COMMAND 9 until the
COMMAND-DONE-FLAG 2 is set to X. This paragraph prompts for
user input 18 , which is put into the INPUT-CLAUSES variable
20 by using the STRING statement. The STRING statement uses the
variable INDEXER to determine where in INPUT-CLAUSES to start
writing information entered by the user. INDEXER is incremented
by one 21 to allow for a space between items of user input.
The current contents of INPUT-CLAUSES is displayed each time the user
enters information 22 .
When the user enters a semicolon 19 , control returns to paragraph
B100-PREPARE-EXECUTE.
After a semicolon is appended to the SQL command in INPUT-CLAUSES
10 , the command is moved to host variable DYNAMICCMD 11
for dynamic preprocessing with the PREPARE command 12 . If
the PREPARE command executes successfully, the EXECUTE command 13
is processed.
Printable version
