Table 3-4 shows the execution characteristics of static and dynamic SQLs.

Table 3-4 Execution characteristics of static and dynamic SQLs

^* Processing efficiency improves when an SQL having the same character string is executed several times.

When static SQL statements are executed, values to be inserted, new values to be set, and search conditions can be modified. When dynamic SQL statements are executed, any part of the SQL statements, such as the table name, column names, and conditional expressions, can be changed, in addition to those values that can be changed during execution of static SQL statements.

The following examples show values that can be changed during execution of static and dynamic SQL statements. Bold letters indicate the areas where values can be changed.

Figure 3-2 Example of values provided at the time of SQL execution

A dynamic SQL provides more flexibility in changing search conditions than a static SQL. However, dynamic SQL statements must be executed each time a condition is changed. For this reason, execution efficiency (processing efficiency) must be considered when deciding whether or not to use a dynamic SQL.

(a) Preprocessing and executing dynamic SQL statements

Dynamic SQL statements need to be processed first by the PREPARE statement and then executed during UAP execution. How a dynamic SQL statement is executed depends on whether the SQL statement to be preprocessed is the dynamic SELECT statement or another statement. If the SQL statement to be preprocessed is the SELECT statement, it is executed with the OPEN, FETCH, and CLOSE statements. If the SQL statement to be preprocessed is a statement other than the dynamic SELECT statement, it is executed with the EXECUTE statement. The EXECUTE IMMEDIATE statement can also be used to both preprocess and execute an SQL statement in a single operation. When the same SQL statement is to be executed dynamically by changing values, ? parameters should be used so that the SQL statement is preprocessed only once, rather than having to preprocess the SQL statement several times; the SQL statement can then be executed repeatedly by changing the values that are assigned to the ? parameters. This results in improved performance (processing efficiency). For details about ? parameters, see the HiRDB Version 8 SQL Reference manual.

Figure 3-3 shows the dynamic SQL execution mode, and Table 3-5 lists the SQLs that can be preprocessed by the PREPARE statement and the SQL statements that can be preprocessed and executed by the EXECUTE IMMEDIATE statement.

Figure 3-3 Dynamic SQL execution mode

Table 3-5 SQL statements preprocessed by the PREPARE statement, and SQL statements preprocessed and executed by the EXECUTE IMMEDIATE statement

Type	SQL statement	PREPARE	EXECUTE IMMEDIATE
Data Manipulation SQL	ASSIGN LIST statement	U3	U
	CALL	U3	U
	DELETE1	U3	U
	Preparable dynamic DELETE statement: locating	U	U
	DROP LIST statement	U3	U
	INSERT	U3	U
	PURGE TABLE	U3
	Single-row SELECT2	U3	U
	Dynamic SELECT	U4
	UPDATE1	U3	U
	Preparable dynamic UPDATE statement: locating	U	U
	Assignment statement	U3
Control SQL	COMMIT
	CONNECT
	DISCONNECT
	LOCK TABLE	U3	U
	CONNECT statement with RD-node specification6
	DISCONNECT statement with RD-node specification6
	ROLLBACK
	SET CONNECTION statement6
	SET SESSION AUTHORIZATION statement
Definition SQL	ALTER PROCEDURE	U3	U
	ALTER ROUTINE	U3	U
	ALTER TABLE	U3	U
	ALTER TRIGGER	U3	U
	COMMENT	U3	U
	CREATE ALIAS6	U3	U
	CREATE AUDIT	U3	U
	CREATE CONNECTION SECURITY	U3	U
	CREATE FOREIGN INDEX5	U	U
	CREATE FOREIGN TABLE5	U	U
	CREATE FUNCTION	U3	U
	CREATE INDEX	U3	U
	CREATE PROCEDURE	U3	U
	CREATE SCHEMA	U3	U
	CREATE SERVER5	U	U
	CREATE TABLE	U3	U
	CREATE TRIGGER	U3	U
	CREATE TYPE	U3	U
	CREATE USER MAPPING5	U	U
	CREATE VIEW	U3	U
	DROP ALIAS6	U3	U
	DROP AUDIT	U	U
	DROP CONNECTION SECURITY	U3	U
	DROP DATA TYPE	U3	U
	DROP FOREIGN INDEX5	U	U
	DROP FOREIGN TABLE5	U	U
	DROP FUNCTION	U3	U
	DROP INDEX	U3	U
	DROP PROCEDURE	U3	U
	DROP SCHEMA	U3	U
	DROP SERVER5	U	U
	DROP TABLE	U3	U
	DROP TRIGGER	U3	U
	DROP USER MAPPING5	U	U
	DROP VIEW	U3	U
	GRANT	U3	U
	REVOKE	U3	U

U: Can be used.

: Cannot be used.

Note

An SQL statement that contains embedded variables cannot be executed dynamically; in this case, ? parameters must be used instead of embedded variables. For details about ? parameters, see the HiRDB Version 8 SQL Reference manual.

¹ Operations requiring the use of a cursor cannot be performed.

² The SQL must not contain an INTO clause.

³ Executed by the EXECUTE statement.

⁴ Executed by the OPEN, FETCH, or CLOSE statement.

⁵ Can be used if HiRDB External Data Access is installed.

⁶ This SQL statement is applicable to the UNIX version only.

An example of inserting data into a dynamically-specified table is shown as follows:

Figure 3-4 Example of inserting data into a dynamically specified table

(b) Using the EXECUTE statement and the EXECUTE IMMEDIATE statement

The EXECUTE IMMEDIATE statement is functionally equivalent to executing the PREPARE and EXECUTE statements in succession. When SQL statements are to be executed repeatedly, it is more efficient to execute it iteratively using the EXECUTE statement after first preprocessing it with the PREPARE statement than to execute it several times with the EXECUTE IMMEDIATE statement.

(c) Executing dynamic SQL statements with preprocessing a dynamic SELECT statement

This execution mode varies depending on whether the SQL statement to be preprocessed is a dynamic SELECT statement or a statement other than the dynamic SELECT statement. If the SQL statement to be preprocessed is a dynamic SELECT statement, the SQL statements after preprocessing should be executed using the OPEN, FETCH, or CLOSE statement; if it is not a dynamic SELECT statement, an EXECUTE statement should be used. An example of executing SQL statements with processing a dynamic SELECT statement is shown as follows:

Figure 3-5 Example of dynamic processing when the preprocessed SQL is a dynamic SELECT statement

(d) Dynamic execution of an SQL statement that uses a cursor for a dynamic SELECT statement

When a dynamic SELECT statement is preprocessed and an SQL statement that uses a cursor is executed dynamically for that dynamic SELECT statement, a cursor declared in a cursor declaration is not used. In this case, a cursor allocated with the ALLOCATE CURSOR statement is used for the preprocessed dynamic SELECT statement. An example of dynamic execution of an SQL statement that uses a cursor for a dynamic SELECT statement is shown below.

 
PREPARE GLOBAL :SEL FROM :XCMND;
//Adds an extended statement name (:SEL='SEL1') to the dynamic SELECT statement that was set to an embedded variable (:XCMND).
ALLOCATE GLOBAL :CR CURSOR FOR GLOBAL :SEL;
//Allocates a cursor (:CR='CR1') to the query identified by the extended statement name (:SEL='SEL1').
PREPARE UPD1 FROM
  'UPDATE SET C1=? WHERE CURRENT OF GLOBAL CR1';
//Preprocesses the UPDATE statement that uses the cursor (CR1) and attaches an SQL statement identifier (UPD1).
OPEN GLOBAL :CR;
//Adds a cursor (:CR='CR1').
FETCH GLOBAL :CR INTO :XKEKKA;
//Reads the search results obtained using the cursor (:CR='CR1') into an embedded variable (:XKEKKA).
EXECUTE UPD1 USING :XDATA;
//Executes the UPDATE statement for the preprocessed SQL statement identifier (UPD1). At this time, the embedded variable (:XDATA) corresponding to the ? parameter is specified.
CLOSE GLOBAL :CR;
//Closes the cursor (:CR='CR1').
 

(e) Receiving information determined during dynamic SQL execution

When a UAP dynamically executes SQL statements, it uses an SQL Descriptor Area as the area for notifying HiRDB about information determined during the execution (including the number, attributes, and addresses of data transfer areas). To realize dynamic execution, the UAP receives search item information for SQL statements preprocessed with the PREPARE statement in the SQL Descriptor Area by using one of the following methods:

• Executing the DESCRIBE statement
• Specifying OUTPUT and INPUT when executing the PREPARE statement. (When this method is used, the number of communications might be reduced, because information can be received during execution of the PREPARE statement.)

For details about the DESCRIBE statement, see the manual HiRDB Version 8 SQL Reference. For an example of the use of SQL Descriptor Areas, see B. SQL Descriptor Area.