Executing Parameterized Statements, introduces parameterized statements, however, it is necessary to know the number of parameter markers (?) and their data types before run time. The preceding section shows how you can analyze a parameterized query so that a select-statement can be generated and subsequently described. The same principle can be used for parameterized non-query statements.
For example, suppose this DELETE statement is read from the terminal and assigned to DSTRING:
DELETE FROM QUOTATIONS WHERE PARTNO = ? AND SUPPNO = ?
Suppose also that the number of parameter markers (?) and their corresponding data types are unknown before run time. The same routine that you coded to scan the FROM and WHERE clauses of select-statements can be used to scan the above DELETE statement. Then, a SELECT statement containing the relevant columns can be constructed:
SELECT PARTNO, SUPPNO FROM QUOTATIONS
This select-statement is then prepared and described as in the previous section. The setup of the SQLDA is also identical: once the SQLDA is analyzed, space to hold the parameter marker values is allocated, and these values are read in and assigned to these locations. The SQLDA will be used for input to the WHERE clause of the SQL statement; no indicator variables are allowed. Because the statement is a non-query statement, the SQLDA is pointed to in the EXECUTE statement. Figure 66 illustrates the pseudocode for a parameterized non-query statement.
Figure 66. Parameterized Non-Query Statement
EXEC SQL INCLUDE SQLDA
.
.
.
READ DSTRING FROM TERMINAL
Scan the FROM clause and the WHERE clause of DSTRING for
parameter markers (?) and generate an appropriate query
in WSTRING.
Allocate an SQLDA of size 2 (2 was obtained from the scan).
SQLN = 2
EXEC SQL PREPARE S2 FROM :WSTRING
EXEC SQL DESCRIBE S2 INTO SQLDA
Analyze the results of the DESCRIBE.
Reset SQLTYPE to reflect that there is no indicator variable.
Allocate storage to hold the input values (the parameter marker (?)
values).
Set SQLDATA for each parameter marker (?) value.
EXEC SQL PREPARE S1 FROM :DSTRING
Read parameter marker (?) values from the terminal.
* A zero parameter value terminates the DO loop.
DO WHILE (parameters ¬= 0)
Assign the values to the storage allocated for
input variables.
EXEC SQL EXECUTE S1 USING DESCRIPTOR SQLDA
Prompt user for more values.
Read parameter marker (?) values from the terminal.
END-DO
.
.
.
|
You may need a more complex scanning routine, depending on how many different non-query statements you wish to process. For example, the above routine would have to be modified if you wanted to process INSERT statements. In that case, you would have to scan for the table and column names.
| Note: | Indicator variables are permitted when you are providing input to the INSERT statement with EXECUTE. |