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.
Figure 10-3 Runtime Dialog of Program COBEX10A
Program to EXECUTE IMMEDIATE the UPDATE STATISTICS command - COBEX10A
Event List:
Prompt for DBE name
Connect to DBE
Begin Work
Prompt for table name
EXECUTE IMMEDIATE UPDATE STATISTICS command
Commit Work
Repeat the above three steps until user enters '/'
Release Database Environment
Enter name of DBEnvironment> ../sampledb/PartsDBE
Connect to DBE
Begin Work
Enter table name or '/' to STOP> PURCHDB.VENDORS
UPDATE STATISTICS FOR TABLE PURCHDB.VENDORS ;
EXECUTE IMMEDIATE UPDATE STATISTICS command
Commit Work
Enter table name or '/' to STOP> SYSTEM.TABLE
UPDATE STATISTICS FOR TABLE SYSTEM.TABLE ;
EXECUTE IMMEDIATE UPDATE STATISTICS command
Commit Work
Enter table name or '/' to STOP> PURCHDB.VENDORSTATISTICS
UPDATE STATISTICS FOR TABLE PURCHDB.VENDORSTATISTICS ;
EXECUTE IMMEDIATE UPDATE STATISTICS command
Command UPDATE STATISTICS is not for views (PURCHDB.VENDORSTATISTICS).
(DBERR 2724)
Enter table name or '/' to STOP> /
END OF PROGRAM
|
Figure 10-4 Program COBEX10A: Using EXECUTE IMMEDIATE
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This program illustrates the use of SQL's EXECUTE *
* IMMEDIATE Command. *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
IDENTIFICATION DIVISION.
PROGRAM-ID. COBEX10A.
AUTHOR. JIM FRANCIS, KAREN THOMAS, JOANN GRAY.
INSTALLATION. HP.
DATE-WRITTEN. 17 OCT 1987.
DATE-COMPILED. 17 OCT 1987.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. HP-9000.
OBJECT-COMPUTER. HP-9000.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT TERM ASSIGN TO ":CO:".
DATA DIVISION.
FILE SECTION.
FD TERM.
01 PROMPT1 PIC X(32).
01 PROMPT2 PIC X(35).
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
* * * * * * BEGIN HOST VARIABLE DECLARATIONS * * * * * * *
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 SQLMESSAGE PIC X(132).
01 CMDLINE PIC X(80).
01 DBENAME PIC X(32). 1
EXEC SQL END DECLARE SECTION END-EXEC.
01 CMDLITERAL PIC X(28).
01 RESPONSE.
05 RESPONSE-PREFIX PIC X(1) VALUE SPACE.
05 RESPONSE-TEXT PIC X(41) VALUE SPACES.
01 SQL-TERMINATOR PIC X(2) VALUE '; '.
77 DONE-FLAG PIC X VALUE SPACE. 2
88 NOT-DONE VALUE SPACE.
88 DONE VALUE 'X'.
77 ABORT-FLAG PIC X VALUE SPACE.
88 NOT-STOP VALUE SPACE.
88 ABORT VALUE 'X'.
01 OK PIC S9(9) COMP VALUE 0.
01 DEADLOCK PIC S9(9) COMP VALUE -14024.
PROCEDURE DIVISION.
A100-MAIN.
ACCEPT RESPONSE.
DISPLAY "Program to EXECUTE IMMEDIATE the UPDATE STATISTICS c
- "ommand - COBEX10A".
DISPLAY " ".
DISPLAY "Event List:".
DISPLAY " Prompt for DBE name".
DISPLAY " Connect to DBE".
DISPLAY " Begin Work".
DISPLAY " Prompt for table name".
DISPLAY " EXECUTE IMMEDIATE UPDATE STATISTICS command".
DISPLAY " Commit Work".
DISPLAY " Repeat the above three steps until user enters '/
- "'".
DISPLAY " Release Database Environment".
OPEN OUTPUT TERM.
PERFORM A200-CONNECT-DBENVIRONMENT THRU A200-EXIT. 3
PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 4
PERFORM B100-EXECUTE-IMMEDIATE THRU B100-EXIT 5
UNTIL DONE.
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. 6
A100-EXIT.
EXIT.
A200-CONNECT-DBENVIRONMENT. 7
MOVE 'Enter name of DBEnvironment> ' TO PROMPT1. 8
DISPLAY " ".
WRITE PROMPT1.
ACCEPT DBENAME.
DISPLAY "Connect to DBE".
EXEC SQL
CONNECT TO :DBENAME 9
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT.
A200-EXIT.
EXIT.
A300-BEGIN-TRANSACTION.
DISPLAY "Begin Work".
EXEC SQL
BEGIN WORK 10
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT.
A300-EXIT.
EXIT.
A400-END-TRANSACTION.
DISPLAY "Commit Work".
EXEC SQL
COMMIT WORK 11
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT.
A400-EXIT.
EXIT.
A500-TERMINATE-PROGRAM.
EXEC SQL
COMMIT WORK RELEASE 12
END-EXEC.
STOP RUN.
A500-EXIT.
EXIT.
B100-EXECUTE-IMMEDIATE.
MOVE SPACES TO CMDLINE.
MOVE SPACES TO RESPONSE.
DISPLAY RESPONSE.
DISPLAY " ".
MOVE "Enter table name or '/' to STOP> " TO PROMPT1. 13
WRITE PROMPT1.
ACCEPT RESPONSE.
IF RESPONSE-PREFIX = "/" 14
MOVE "X" TO DONE-FLAG
GO TO B100-EXIT
ELSE
MOVE "UPDATE STATISTICS FOR TABLE " TO CMDLITERAL 15
STRING CMDLITERAL, RESPONSE, SQL-TERMINATOR
DELIMITED BY SIZE INTO CMDLINE.
DISPLAY CMDLINE.
DISPLAY "EXECUTE IMMEDIATE UPDATE STATISTICS command".
EXEC SQL
EXECUTE IMMEDIATE :CMDLINE 16
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
GO TO B100-EXIT
ELSE
PERFORM A400-END-TRANSACTION THRU A400-EXIT. 17
B100-EXIT.
EXIT.
S100-SQL-STATUS-CHECK. 18
IF SQLCODE < DEADLOCK
MOVE 'X' TO ABORT-FLAG. 19
PERFORM S200-SQLEXPLAIN THRU S200-EXIT
UNTIL SQLCODE = 0.
IF ABORT
PERFORM A500-TERMINATE-PROGRAM THRU A500-EXIT. 20
S100-EXIT.
EXIT.
S200-SQLEXPLAIN. 21
EXEC SQL
SQLEXPLAIN :SQLMESSAGE
END-EXEC.
DISPLAY SQLMESSAGE.
S200-EXIT.
EXIT.
|
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.
Figure 10-5 Runtime Dialog of Program COBEX10B
Program to PREPARE & EXECUTE SQL commands - COBEX10B
Event List:
Connect to PartsDBE
Begin Work
Prompt for SQL command
PREPARE SQL Command
EXECUTE SQL Command
Repeat the above three steps until user enters '/'
Commit Work
Release PartsDBE
Connect to PartsDBE
Begin Work
You may enter a non-query SQL command or a '/' to STOP the program.
The command can be continued on the next line. The command must be
terminated with a semicolon on a separate line.
> UPDATE STATISTICS FOR
UPDATE STATISTICS FOR
> TABLE PURCHDB.PARTS
UPDATE STATISTICS FOR TABLE PURCHDB.PARTS
> ;
PREPARE COMMAND
EXECUTE COMMAND
You may enter a non-query SQL command or a '/' to STOP the program.
The command can be continued on the next line. The command must be
terminated with a semicolon on a separate line.
> SELECT * FROM
SELECT * FROM
> PURCHDB.PARTS
SELECT * FROM PURCHDB.PARTS
> ;
PREPARE COMMAND
EXECUTE COMMAND
Module TEMP.COBEX10B(1) is not a procedure. (DBERR 2752)
You may enter a non-query SQL command or a '/' to STOP the program.
The command can be continued on the next line. The command must be
terminated with a semicolon on a separate line.
> /
|
Figure 10-6 Program COBEX10B: Using PREPARE and EXECUTE
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This program illustrates the use of SQL's PREPARE-EXECUTE *
* Commands. *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
IDENTIFICATION DIVISION.
PROGRAM-ID. COBEX10B.
AUTHOR. JIM FRANCIS, KAREN THOMAS, JOANN GRAY.
INSTALLATION. HP.
DATE-WRITTEN. 17 OCT 1987.
DATE-COMPILED. 17 OCT 1987.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. HP-9000.
OBJECT-COMPUTER. HP-9000.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT TERM ASSIGN TO ":CO:".
DATA DIVISION.
FILE SECTION.
FD TERM.
01 PROMPT-USER PIC X(3).
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
* * * * * * BEGIN HOST VARIABLE DECLARATIONS * * * * * * *
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 SQLMESSAGE PIC X(132).
01 DYNAMICCMD PIC X(1014).
EXEC SQL END DECLARE SECTION END-EXEC.
* * * * * * END OF HOST VARIABLE DECLARATIONS * * * * * * *
01 I PIC S9(4) COMP.
01 INPUT-CLAUSES.
05 COMMAND-LINE-TABLE OCCURS 13 TIMES.
10 PARTIAL-COMMAND PIC X(78).
01 RESPONSE.
05 RESPONSE-PREFIX PIC X(1) VALUE SPACE.
05 RESPONSE-SUFFIX PIC X(79) VALUE SPACES.
01 INDEXER PIC S9(4) COMP.
77 DONE-FLAG PIC X VALUE SPACE. 1
88 NOT-DONE VALUE SPACE.
88 DONE VALUE 'X'.
77 COMMAND-DONE-FLAG PIC X VALUE SPACE. 2
88 CMD-NOT-DONE VALUE SPACE.
88 CMD-DONE VALUE 'X'.
77 ABORT-FLAG PIC X VALUE SPACE.
88 NOT-STOP VALUE SPACE.
88 ABORT VALUE 'X'.
01 OK PIC S9(9) COMP VALUE 0.
01 DEADLOCK PIC S9(9) COMP VALUE -14024.
PROCEDURE DIVISION.
A100-MAIN.
ACCEPT RESPONSE.
DISPLAY "Program to PREPARE & EXECUTE SQL commands -"
"COBEX10B".
DISPLAY " ".
DISPLAY "Event List:".
DISPLAY " Connect to PartsDBE".
DISPLAY " Begin Work".
DISPLAY " Prompt for SQL command".
DISPLAY " PREPARE SQL Command".
DISPLAY " EXECUTE SQL Command".
DISPLAY " Repeat the above three steps until user enters"
" '/' ".
DISPLAY " Commit Work".
DISPLAY " Release PartsDBE".
DISPLAY " ".
OPEN OUTPUT TERM.
PERFORM A200-CONNECT-DBENVIRONMENT THRU A200-EXIT. 3
PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT. 4
PERFORM B100-PREPARE-EXECUTE THRU B100-EXIT 5
UNTIL DONE.
PERFORM A400-TERMINATE-PROGRAM THRU A400-EXIT.
A100-EXIT.
EXIT.
A200-CONNECT-DBENVIRONMENT.
DISPLAY "Connect to ../sampledb/PartsDBE".
EXEC SQL
CONNECT TO '../sampledb/PartsDBE' 6
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A400-TERMINATE-PROGRAM THRU A400-EXIT.
A200-EXIT.
EXIT.
A300-BEGIN-TRANSACTION.
DISPLAY "Begin Work".
EXEC SQL
BEGIN WORK 7
END-EXEC.
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
PERFORM A400-TERMINATE-PROGRAM THRU A400-EXIT.
A300-EXIT.
EXIT.
A400-TERMINATE-PROGRAM.
EXEC SQL
COMMIT WORK RELEASE
END-EXEC.
STOP RUN.
A400-EXIT.
EXIT.
B100-PREPARE-EXECUTE.
PERFORM C100-INITIALIZE-VARIABLES THRU C100-EXIT. 8
DISPLAY " ".
DISPLAY "You may enter a non-query SQL command or a '/' to "
"STOP the program.".
DISPLAY "The command can be continued on the next line. "
"The command must be".
DISPLAY "terminated with a semicolon on a separate "
"line.".
MOVE SPACE TO COMMAND-DONE-FLAG.
PERFORM C200-ACCEPT-COMMAND THRU C200-EXIT 9
UNTIL CMD-DONE.
IF NOT-DONE
STRING ";" DELIMITED BY SIZE INTO INPUT-CLAUSES 10
WITH POINTER INDEXER
MOVE INPUT-CLAUSES TO DYNAMICCMD 11
DISPLAY "PREPARE COMMAND"
EXEC SQL
PREPARE CMD1 FROM :DYNAMICCMD 12
END-EXEC
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT
GO TO B100-EXIT
ELSE
DISPLAY "EXECUTE COMMAND"
EXEC SQL 13
EXECUTE CMD1
END-EXEC
IF SQLCODE NOT = OK
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
B100-EXIT.
EXIT.
C100-INITIALIZE-VARIABLES.
MOVE SPACES TO DYNAMICCMD. 14
MOVE SPACES TO INPUT-CLAUSES. 15
MOVE SPACES TO RESPONSE. 16
MOVE 1 TO INDEXER. 17
C100-EXIT.
EXIT.
C200-ACCEPT-COMMAND.
MOVE "> " TO PROMPT-USER.
DISPLAY " ".
WRITE PROMPT-USER.
ACCEPT RESPONSE. 18
IF RESPONSE-PREFIX = "/"
MOVE "X" TO DONE-FLAG
MOVE 'X' TO COMMAND-DONE-FLAG
GO TO C200-EXIT.
IF RESPONSE-PREFIX = ";" 19
MOVE "X" TO COMMAND-DONE-FLAG
GO TO C200-EXIT.
STRING RESPONSE DELIMITED BY " " 20
INTO INPUT-CLAUSES WITH POINTER INDEXER;
ON OVERFLOW
DISPLAY "Command too long!"
DISPLAY "Try again!"
PERFORM C100-INITIALIZE-VARIABLES THRU C100-EXIT
GO TO C200-ACCEPT-COMMAND.
MOVE SPACES TO RESPONSE.
ADD 1 TO INDEXER. 21
DISPLAY ' '.
PERFORM D100-DISPLAY-COMMAND VARYING I FROM 1 BY 1 22
UNTIL I > 13.
C200-EXIT.
EXIT.
D100-DISPLAY-COMMAND.
IF PARTIAL-COMMAND(I) IS NOT = ' '
DISPLAY PARTIAL-COMMAND(I).
D100-EXIT.
EXIT.
S100-SQL-STATUS-CHECK.
IF SQLCODE < DEADLOCK
MOVE 'X' TO ABORT-FLAG.
PERFORM S200-SQLEXPLAIN UNTIL SQLCODE = 0.
IF ABORT
PERFORM A400-TERMINATE-PROGRAM THRU A400-EXIT.
S100-EXIT.
EXIT.
S200-SQLEXPLAIN.
EXEC SQL
SQLEXPLAIN :SQLMESSAGE
END-EXEC.
DISPLAY SQLMESSAGE.
S200-EXIT.
EXIT.
|
