 |
» |
|
|
|
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. Figure 9-4 Runtime Dialog for Program forex9a
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-5 Program forex9a: Sample Program Using EXECUTE IMMEDIATE
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
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
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
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'
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,
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
|
|
