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 EXECUTE IMMEDIATE
If you know in advance that a dynamic command will not be a query, you
can dynamically preprocess and execute the command in one step, using the
EXECUTE IMMEDIATE command. Figure 9-3 illustrates a procedure hosting a
dynamic UPDATE STATISTICS command that can be handled in this fashion.
Subroutine UpdateStatistics 1 prompts the user for a table name 2 .
The table name entered is assigned to the host variable CmdLine 3 to
complete the UPDATE STATISTICS command. After the command is prepared
and executed 4 , the transaction is terminated with a COMMIT WORK
command 5 or a ROLLBACK WORK command 6 , depending on the value in
SQLCA.SQLCODE. Terminating the transaction before accepting another table
name and re-executing the UPDATE STATISTICS command releases any locks
obtained and improves concurrency.
________________________________________________________________________________
| |
| SUBROUTINE UpdateStatistics 1|
| |
| |
| CHARACTER*50 TableName |
| |
| EXEC SQL BEGIN DECLARE SECTION |
| CHARACTER*1024 CmdLine |
| EXEC SQL END DECLARE SECTION |
| |
| |
| |
| DO WHILE (TableName .NE. '/') |
| |
| WRITE (*,*) 'Enter table name or a / to stop > ' 2 |
| READ(6,100) TableName |
| 100 FORMAT(A50) |
| IF (TableName .NE. '/') THEN |
| |
| CmdLine ='UPDATE STATISTICS FOR TABLE '// TableName // ;' 3 |
| |
| EXEC SQL EXECUTE IMMEDIATE :CmdLine 4 |
| |
| IF (SQLCode .EQ. 0) THEN |
| EXEC SQL COMMIT WORK 5 |
| ELSE |
| EXEC SQL ROLLBACK WORK 6 |
| ENDIF |
| |
| ENDIF (* END OF IF TABLENAME *) |
| |
| END DO |
| |
| RETURN |
| END (* END OF UPDATESTATISTICS PROCEDURE *) |
| |
________________________________________________________________________________
Figure 9-3. Procedure Hosting Dynamic Non-Query Commands
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;
I = MaxIterations
DO WHILE (I .NE. 0)
EXEC SQL EXECUTE MyNonQuery;
I = I - 1
END DO
As soon as you process a COMMIT WORK or ROLLBACK WORK command, the
temporary section is deleted.
The program examined later in this chapter under "Sample Program Using
PREPARE and EXECUTE" illustrates how to handle PREPARE and EXECUTE.