In rare circumstances, the SQL statement - and thus the number of possible parameters - is not known at script writing time, or may change dynamically. For example, a script may be attempting to execute a series of SQL statements (perhaps read from a log file) where the number of parameters changes with each statement, and the parameters are encoded separately. Parsing and modifying each SQL statement to insert an arbitrary number of -variables would be cumbersome.
PARAMPREFIX option can be set. When
PARAMPREFIX is non-empty, parameters are denoted in the SQL
statement by embedded question marks ("
?") instead of
-variables, and their values come from the Vortex variables
with the name prefix denoted by the value of
numbers appended starting from 1 for the first parameter.
For example, given the statement:
<SQL PARAMPREFIX="myParam" "SELECT * FROM myTable WHERE x=? AND y=?">
the Vortex variables myParam1 and myParam2 would be
used for the corresponding
?-parameters. Thus, arbitrary parameters from encoded data
could be set by declaring (with
<local>) a block of
myParamN variables, and sequentially assigning them from the
encoded data with
Note that a
? embedded inside a SQL literal string
(e.g. "select x + 'A question ?' from myTable") does not denote
PARAMPREFIX is an awkward method of passing
parameters in most circumstances; embedding -variables is
preferred, especially when the SQL statement is known.
PARAMPREFIX is only used in rare situations, with truly
unknown SQL statements.