<SQL [options] "SQL command" ["SQL fragment" ...][ /]>
... Vortex statements ...
SQL statement executes the given Texis SQL command
(with a "
;" appended if needed). The returned rows' fields
are assigned to the Vortex variables of the same name, one field per
variable value. For each result row returned by the command, the
corresponding variables are updated, and the statements inside the
SQL block are executed.
The field variables returned by
SQL behave just like
LOOP variables (see here), in that only the
current value returned is visible inside the
SQL loop. This
allows easy iteration of result rows, yet retains all rows for future
With the default <sqlcp arrayconvert> settings, certain
multi-value result types may be converted into more than one Vortex
variable value per SQL row. For example, a single
result value that contains 3 strings will add 3
to its Vortex variable, instead of 1
strlst value. This
conversion increases flexibility by avoiding the need for manual
conversion of cumbersome types like
strlst, but can cause
result columns to become out of sync with each other, if some have
more values added per row than others.
Flags/attributes that may be given before the SQL statement include:
SKIP=nSkip the first n result rows. The skipped rows are not assigned to variables; the next (n+1) row retrieved will be the first (0th) value of variables. This is typically used to skip to a specific "page" of results when paginating output.
MAX=nReturn at most n result rows. The default is all rows. Note that result rows are only counted after the initial
SKIP, if any.
NOVARS[=varname[,varname,...]]Do not return any field variables from the statement; just execute and loop over it. This is useful for
deletestatements, which normally return the variable(s) from inserted/deleted rows, which may sometimes conflict with parameters or other variables. It can also save memory for SQL statements where many unneeded rows are returned. Note that
$nextare still set as usual, so rows can be counted. In version 6 and later, a CSV list of zero or more variable names may be given: if a returned column name is in the list, it is not returned/assigned, otherwise it is returned.
NOVARSwith no list (or an empty list) blocks all variables.
OKVARS=varname[,varname,...]Only return variables in the given list; columns not in the list are not returned/assigned to variables. Added in version 6.
ROWSet return variables to a single row; do not append in a list. At each iteration, the previous row's values are freed, and the return variables have a single value at the end of the loop. This is useful for SQL statements that return a large number of rows that are only needed one at a time: the script might otherwise run out of memory trying to retain all rows in the variables.
DB=dbSet the database to use for this statement. Normally the database used is the one last set with the
<DB>statement (here). However the
SQLoverrides this value for this
SQLstatement. This is useful for avoiding side-effects in
SQLstatements that work on an alternate database and don't want to disturb the current
<DB>value (which can be obtained with the
vxinfofunction, here). Added in version 3.01.966300000 20000814.
USER=userSet the username to access the database as. Normally taken from the
<USER>statement value (here); this option overrides it without disturbing it. Added in version 3.01.966300000 20000814.
PASS=passwordSet the password to access the database with. Normally taken from the
<PASS>statement value (here); this option overrides it without disturbing it. Added in version 3.01.966300000 20000814.
NULL=valueSet what value(s), if any, to match against parameters that should be dropped from the statement. The default is the value of
$null. Added in version 3.01.966300000 20000814. (See Parameter Substitution, here). To set the null value to no-values, use the
NONULLSet the parameter-dropping match value(s) to none, i.e. do no parameter-dropping. The default is the value of
$null. Added in version 3.01.966300000 20000814. (See Parameter Substitution, here).
OUTPUT=formatAlso output the results of the SQL statement in the specified format. Normally the
NOVARSflag would be specified with this option, since the variables are now being printed. The currently known formats are:
xmlOutputs the data in XML. Each field will be an element, named the same as the Vortex variable would be, nested in a
<result>element per row, within a single
<results>top-level element. Added in version 4.00.1001000000 20010920. In version 5.01.1226024000 20081106 and later,
strlstcolumns have their individual string values printed in child
xml:adoOutputs the data in an XML format that is compatible with ADO's XML persistence format. An ADO RecordSet can be opened with:
rs.open URL,,,,adCmdFileIn general it is preferable to fetch the already formatted data and display that if you are using ASP. Added in version 3.0.974700000 20001120.
In addition, the following flags may be appended to the
:utf8Assume data is ISO-8859-1 and translate it to UTF-8.
:noutf8Do not translate data from ISO-8859-1 to UTF-8; assume data is already UTF-8. Illegal UTF-8 sequences, however, will still be assumed to be ISO-8859-1 and will be translated to UTF-8. This is the default in version 7 and later; previous versions defaulted to
:base64Data which contains bytes less than 32 or greater than 126 is encoded in base64. Enables output of binary data (for some XML processors which understand base64). Not supported in ADO format. Added in version 5.01.1100210584 20041111.
PROVIDER=$providerSpecifies what provider will execute the SQL. The value may be
texisfor normal internal Texis execution (which is also the default if unspecified or empty), or
odbcto connect to an ODBC server to run the SQL. The
odbcprovider requires the
CONNECTSTRoption to be set, and is currently only supported in Windows versions of Vortex. Added in version 5.
In version 5.01.1222808000 20080930 and later, wide character
result data (e.g.
from the ODBC driver is converted to
varchar parameter data (e.g. for
is converted from UTF-8 to
CONNECTSTR=$connectstrProvides the ODBC connection string if
PROVIDERis set to
odbc. Added in version 5.
PARAMPREFIX=$prefixSets the Vortex variable parameter name prefix: an alternate method of passing SQL parameters, used only in exceptional circumstances. See Dynamic Parameters (here) for details. Added in version 5.01.1226541000 20081112.
BREAK statement is encountered inside the loop, the loop
is exited at that point, as if the SQL command generated no more rows.
If a SQL statement that modifies a row is nested inside a SQL selecting from the same table then care may need to be used to ensure that the same row is not updated multiple times. This is most likely to occur when the outer SQL is selecting without an index, and the update may increase the size of the record. The size increase may move the record, and it may then reappear in the select.