HP 3000 Manuals HP 3000 Manuals
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
Program forex9a, whose runtime dialog is shown in Figure 9-4 and whose
source code is given in Figure 9-5, can be used to execute the UPDATE
STATISTICS command on any table 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.
The main program 1 first performs function ConnectDBE 2 to start a DBE
session. ConnectDBE prompts for the name of a DBEnvironment 2A. A READ
command places the DBEnvironment name in the host variable DBEnvironment,
and the CONNECT command is then executed. The program performs implicit
status checking, which results in calls to subroutine SQLStatusCheck 8 if
an error occurs. Note that it is necessary either to include the
appropriate label in each subsequent subprogram unit that follows the
WHENEVER Condition GOTO commands or to turn implicit status checking off.
The program then performs subroutine BeginWork 3 to begin a transaction.
BeginWork executes a BEGIN WORK command. Function Update 7 is then
performed to execute the UPDATE STATISTICS command. Update declares the
host variables used to hold information about the dynamic command. The
static part of the UPDATE STATISTICS command is placed into the variable
Static, and then the user is prompted for the name of the table to be
updated 7A. The TableName is then concatenated with the rest of the
UPDATE STATISTICS command in Static and placed into the variable Command.
The full UPDATE STATISTICS command is then preprocessed and executed with
the EXECUTE IMMEDIATE command 7B. At the end of the function, implicit
status checking is turned off.
If function Update evaluates to TRUE, it prompts the user for another
table name. Function Update terminates when Update is set to FALSE by
the user's entering a slash in response to the prompt for a table name.
When Update evaluates to FALSE, subroutine CommitWork 4 is performed.
CommitWork executes a COMMIT WORK command, then subroutine ReleaseDBE is
performed 5. ReleaseDBE executes a RELEASE command to terminate the DBE
session. After ReleaseDBE has executed, the program terminates.
When ALLBASE/SQL returns a negative value or a value of 100 in SQLCode
following the execution of the embedded SQL commands, subroutine
SQLStatusCheck 8 is performed. This subroutine writes out messages based
on the values of SQLCode and SQLWARN, then calls subroutine SQLExplain 6
to display one or more messages. Subroutine SQLExplain executes the
SQLEXPLAIN command and prints out the error message. If an error is very
serious (SQLCode < -14024), a flag named Abort is set, and subroutines
CommitWork 4 and ReleaseDBE 5 are performed before the program is
terminated.
__________________________________________________________________________
| |
| Program to illustrate the EXECUTE IMMEDIATE command -- forex9a |
| Event List: |
| Prompt for the DBEnvironment Name |
| CONNECT TO the DBEnvironment |
| BEGIN WORK |
| Prompt for the table name |
| EXECUTE IMMEDIATE UPDATE STATISTICS command |
| COMMIT WORK |
| Repeat the above 3 steps until the user enters a / |
| RELEASE from DBEnvironment |
| Repeat the above 8 steps until the user enters a / |
| Terminate the Program |
| |
| Enter DBEnvironment to CONNECT TO or a / to STOP > PartsDBE |
| CONNECT TO DBEnvironment |
| Successful CONNECT |
| BEGIN WORK |
| |
| Enter Table Name or a / to Stop > PurchDB.Vendors |
| Table to Update - PurchDB.Vendors |
| Command - UPDATE STATISTICS FOR TABLE PurchDB.Vendors ;|
| SQL command executed successfully. |
| |
| Enter Table Name or a / to Stop > System.Table |
| Table to Update - System.Table |
| Command - UPDATE STATISTICS FOR TABLE System.Table ;|
| SQL command executed successfully. |
| |
| Enter Table Name or a / to Stop > PurchDB.VendorStatistics |
| Table to Update - PurchDB.VendorStatistics |
| Command - UPDATE STATISTICS FOR TABLE PurchDB.VendorStatistics ;|
| HPSQL error! |
| Call SQLExplain |
| Command UPDATE STATISTICS is not allowed |
| for views (PURCHDB.VENDORSTATISTICS) |
| SQL command not successfully executed. |
| |
| Enter Table Name or a / to Stop > / |
| No more tables to update. |
| COMMIT WORK |
| RELEASE DBEnvironment |
| |
| Enter DBEnvironment to CONNECT TO or a / to STOP > / |
| Terminating the Program! |
| |
| |
| |
__________________________________________________________________________
Figure 9-4. Runtime Dialog for Program forex9a
__________________________________________________________________________________
| |
| PROGRAM forex9a |
| |
| C * * * * * * * * * * * * * * * * * * * * * * * * * * * * |
| C * This program illustrates the use of SQL dynamic * |
| C * non-query commands executed from a FORTRAN program. * |
| C * This program demonstrates the use of the EXECUTE * |
| C * IMMEDIATE command. * |
| C * * * * * * * * * * * * * * * * * * * * * * * * * * * * |
| |
| IMPLICIT NOTE |
| LOGICAL*2 Update, Test, ConnectDBE |
| |
| C (**** Begin SQL Communication Area ****) |
| EXEC SQL INCLUDE SQLCA |
| |
| C (**** begin Host Variable Declarations ****) |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| |
| C (**** Beginning of Main Program ****) 1 |
| |
| WRITE (*,*) CHAR(27), 'U' |
| WRITE (*,*) 'Program to illustrate the EXECUTE |
| 1IMMEDIATE command -- forex9a' |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'Event List:' |
| WRITE (*,*) ' Prompt for the DBEnvironment Name:' |
| WRITE (*,*) ' CONNECT TO the DBEnvironment' |
| WRITE (*,*) ' BEGIN WORK' |
| WRITE (*,*) ' Prompt for the table name:' |
| WRITE (*,*) ' EXECUTE IMMEDIATE UPDATE STATISTICS command' |
| WRITE (*,*) ' COMMIT WORK' |
| WRITE (*,*) ' Repeat the above 3 steps until the |
| 1user enters a /' |
| WRITE (*,*) ' RELEASE from DBEnvironment' |
| WRITE (*,*) ' Repeat the above 8 steps until the |
| 1user enters a /' |
| WRITE (*,*) ' Terminate the Program.' |
| |
| DO WHILE (ConnectDBE()) |
| CALL BeginWork |
| Test = .TRUE. |
| DO WHILE (Test) |
| Test = Update() |
| END DO |
| CALL CommitWork |
| CALL ReleaseDBE |
| |
| |
__________________________________________________________________________________
Figure 9-5. Program forex9a: Sample Program Using EXECUTE IMMEDIATE
____________________________________________________________________________
| |
| END DO |
| WRITE (*,*) 'Connect was Unsuccessful!' |
| C |
| END |
| C (* Beginning of the Subroutines *) |
| |
| LOGICAL*2 FUNCTION ConnectDBE() 2 |
| |
| C (**** Subroutine to connect to user |
| entered DBEnvironment ****) |
| |
| C (* Begin Communication Area *) |
| EXEC SQL INCLUDE SQLCA |
| |
| C (**** Begin Host Variable Declarations ****) |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*80 DBEnvironment |
| CHARACTER*80 SQLMessage |
| EXEC SQL END DECLARE SECTION |
| |
| EXEC SQL WHENEVER SQLERROR GOTO 1000 |
| EXEC SQL WHENEVER SQLWARNING GOTO 1000 |
| EXEC SQL WHENEVER NOT FOUND GOTO 1000 |
| |
| ConnectDBE = .FALSE. |
| DBEnvironment = '/' |
| |
| WRITE (*,100) 2A |
| |
| 100 FORMAT (/$,'Enter DBEnvironment to CONNECT TO or a / |
| 1to STOP > ') |
| READ (*,110) DBEnvironment |
| 110 FORMAT(A80) |
| |
| IF (DBEnvironment .EQ. '/') THEN |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'Terminating the Program!' |
| STOP |
| ELSE |
| WRITE (*,*) ' ' |
| WRITE (*,*) 'CONNECT TO DBEnvironment' |
| EXEC SQL CONNECT TO :DBEnvironment |
| ENDIF |
| IF (SQLCode .NE. 0) THEN |
| GOTO 1000 |
| ENDIF |
| ConnectDBE = .TRUE. |
| WRITE (*,*) 'Successful CONNECT' |
| GOTO 2000 |
| |
____________________________________________________________________________
Figure 9-5. Program forex9a: Sample Program Using EXECUTE IMMEDIATE (page 2 of 6)
_____________________________________________________________________
| |
| 1000 CALL SQLStatusCheck |
| ConnectDBE = .FALSE. |
| CALL ReleaseDBE |
| 2000 RETURN |
| EXEC SQL WHENEVER SQLERROR CONTINUE |
| EXEC SQL WHENEVER SQLWARNING CONTINUE |
| EXEC SQL WHENEVER NOT FOUND CONTINUE |
| END |
| |
| SUBROUTINE BeginWork 3 |
| |
| C (**** Subroutine to Begin a Transaction ****) |
| |
| C (* Begin Communication Area *) |
| EXEC SQL INCLUDE SQLCA |
| |
| C (**** Begin Host Variable Declarations ****) |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| |
| EXEC SQL WHENEVER SQLERROR GOTO 1000 |
| EXEC SQL WHENEVER SQLWARNING GOTO 1000 |
| EXEC SQL WHENEVER NOT FOUND GOTO 1000 |
| |
| WRITE (*,*) 'BEGIN WORK' |
| EXEC SQL BEGIN WORK |
| GOTO 2000 |
| 1000 CALL SQLStatusCheck |
| CALL ReleaseDBE |
| 2000 RETURN |
| EXEC SQL WHENEVER SQLERROR CONTINUE |
| EXEC SQL WHENEVER SQLWARNING CONTINUE |
| EXEC SQL WHENEVER NOT FOUND CONTINUE |
| END |
| |
| SUBROUTINE CommitWork 4 |
| |
| C (**** Subroutine to Commit Work ****) |
| |
| C (* Begin Communication Area *) |
| EXEC SQL INCLUDE SQLCA |
| |
| C (**** Begin Host Variable Declarations ****) |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| |
| EXEC SQL WHENEVER SQLERROR GOTO 1000 |
| EXEC SQL WHENEVER SQLWARNING GOTO 1000 |
| EXEC SQL WHENEVER NOT FOUND GOTO 1000 |
| |
_____________________________________________________________________
Figure 9-5. Program forex9a: Sample Program Using EXECUTE IMMEDIATE (page 3 of 6)
______________________________________________________________________________
| |
| WRITE (*,*) 'COMMIT WORK' |
| EXEC SQL COMMIT WORK |
| GOTO 2000 |
| 1000 CALL SQLStatusCheck |
| CALL ReleaseDBE |
| 2000 RETURN |
| EXEC SQL WHENEVER SQLERROR CONTINUE |
| EXEC SQL WHENEVER SQLWARNING CONTINUE |
| EXEC SQL WHENEVER NOT FOUND CONTINUE |
| END |
| |
| SUBROUTINE ReleaseDBE 5 |
| |
| C (**** Subroutine to Release the DBEnvironment ****) |
| |
| C (* Begin Communication Area *) |
| EXEC SQL INCLUDE SQLCA |
| |
| C (**** Begin Host Variable Declarations ****) |
| EXEC SQL BEGIN DECLARE SECTION |
| EXEC SQL END DECLARE SECTION |
| |
| WRITE (*,*) 'RELEASE DBEnvironment' |
| EXEC SQL RELEASE |
| RETURN |
| END |
| |
| SUBROUTINE SQLExplain 6 |
| |
| C (**** Subroutine to CALL SQLEXPLAIN ****) |
| |
| C (* Begin Communication Area *) |
| EXEC SQL INCLUDE SQLCA |
| |
| C (**** Begin Host Variable Declarations ****) |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*80 SQLMessage |
| EXEC SQL END DECLARE SECTION |
| |
| WRITE (*,*) 'Call SQLExplain' |
| EXEC SQL SQLEXPLAIN :SQLMessage |
| WRITE (*,*) ' ' |
| WRITE (*,*) SQLMessage |
| RETURN |
| END |
| |
| LOGICAL*2 FUNCTION Update() 7 |
| |
| C (**** Function to Update the user entered tables ****) |
| Static = 'UPDATE STATISTICS FOR TABLE' |
| |
______________________________________________________________________________
Figure 9-5. Program forex9a: Sample Program Using EXECUTE IMMEDIATE (page 4 of 6)
___________________________________________________________________________
| |
| C (**** Begin SQL Communication Area ****) |
| EXEC SQL INCLUDE SQLCA |
| |
| CHARACTER*30 Static |
| |
| C (**** Begin Host Variable Declarations ****) |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*50 TableName |
| CHARACTER*81 Command |
| CHARACTER*80 SQLMessage |
| EXEC SQL END DECLARE SECTION |
| |
| EXEC SQL WHENEVER SQLERROR GOTO 1000 |
| EXEC SQL WHENEVER SQLWARNING GOTO 1000 |
| EXEC SQL WHENEVER NOT FOUND GOTO 1000 |
| |
| WRITE (*,100) 7A |
| |
| 100 FORMAT (/$, 'Enter Table Name or a / to Stop > ') |
| READ (*,110) TableName |
| 110 FORMAT(A50) |
| IF (TableName .EQ. '/') THEN |
| WRITE (*,*) 'No more tables to update.' |
| Update = .FALSE. |
| ELSE |
| WRITE(*, '(''Table to Update - '',A25)') TableName |
| Command = Static // TableName // ';' |
| WRITE (*, '(''Command - '',A56)') Command |
| EXEC SQL EXECUTE IMMEDIATE :Command 7B |
| |
| WRITE (*,*) 'SQL command executed successfully.' |
| Update = .TRUE. |
| ENDIF |
| GOTO 2000 |
| 1000 CALL SQLStatusCheck |
| WRITE (*,*) 'SQL command not successfully executed.' |
| 2000 CONTINUE |
| RETURN |
| EXEC SQL WHENEVER SQLERROR CONTINUE |
| EXEC SQL WHENEVER SQLWARNING CONTINUE |
| EXEC SQL WHENEVER NOT FOUND CONTINUE |
| END |
| |
| SUBROUTINE SQLStatusCheck 8 |
| |
| C (**** Subroutine SQLStatusCheck checks status |
| of SQL commands ****) |
| C (**** and print HPSQL error messages. ****) |
| LOGICAL*2 Abort, Check |
| INTEGER MultipleRows, DeadLock, NotFound |
| PARAMETER (MultipleRows = -10002, |
___________________________________________________________________________
Figure 9-5. Program forex9a: Sample Program Using EXECUTE IMMEDIATE (page 5 of 6)
_________________________________________________________________________
| |
| 1 DeadLock = -14024, |
| 2 NotFound = 100) |
| |
| C (**** Begin Communication Area ****) |
| EXEC SQL INCLUDE SQLCA |
| |
| C (**** Begin Host Variable Declarations ****) |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*80 SQLMessage |
| EXEC SQL END DECLARE SECTION |
| |
| Abort = .FALSE. |
| Check = .TRUE. |
| |
| IF (SQLWarn(0) .EQ. 'W') THEN |
| WRITE (*,*) 'HPSQL Warning!' |
| ELSEIF (SQLCode .EQ. NotFound) THEN |
| WRITE (*,*) 'No record found for this PartNumber!' |
| ELSEIF (SQLCode .EQ. MultipleRows) THEN |
| WRITE (*,*) 'Multiple records exit for this PartNumber!'|
| ELSEIF (SQLCode .EQ. DeadLock) THEN |
| Abort = .TRUE. |
| WRITE (*,*) 'A DEADLOCK has occurred!' |
| ELSEIF (SQLCode .LT. DeadLock)THEN |
| Abort = .TRUE. |
| WRITE (*,*) 'Serious ALLBASE/SQL error!' |
| ELSEIF (SQLCode .LT. 0) THEN |
| WRITE (*,*) 'ALLBASE/SQL error!' |
| ENDIF |
| |
| DO WHILE (Check) |
| CALL SQLExplain |
| |
| IF (SQLCode .EQ. 0) THEN |
| Check = .FALSE. |
| ENDIF |
| END DO |
| |
| IF (Abort) THEN |
| CALL CommitWork |
| CALL ReleaseDBE |
| ENDIF |
| |
| RETURN |
| END |
| |
| |
_________________________________________________________________________
Figure 9-5. Program forex9a: Sample Program Using EXECUTE IMMEDIATE (page 6 of 6)