|
|
|
Repeat queries usually require parameters that take on different values each time the query is invoked. These parameters are represented by host variables in embedded SQL. OpenAPI does not support embedded host variable references and dynamic parameter markers cannot be used since the query is not being prepared (although the define/invoke mechanism appears similar to the prepare/execute scenario of dynamic SQL).
This section describes a parameter marker mechanism used by embedded SQL to implement support for host variables. An OpenAPI application can use this mechanism as an alternative to dynamic SQL for standard queries. The following section extends the parameter marker mechanism for use in repeat queries.
|
|
|
In place of dynamic parameter markers (?) and embedded host variables (:host_var), you can form a query using the parameter marker ~V. A ~V marker can appear any place a dynamic parameter marker or embedded host variable is allowed. The ~V marker must be preceded and followed by a space character. Values for the parameter markers are sent with the query using IIapi_setDescriptor() and IIapi_putParms(), in the same order the parameter markers appear in the query text.
As an example, the following query:
select * from employee where dept = ~V and age > ~V
when sent with parameter values of 'admin' and 35 is identical to the query:
select * from employee where dept = 'admin' and age > 35
The advantage is that the query using ~V does not require the application to build the query text at runtime using values not available at compile time; neither does it require the use of a cursor to perform the query dynamically.