 |
» |
|
|
|
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
.
.
EXEC SQL EXECUTE :DynamicCommand
|
As soon as you process a COMMIT WORK or ROLLBACK WORK command,
the temporary section is deleted.
Figure 9-6 illustrates the runtime dialog for a program that
uses the PREPARE and EXECUTE commands, program forex9b. The
program starts a DBE session in the DBEnvironment named
PartsDBE, then prompts for entry of an SQL command. After
the user enters a command, the program displays the entered SQL
command, and the command is dynamically preprocessed and
executed. When the program user enters a slash (/) in response
to the prompt, the transaction is committed and the program
terminates. Note what happens when a SELECT command is entered.
As illustrated in Figure 9-7, the main program 1 first
performs a function named ConnectDBE 2 to start a DBE
session. The CONNECT command starts the session in the
DBEnvironment named PartsDBE.
The program then performs subroutine BeginWork 3 to
start a transaction with the BEGIN WORK command. Once a
transaction has been started, function PrepareExecute 7
is performed until Check evaluates to FALSE.
PrepareExecute first declares a dynamic host variable
DynamicCommand, which will hold the dynamic command to be
entered by the user.
Then the user is prompted for the non-query command 7A to be
dynamically prepared and executed. The entered command is then
prepared 7B, and if the command preparation is successful,
it is executed 7C. If the command was successfully executed, the
user is re-prompted for another non-query command. The function
terminates when PrepareExecute is set to FALSE by the user
entering a slash (/) in response to the command prompt 7A.
When PrepareExecute evaluates to FALSE, subroutine
CommitWork 4 is performed. This subroutine executes a
COMMIT WORK command. Then subroutine ReleaseDBE 5
executes a ROLLBACK WORK RELEASE command to terminate the DBE
session. After ReleaseDBE has executed, the program
terminates. Explicit status checking is used throughout this
program. When ALLBASE/SQL returns a non-zero value in SQLCode
following the execution of each embedded SQL command, subroutine
SQLStatusCheck 8 is performed. This subroutine writes
out messages based on the values of SQLCode and SQLWARN, then
calls SQLExplain 6 to display one or more messages.
SQLExplain executes the SQLEXPLAIN command and prints out
the error messages. If an error is very serious (SQLCode <
-14024), a flag named Abort is set, and subroutines
CommitWork and ReleaseDBE are performed before the
program is terminated.
Figure 9-6 Runtime Dialog of Program forex9b
Program to illustrate the PREPARE and EXECUTE commands -- forex9b
Event List:
CONNECT TO PartsDBE
BEGIN WORK
Prompt for SQL command
PREPARE SQL command
EXECUTE SQL command
Repeat the above 3 steps until the user enters a /
COMMIT WORK
RELEASE from DBEnvironment
CONNECT TO PartsDBE
Successful CONNECT
BEGIN WORK
Successful BEGIN
Enter an SQL non-query command or a / to stop:
>UPDATE STATISTICS FOR TABLE PurchDB.Parts;
Dynamic command to PREPARE is: UPDATE STATISTICS FOR TABLE PurchDB.Parts
PREPARE successful.
EXECUTE the command.
EXECUTE successful.
Enter an SQL non-query command or a / to stop:
>SELECT * FROM PurchDB.Parts;
Dynamic command to PREPARE is: SELECT * FROM PurchDB.Parts;
PREPARE successful.
EXECUTE the command.
HPSQL error!
Call SQLExplain
Module TEMP.FOREX9B(1) is not a procedure. (DBERR 2752)
Enter an SQL non-query command or a / to stop:
> /
No more commands.
COMMIT WORK
Successful COMMIT
RELEASE DBEnvironment
Successful RELEASE
|
Figure 9-7 Program forex9b: Sample Program Using PREPARE and EXECUTE
C * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
C * This program illustrates the use of SQL dynamic non-query *
C * commands executed from a FORTRAN program. *
C * This program demonstrates the use of the PREPARE and *
C * EXECUTE commands. *
C * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
IMPLICIT NONE
LOGICAL*2 PrepareExecute, Check, 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 PREPARE and EXECUTE
1command1s -- forex9b'
WRITE (*,*) ' '
WRITE (*,*) 'Event List:'
WRITE (*,*) ' CONNECT TO ../sampledb/PartsDBE'
WRITE (*,*) ' BEGIN WORK'
WRITE (*,*) ' Prompt for SQL command:'
WRITE (*,*) ' PREPARE SQL command'
WRITE (*,*) ' EXECUTE SQL command'
WRITE (*,*) ' Repeat the above 3 steps until the user
1enters a /'
WRITE (*,*) ' COMMIT WORK'
WRITE (*,*) ' RELEASE from ../sampledb/DBEnvironment'
WRITE (*,*) ' '
IF (ConnectDBE()) THEN
CALL BeginWork
Check = .TRUE.
DO WHILE (Check)
Check = PrepareExecute()
END DO
CALL CommitWork
CALL ReleaseDBE
ENDIF
STOP
END
C (* Beginning of the Subroutines *)
LOGICAL*2 FUNCTION ConnectDBE() 2
C (**** Subroutine to CONNECT TO ../sampledb/PartsDBE ****)
INTEGER*2 OK
PARAMETER (OK = 0)
C (* Begin Communication Area *)
EXEC SQL INCLUDE SQLCA
C (**** Begin Host Variable Declarations ****)
EXEC SQL BEGIN DECLARE SECTION
EXEC SQL END DECLARE SECTION
WRITE (*,*) 'CONNECT TO ../sampledb/PartsDBE'
EXEC SQL CONNECT TO '../sampledb/PartsDBE'
ConnectDBE = .TRUE.
IF (SQLCode .NE. OK) THEN
ConnectDBE = .FALSE.
CALL SQLStatusCheck
ELSE
WRITE (*,*) 'Successful CONNECT'
ENDIF
RETURN
END
SUBROUTINE BeginWork 3
C (**** Subroutine to Begin a Transaction ****)
INTEGER*2 OK
PARAMETER (OK = 0)
C (* Begin Communication Area *)
EXEC SQL INCLUDE SQLCA
C (**** Begin Host Variable Declarations ****)
EXEC SQL BEGIN DECLARE SECTION
EXEC SQL END DECLARE SECTION
WRITE (*,*) 'BEGIN WORK'
EXEC SQL BEGIN WORK
IF (SQLCode .NE. OK) THEN
CALL SQLStatusCheck
CALL ReleaseDBE
ELSE
WRITE (*,*) 'Successful BEGIN'
ENDIF
RETURN
END
SUBROUTINE CommitWork 4
C (**** Subroutine to COMMIT WORK ****)
INTEGER*2 OK
PARAMETER (OK = 0)
C (* Begin Communication Area *)
EXEC SQL INCLUDE SQLCA
C (**** Begin Host Variable Declarations ****)
EXEC SQL BEGIN DECLARE SECTION
EXEC SQL END DECLARE SECTION
WRITE (*,*) 'COMMIT WORK'
EXEC SQL COMMIT WORK
IF (SQLCode .NE. OK) THEN
CALL SQLStatusCheck
CALL ReleaseDBE
ELSE
WRITE (*,*) 'Successful COMMIT'
ENDIF
RETURN
END
SUBROUTINE ReleaseDBE 5
C (**** Subroutine to RELEASE ../sampledb/PartsDBE ****)
INTEGER*2 OK
PARAMETER (OK = 0)
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 ROLLBACK WORK RELEASE
IF (SQLCode .NE. OK) THEN
CALL SQLStatusCheck
CALL ReleaseDBE
ELSE
WRITE (*,*) 'Successful RELEASE'
ENDIF
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 (*,100) SQLMessage
100 FORMAT(A80)
RETURN
END
LOGICAL*2 FUNCTION PrepareExecute() 7
C (**** Function to PREPARE and EXECUTE the ****)
C (**** user-entered command. ****)
CHARACTER*80 CMD1
INTEGER*2 OK
PARAMETER (OK = 0)
C (**** Begin SQL Communication Area ****)
EXEC SQL INCLUDE SQLCA
C (**** Begin Host Variable Declarations ****)
EXEC SQL BEGIN DECLARE SECTION
CHARACTER*80 DynamicCommand
EXEC SQL END DECLARE SECTION
WRITE (*,100) 7A
100 FORMAT(/'Enter an SQL non-query command or / to STOP '
1 ,//$,' > ')
READ (*,110) DynamicCommand
110 FORMAT(A80)
IF (DynamicCommand .EQ. '/') THEN
WRITE (*,*) 'No more commands.'
PrepareExecute = .FALSE.
ELSE
WRITE (*, 120) DynamicCommand
120 FORMAT (/'The dynamic command to PREPARE is: '//, A80)
EXEC SQL PREPARE CMD1 FROM :DynamicCommand 7B
IF (SQLCode .NE. OK) THEN
CALL SQLStatusCheck
WRITE (*,*) 'PREPARE failed.'
ELSE
WRITE (*,*) 'PREPARE successful.'
WRITE (*,*) 'EXECUTE the command.'
EXEC SQL EXECUTE CMD1 7C
IF (SQLCode .NE. OK) THEN
CALL SQLStatusCheck
ELSE
WRITE (*,*) 'EXECUTE successful.'
ENDIF
ENDIF
PrepareExecute = .TRUE.
ENDIF
RETURN
END
SUBROUTINE SQLStatusCheck 8
C (**** Subroutine SQLStatusCheck checks status of SQL
1commands ****)
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
|
|
