Parameter Substitution

Variables can be placed in the SQL command as parameters. These variables are not merely substituted as strings, but become distinct arguments to the Texis SQL command, preserving their type. Thus, no escapement of special SQL characters like "'" or ";" needs to be done for parameters, and binary data such as images may be passed safely for byte fields. There is also no danger of the SQL command being modified by a rogue argument (aka "SQL injection"), e.g. a variable argument value like "; DROP TABLE customer" won't end the SQL command and get executed (but see notes under SQL Command Construction, here). Parameters that are multi-value variables may be converted into another type (e.g. an array of varchar values converted into a strlst), depending on the current <sqlcp arrayconvert> settings.

To simplify construction of complex WHERE clauses, parameter variables can be automatically dropped from a SQL SELECT query. Normally, all variables embedded in the SQL command become parameters; if a variable is unset (has no values) it's treated as a single empty string ("") parameter. If the NULL option is set, however, any single-value parameter that matches any value of that option is dropped from the query, and its part of the WHERE clause collapses.

For example, in the following query NULL is set to "any":

<$xval = "any">
<$yval =
  "This is a test."
  "So is this."
>
<SQL NULL="any" "SELECT result
                 FROM Text
                 WHERE X = $xval AND Y = $yval">
  $result
</SQL>

Since the SQL parameter $xval has one value that matches the NULL option, it is dropped from the SQL query and the WHERE clause becomes equivalent to "WHERE Y = $yval". Unset parameter variables are treated as empty strings ("") when comparing against the NULL option.

Parameter-dropping allows complicated queries to collapse into simple ones when the extra variables are not needed, without cumbersome checking of all the parameters. A common use is in HTML forms, where there may be several search fields that are optional (e.g. an option checkbox, or subject and author text fields). By setting NULL to the empty string (NULL=""), any unset checkboxes or empty (unfilled) text fields from the form can be implicitly dropped from the SQL query.

Note that this feature only applies to SELECT clauses. This is to help prevent inadvertent deletion/modification of too many rows. For example, a DELETE statement whose WHERE clause parameters are accidentally left empty by the user might otherwise delete the entire table, if NULL is "". Thus, any unwanted parameters must explicitly be left out in non-SELECT statements.