HP 3000 Manuals HP 3000 Manuals
Preprocessing of Dynamic Non-Queries
There are two methods for dynamic preprocessing of a non-query:
* Using EXECUTE IMMEDIATE.
* 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:
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 run time 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 upon which to execute
the UPDATE STATISTICS command. The UPDATE STATISTICS command is handled
by using the EXECUTE IMMEDIATE command.
Program COBEX10A performs paragraph CONNECT-DBENVIRONMENT 3 to start a
DBE session. Paragraph CONNECT-DBENVIRONMENT 18 prompts for the name
of a DBEnvironment 19 . A CONNECT command that references the name
entered at 1 is executed 20 .
The program then performs paragraph BEGIN-TRANSACTION 4 , which executes
a BEGIN WORK command 21 . Paragraph EXECUTE-IMMEDIATE is then performed
5 until the DONE-FLAG 2 is set to X.
Paragraph EXECUTE-IMMEDIATE prompts for the name of a table 8 . The
table name is concatenated with the rest of the UPDATE STATISTICS command
10 in CMDLINE 11 . Then the UPDATE statistics command is preprocessed
and executed with the EXECUTE IMMEDIATE command 12 . After paragraph
END-TRANSACTION 13 terminates the transaction with a COMMIT WORK command
22 , the program prompts for another table name 8 . Paragraph
EXECUTE-IMMEDIATE terminates when the user enters a slash in response to
the table name prompt 9 .
The paragraph named TERMINATE-PROGRAM 6 is performed in order to
terminate the DBE session 7 .
When ALLBASE/SQL returns a negative SQLCODE following the execution of
the embedded SQL commands, paragraph SQL-STATUS-CHECK 14 is performed.
This paragraph performs paragraph SQLEXPLAIN 17 to display one or more
messages. If an error is very serious (SQLCODE < -14024), a flag named
ABORT is set 15 , and paragraph TERMINATE-PROGRAM is performed 16 .
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.
_____________________________________________________________________________
| |
| :RUN COBX10AP |
| |
| 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> 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-3. Execution of Program COBEX10A
_________________________________________________________________________
| |
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * |
| * This program illustrates the use of SQL's EXECUTE * |
| * IMMEDIATE Command. * |
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * |
| IDENTIFICATION DIVISION. |
| PROGRAM-ID. COBEX10A. |
| AUTHOR. JIM FRANCIS AND KAREN THOMAS. |
| INSTALLATION. HP. |
| DATE-WRITTEN. 17 MARCH 1987. |
| DATE-COMPILED. 17 MARCH 1987. |
| REMARKS. ILLUSTRATES EXECUTE IMMEDIATE |
| ENVIRONMENT DIVISION. |
| $CONTROL USLINIT |
| CONFIGURATION SECTION. |
| SOURCE-COMPUTER. HP-3000. |
| OBJECT-COMPUTER. HP-3000. |
| SPECIAL-NAMES. CONSOLE IS TERMINAL-INPUT. |
| INPUT-OUTPUT SECTION. |
| FILE-CONTROL. |
| SELECT CRT ASSIGN TO "$STDLIST". |
| DATA DIVISION. |
| FILE SECTION. |
| FD CRT. |
| 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. |
| * * * * * * END OF HOST VARIABLE DECLARATIONS * * * * * * * |
| $PAGE |
| |
| 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'. |
_________________________________________________________________________
Figure 10-4. Program COBEX10A: Using EXECUTE IMMEDIATE
________________________________________________________________________
| |
| 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. |
| |
| $PAGE |
| PROCEDURE DIVISION. |
| BEGIN. |
| |
| DISPLAY "Program to EXECUTE IMMEDIATE the " |
| "UPDATE STATISTICS command - 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". |
| DISPLAY " ". |
| |
| OPEN OUTPUT CRT. |
| |
| PERFORM CONNECT-DBENVIRONMENT. 3 |
| |
| PERFORM BEGIN-TRANSACTION. 4 |
| |
| PERFORM EXECUTE-IMMEDIATE THRU EXECUTE-IMMEDIATE-EXIT 5 |
| UNTIL DONE. |
| |
| PERFORM TERMINATE-PROGRAM. 6 |
| |
| TERMINATE-PROGRAM. |
| |
| EXEC SQL COMMIT WORK RELEASE END-EXEC. 7 |
| |
| STOP RUN. |
________________________________________________________________________
Figure 10-4. Program COBEX10A: Using EXECUTE IMMEDIATE (page 2 of 4)
__________________________________________________________________________
| |
| $PAGE |
| EXECUTE-IMMEDIATE. |
| |
| MOVE SPACES TO CMDLINE. |
| MOVE SPACES TO RESPONSE. |
| MOVE "Enter table name or '/' to STOP> " 8 |
| TO PROMPT2. |
| WRITE PROMPT2 AFTER ADVANCING 1 LINE. |
| ACCEPT RESPONSE. |
| IF RESPONSE-PREFIX = "/" THEN 9 |
| MOVE "X" TO DONE-FLAG |
| GO TO EXECUTE-IMMEDIATE-EXIT |
| ELSE |
| MOVE "UPDATE STATISTICS FOR TABLE " 10 |
| TO CMDLITERAL |
| STRING CMDLITERAL, RESPONSE, SQL-TERMINATOR 11 |
| DELIMITED BY SIZE INTO CMDLINE. |
| |
| DISPLAY CMDLINE. |
| DISPLAY "EXECUTE IMMEDIATE UPDATE STATISTICS command". |
| EXEC SQL EXECUTE IMMEDIATE :CMDLINE 12 |
| END-EXEC. |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| GO TO EXECUTE-IMMEDIATE-EXIT |
| ELSE PERFORM END-TRANSACTION. 13 |
| |
| EXECUTE-IMMEDIATE-EXIT. |
| |
| EXIT. |
| $PAGE |
| SQL-STATUS-CHECK. 14 |
| |
| IF SQLCODE < DEADLOCK THEN 15 |
| MOVE 'X' TO ABORT-FLAG. |
| PERFORM SQLEXPLAIN UNTIL SQLCODE = 0. |
| |
| IF ABORT THEN PERFORM TERMINATE-PROGRAM. 16 |
| |
| SQL-STATUS-CHECK-EXIT. |
| |
| EXIT. |
__________________________________________________________________________
Figure 10-4. Program COBEX10A: Using EXECUTE IMMEDIATE (page 3 of 4)
__________________________________________________________________________
| |
| SQLEXPLAIN. 17 |
| |
| EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. |
| DISPLAY SQLMESSAGE. |
| |
| CONNECT-DBENVIRONMENT. 18 |
| |
| MOVE "Enter name of DBEnvironment> " 19 |
| TO PROMPT1. |
| WRITE PROMPT1 AFTER ADVANCING 1 LINE. |
| ACCEPT DBENAME FREE. |
| |
| DISPLAY "Connect to DBE". |
| EXEC SQL CONNECT TO :DBENAME END-EXEC. 20 |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
| |
| BEGIN-TRANSACTION. |
| |
| DISPLAY "Begin Work". |
| EXEC SQL BEGIN WORK END-EXEC. 21 |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
| |
| END-TRANSACTION. |
| |
| DISPLAY "Commit Work". |
| EXEC SQL COMMIT WORK END-EXEC. 22 |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
__________________________________________________________________________
Figure 10-4. Program COBEX10A: Using EXECUTE IMMEDIATE (page 4 of 4)
Sample Program Using PREPARE and EXECUTE
To prepare a dynamic command for execution later during the current
transaction, you use the PREPARE command to dynamically preprocess the
command. 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 run time 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 CONNECT-DBENVIRONMENT 3 to start a DBE session. The CONNECT
command 21 starts a DBE session in the DBEnvironment named PartsDBE.
The program then performs paragraph BEGIN-TRANSACTION 4 to start a
transaction with the BEGIN WORK command 22 . Once a transaction has
been started, paragraph PREPARE-EXECUTE is performed 5 until the
DONE-FLAG 1 is set to X.
Paragraph PREPARE-EXECUTE first performs paragraph INITIALIZE-VARIABLES
6 to initialize the variables used to handle the building and display of
each SQL command:
* DYNAMICCMD 17 is a host variable that holds the fully assembled
SQL command.
* INPUT-CLAUSES 18 holds the SQL command as it is being built.
* RESPONSE 19 holds the SQL command clauses entered by the program
user.
* INDEXER 20 contains a number identifying the location in
INPUT-CLAUSES to store user input.
The program then performs paragraph ACCEPT-COMMAND 7 until the
COMMAND-DONE-FLAG 2 is set to X. This paragraph prompts for user input
12 , which is put into the INPUT-CLAUSES variable 14 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 15 to allow for a space
between items of user input. The current contents of INPUT-CLAUSES is
displayed each time the user enters information 16 . When the user
enters a semicolon 13 , control returns to paragraph PREPARE-EXECUTE.
After a semicolon is appended to the SQL command in INPUT-CLAUSES 8 ,
the command is moved to host variable DYNAMICCMD 9 for dynamic
preprocessing with the PREPARE command 10 . If the PREPARE command
executes successfully, the EXECUTE command 11 is processed.
________________________________________________________________________
| |
| :RUN COBX10BP |
| 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 |
| Enter an SQL command or clause; enter only a semicolon when done.|
| |
| > UPDATE STATISTICS FOR |
| |
| UPDATE STATISTICS FOR |
| |
| > TABLE PURCHDB.PARTS |
| |
| UPDATE STATISTICS FOR TABLE PURCHDB.PARTS |
| |
| > ; |
| PREPARE COMMAND |
| EXECUTE COMMAND |
| Enter an SQL command or clause; enter only a semicolon when done.|
| |
| > SELECT * FROM |
| |
| SELECT * FROM |
| |
| > PURCHDB.PARTS |
| |
| SELECT * FROM PURCHDB.PARTS |
| |
| > ; |
| PREPARE COMMAND |
| EXECUTE COMMAND |
| Module TEMP.COBEX10B(1) is not a procedure. (DBERR 2752) |
| Enter an SQL command or clause; enter only a semicolon when done.|
| |
| > / |
| |
| END OF PROGRAM |
________________________________________________________________________
Figure 10-5. Execution of Program COBEX10B
______________________________________________________________________
| |
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *|
| * This program illustrates the use of SQL's PREPARE-EXECUTE *|
| * Commands. *|
| * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *|
| IDENTIFICATION DIVISION. |
| PROGRAM-ID. COBEX10B. |
| AUTHOR. JIM FRANCIS AND KAREN THOMAS. |
| INSTALLATION. HP. |
| DATE-WRITTEN. 17 MARCH 1987. |
| DATE-COMPILED. 17 MARCH 1987. |
| REMARKS. ILLUSTRATES PREPARE-EXECUTE. |
| ENVIRONMENT DIVISION. |
| $CONTROL USLINIT |
| CONFIGURATION SECTION. |
| SOURCE-COMPUTER. HP-3000. |
| OBJECT-COMPUTER. HP-3000. |
| SPECIAL-NAMES. CONSOLE IS TERMINAL-INPUT. |
| INPUT-OUTPUT SECTION. |
| FILE-CONTROL. |
| SELECT CRT ASSIGN TO "$STDLIST". |
| DATA DIVISION. |
| FILE SECTION. |
| FD CRT. |
| 01 PROMPT 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). |
| |
| $PAGE'' |
| 01 RESPONSE. |
| 05 RESPONSE-PREFIX PIC X(1) VALUE SPACE. |
| 05 FILLER PIC X(79) VALUE SPACES. |
______________________________________________________________________
Figure 10-6. Program COBEX10B: Using PREPARE and EXECUTE
_________________________________________________________________________
| |
| 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. |
| |
| $PAGE |
| PROCEDURE DIVISION. |
| BEGIN. |
| |
| 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 CRT. |
| |
| PERFORM CONNECT-DBENVIRONMENT. 3 |
| |
| PERFORM BEGIN-TRANSACTION. 4 |
| |
| PERFORM PREPARE-EXECUTE THRU PREPARE-EXECUTE-EXIT 5 |
| UNTIL DONE. |
_________________________________________________________________________
Figure 10-6. Program COBEX10B: Using PREPARE and EXECUTE (page 2 of 5)
__________________________________________________________________________
| |
| PERFORM TERMINATE-PROGRAM. |
| |
| TERMINATE-PROGRAM. |
| |
| EXEC SQL COMMIT WORK RELEASE END-EXEC. |
| |
| STOP RUN. |
| |
| $PAGE |
| PREPARE-EXECUTE. |
| |
| PERFORM INITIALIZE-VARIABLES. 6 |
| |
| DISPLAY "Enter an SQL command or clause; enter only a" |
| " semicolon when done.". |
| |
| MOVE SPACE TO COMMAND-DONE-FLAG. |
| |
| PERFORM ACCEPT-COMMAND THRU ACCEPT-COMMAND-EXIT 7 |
| UNTIL CMD-DONE. |
| |
| IF NOT-DONE |
| STRING ";" DELIMITED BY SIZE INTO INPUT-CLAUSES 8 |
| WITH POINTER INDEXER |
| MOVE INPUT-CLAUSES TO DYNAMICCMD 9 |
| DISPLAY "PREPARE COMMAND" |
| EXEC SQL PREPARE CMD1 FROM :DYNAMICCMD 10 |
| END-EXEC |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| GO TO PREPARE-EXECUTE-EXIT; |
| ELSE |
| DISPLAY "EXECUTE COMMAND" |
| EXEC SQL EXECUTE CMD1 END-EXEC 11 |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK. |
| |
| PREPARE-EXECUTE-EXIT. |
| |
| EXIT. |
| |
| ACCEPT-COMMAND. |
| |
| MOVE "> " TO PROMPT. 12 |
| WRITE PROMPT AFTER ADVANCING 1 LINE. |
| ACCEPT RESPONSE. |
__________________________________________________________________________
Figure 10-6. Program COBEX10B: Using PREPARE and EXECUTE (page 3 of 5)
__________________________________________________________________________
| |
| IF RESPONSE-PREFIX = "/" THEN |
| MOVE "X" TO DONE-FLAG |
| MOVE 'X' TO COMMAND-DONE-FLAG |
| GO TO ACCEPT-COMMAND-EXIT. |
| |
| IF RESPONSE-PREFIX = ";" THEN 13 |
| MOVE "X" TO COMMAND-DONE-FLAG |
| GO TO ACCEPT-COMMAND-EXIT. |
| |
| STRING RESPONSE DELIMITED BY " " 14 |
| INTO INPUT-CLAUSES WITH POINTER INDEXER; |
| ON OVERFLOW |
| DISPLAY "Command too long!" |
| DISPLAY "Try again!" |
| PERFORM INITIALIZE-VARIABLES |
| GO TO ACCEPT-COMMAND. |
| |
| MOVE SPACES TO RESPONSE. |
| ADD 1 TO INDEXER. 15 |
| |
| DISPLAY ' '. |
| PERFORM DISPLAY-COMMAND VARYING I FROM 1 BY 1 16 |
| UNTIL I > 13. |
| |
| ACCEPT-COMMAND-EXIT. |
| |
| EXIT. |
| |
| |
| INITIALIZE-VARIABLES. |
| |
| MOVE SPACES TO DYNAMICCMD. 17 |
| |
| MOVE SPACES TO INPUT-CLAUSES. 18 |
| |
| MOVE SPACES TO RESPONSE. 19 |
| |
| MOVE 1 TO INDEXER. 20 |
| |
| $PAGE |
| DISPLAY-COMMAND. |
| |
| IF PARTIAL-COMMAND(I) IS NOT = ' ' THEN |
| DISPLAY PARTIAL-COMMAND(I). |
__________________________________________________________________________
Figure 10-6. Program COBEX10B: Using PREPARE and EXECUTE (page 4 of 5)
__________________________________________________________________________
| |
| SQL-STATUS-CHECK. |
| |
| IF SQLCODE < DEADLOCK THEN |
| MOVE 'X' TO ABORT-FLAG. |
| |
| PERFORM SQLEXPLAIN UNTIL SQLCODE = 0. |
| |
| IF ABORT THEN PERFORM TERMINATE-PROGRAM. |
| SQL-STATUS-CHECK-EXIT. |
| |
| EXIT. |
| |
| SQLEXPLAIN. |
| |
| EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. |
| DISPLAY SQLMESSAGE. |
| |
| CONNECT-DBENVIRONMENT. |
| |
| DISPLAY "Connect to PartsDBE". |
| EXEC SQL CONNECT TO 'PartsDBE' END-EXEC. 21 |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
| |
| BEGIN-TRANSACTION. |
| |
| DISPLAY "Begin Work". |
| EXEC SQL BEGIN WORK END-EXEC. 22 |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
| |
| END-TRANSACTION. |
| |
| DISPLAY "Commit Work". |
| EXEC SQL COMMIT WORK END-EXEC. |
| |
| IF SQLCODE NOT = OK THEN |
| PERFORM SQL-STATUS-CHECK |
| PERFORM TERMINATE-PROGRAM. |
__________________________________________________________________________
Figure 10-6. Program COBEX10B: Using PREPARE and EXECUTE (page 5 of 5)