A dynamic SQL statement cannot contain host variables directly. It is, however, possible to provide a dynamic SQL statement after it has been prepared with value parameters at execution time. The dynamic statement must then contain a host variable marker for every host variable. A host variable marker is represented by a question mark (?).
Example:
EXEC SQL
PREPARE statement_id FROM "DELETE FROM CRUISE WHERE CRUISE_ID = ?";
EXEC SQL
EXECUTE STATEMENT_ID USING :cruise_id;
The dynamic DELETE statement contains one host variable marker, so the USING clause in the EXECUTE statement contains one host variable. The host variable cruise_id is used to provide a parameter for the prepared DELETE statement. It is as if the following static SQL statements were executed:
EXEC SQL
DELETE FROM CRUISE WHERE CRUISE_ID = :cruise_id;
The host program can re-execute repeatedly the prepared statement by supplying a fresh value in the host variable with each iteration.
In principle, a host variable marker may appear everywhere in a statement where a host variable may appear. Because of the nature of dynamic SQL, however, there are certain restrictions. The following rules apply:
a host variable marker is not allowed to appear in a derived column list
only one operand of a diadic arithmetic operator or comparison operator may be a host variable marker, for example, ? = ? or ? * ? is not allowed.
the first two operands of a BETWEEN or IN operator cannot be host variable markers, for example, ? IN (?,...) is not allowed. However, 5 + ? IN (?,...) is allowed.
The reason for these restrictions is that at the time the dynamic SQL statement is compiled, the data type of each one of the host variable markers needs to be determined. In the cases described above this cannot be done.
As with SELECT statements, there are different methods to deal with host variable markers. One method can be applied in situations where the number of host variable markers is constant and their type is known and also constant. Another method must be applied if the number of host variable markers varies. Both methods are described in the following sections.
When the number and data types of the host variable markers are constant and known at compilation time in a dynamic SQL statement, a matching set of host variables can be defined to be used to provide values prior to the execution of the prepared dynamic statement. These host variables can be specified in the USING clause of either an EXECUTE or an OPEN statement.
For NON-SELECT statements, the host variables used to resolve the host variable markers must be specified in the USING clause on the EXECUTE statement. The host variables in the USING clause must be specified in the same order as the host variable markers were specified in the dynamic SQL statement.
Example:
EXEC SQL
PREPARE statement_id FROM "INSERT INTO CRUISE VALUES (?,?,?,?,?)";
EXEC SQL
EXECUTE statement_id USING :hv1,:hv2,:hv3,:hv4,:hv5;
For SELECT statements, the host variables used to resolve the host variable markers must be specified in the USING clause appended to the OPEN statement. The host variables in the USING clause must be specified in the same order as the host variable markers were specified in the dynamic SQL statement.
Example:
EXEC SQL
PREPARE statement_id FROM "SELECT CRUISE_ID FROM CRUISE WHERE CRUISE_ID = ?";
EXEC SQL
DECLARE ABC CURSOR FOR statement_id;
EXEC SQL
OPEN ABC USING :hv1;
When the number and data types of the host variable markers varies with each dynamically prepared statement and/or their data type cannot be pre-determined, it is not possible to define a matching set of host variables to provide values prior to the execution of the prepared statement.
In that case, the application program needs to get information about the host variable markers in a prepared statement dynamically. The application program can either do this itself by analyzing the dynamic SQL statement, or Adabas SQL Gateway Embedded SQL can provide this information in an SQL descriptor area using a PREPARE or DESCRIBE statement.
Upon return from an appropriate PREPARE or DESCRIBE statement, Adabas SQL Gateway Embedded SQL will have filled the SQLDA with information about each one of the host variable markers. This information can then be used by the application program to allocate and assign host variables for each one of the host variable markers. Note that it is possible at this stage to change the data type description of a host variable in the SQLDA. Be aware that this may lead to runtime errors if the data type of a host variable is changed to one that is incompatible with the one established by Adabas SQL Gateway Embedded SQL.
Note: Such an input SQLDA is a separate instance of an output SQLDA but has the same structure.
For NON-SELECT statements, the input SQLDA must be supplied with the EXECUTE statement. The host variables described in the SQLDA must be specified in the same order as the host variable markers were specified in the dynamic SQL statement.
Example:
EXEC SQL
PREPARE statement_id FROM "INSERT INTO CRUISE VALUES (?,?,?,?,?)";
EXEC SQL
DESCRIBE statement_id INTO INPUT :input_sqlda;
EXEC SQL
EXECUTE statement_id USING DESCRIPTOR :input_sqlda;
For SELECT statements, the input SQLDA must be supplied with the OPEN statement. The host variables in the input SQLDA must be specified in the same order as the host variable markers were specified in the dynamic SQL statement.
Example:
EXEC SQL
PREPARE statement_id
FROM "SELECT CRUISE_ID FROM CRUISE WHERE CRUISE_ID = ?";
EXEC SQL
DESCRIBE statement_id INTO INPUT :input_sqlda;
EXEC SQL
DECLARE ABC CURSOR FOR statement_id;
EXEC SQL
OPEN ABC USING DESCRIPTOR :input_sqlda;
A program which issues dynamic statements which contain host variable markers must perform the following steps:
Construct the dynamic SQL statement. The dynamic SQL statement must be constructed as a character string, which will contain host variable markers (?).
Prepare the dynamic SQL statement. The dynamic SELECT statement always has to be prepared using a PREPARE statement.
Establish information about the host variable markers. If the host variable markers are constant in number and data type, host variables may be applied statically. Otherwise an INTO input clause in the PREPARE or DESCRIBE statement must be used in order to obtain the information about the host variable markers. Variables must then be allocated dynamically.
Either assign values to any static host variables
Or load the input SQLDA. If the host variables are assigned dynamically, the SQLDA has to be supplied with information about them. The host variables themselves must have appropriate values assigned to them
Execute the dynamic SQL statement. A USING clause containing either references to the static host variables or the input SQLDA is appended to either the EXECUTE statement or the OPEN statement as required.