HP 3000 Manuals HP 3000 Manuals
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
.
.
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.
_______________________________________________________________________________
| |
| 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-6. Runtime Dialog of Program forex9b
_________________________________________________________________________
| |
| 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 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 DBEnvironment' |
| WRITE (*,*) ' ' |
| |
| IF (ConnectDBE()) THEN |
| CALL BeginWork |
| Check = .TRUE. |
| DO WHILE (Check) |
| Check = PrepareExecute() |
| END DO |
| CALL CommitWork |
| CALL ReleaseDBE |
| ENDIF |
| STOP |
| END |
| |
_________________________________________________________________________
Figure 9-7. Program forex9b: Sample Program Using PREPARE and EXECUTE
_____________________________________________________________________
| |
| |
| C (* Beginning of the Subroutines *) |
| |
| LOGICAL*2 FUNCTION ConnectDBE() 2 |
| |
| C (**** Subroutine to CONNECT TO 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 PartsDBE' |
| EXEC SQL CONNECT TO '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 |
| |
| |
| |
| |
| |
| |
| |
| |
_____________________________________________________________________
Figure 9-7. Program forex9b: Sample Program Using PREPARE and EXECUTE (page 2 of 6)
____________________________________________________________________
| |
| |
| 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 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 |
| |
____________________________________________________________________
Figure 9-7. Program forex9b: Sample Program Using PREPARE and EXECUTE (page 3 of 6)
_______________________________________________________________________
| |
| |
| 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 |
| |
| |
| |
_______________________________________________________________________
Figure 9-7. Program forex9b: Sample Program Using PREPARE and EXECUTE (page 4 of 6)
_______________________________________________________________________________
| |
| |
| 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 |
| |
| |
| |
| |
| |
_______________________________________________________________________________
Figure 9-7. Program forex9b: Sample Program Using PREPARE and EXECUTE (page 5 of 6)
________________________________________________________________________
| |
| 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-7. Program forex9b: Sample Program Using PREPARE and EXECUTE (page 6 of 6)