Dynamic Parameters

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.

Instead, the 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 PARAMPREFIX, with 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 x and y column ?-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 <setvar>.

Note that a ? embedded inside a SQL literal string (e.g. "select x + 'A question ?' from myTable") does not denote a parameter.

Note: 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.