SQL - execute SQL statement

SYNOPSIS

<SQL [options] "SQL command" ["SQL fragment" ...][ /]>
[ ... Vortex statements ...
</SQL>]

DESCRIPTION
The <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 (if given) are executed.

In version 7 syntax (when the syntaxversion pragma is 7, here), the field variables (e.g. SELECT columns) returned by SQL have their values accumulate, and behave like <LOOP> variables (here), in that only the current iteration's value is visible inside the <SQL> loop. With version 8 and later syntax, values never accumulate when looping (i.e. ROW is effectively always on), and thus the ROW flag is unneeded and not accepted.

In version 7 and later, the statement may be self-closing (<SQL ... />) instead of terminated with an end tag (</SQL>). The statement is then non-looping, however special variables (here) are still set (except for $loop/$next prior to version 8.00.1645136290 20220217); and SKIP/MAX are still respected. Return variables accumulate (ROW is not permitted).

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 strlst result value that contains 3 strings will add 3 varchar values 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=n Skip 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=n Return 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 insert and delete statements, 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 $loop and $next are still set as usual (if looping or version 8 syntax), 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. NOVARS with 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.

• ROW Set return variables to a single row; do not append/accumulate 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. For this reason, in version 8 and later syntax (when the syntaxversion pragma is 8 or more, here), values never accumulate for looping statements, and the ROW flag is not needed nor accepted: use the self-closing (non-loop) syntax to accumulate values.

• DB=db Set the database to use for this statement. Normally the database used is the one last set with the <DB> statement (here). However the DB option to SQL overrides this value for this SQL statement. This is useful for avoiding side-effects in SQL statements that work on an alternate database and don't want to disturb the current <DB> value (which can be obtained with the vxinfo function, here). Added in version 3.01.966300000 20000814.

• USER=user Set 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=password Set 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=value Set what value(s), if any, to match against parameters that should be dropped from the statement. The default is none if compatibilityversion (here) is 8 or more; if 7 or less, the defult is the value of $null, for legacy code. Added in version 3.01.966300000 20000814. (See Parameter Substitution, here). To set the null value to no-values, use the NONULL option.

• NONULL Set the parameter-dropping match value(s) to none, i.e. do no parameter-dropping. The default is none if compatibilityversion (here) is 8 or more; if 7 or less, the defult is the value of $null, for legacy code. Added in version 3.01.966300000 20000814. (See Parameter Substitution, here).

• OUTPUT=format Also output the results of the SQL statement in the specified format. Normally the NOVARS flag would be specified with this option, since the variables are now being printed. The currently known formats are:

  • xml Outputs 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, strlst columns have their individual string values printed in child <value> elements.

  • xml:ado Outputs 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,,,,adCmdFile In 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 OUTPUT string:

  • :utf8 Assume data is ISO-8859-1 and translate it to UTF-8.

  • :noutf8 Do 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 :utf8.

  • :base64 Data 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=$provider Specifies what provider will execute the SQL. The value may be texis for normal internal Texis execution (which is also the default if unspecified or empty), or odbc to connect to an ODBC server to run the SQL. The odbc provider requires the CONNECTSTR option 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. nvarchar/SQL_WCHAR) returned from the ODBC driver is converted to varchar UTF-8, and hi-bit varchar parameter data (e.g. for INSERT) is converted from UTF-8 to SQL_WCHAR).

• CONNECTSTR=$connectstr Provides the ODBC connection string if PROVIDER is set to odbc. Added in version 5.

• PARAMPREFIX=$prefix Sets 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.

If a BREAK statement is encountered inside the loop, the loop is exited at that point, as if the SQL command generated no more rows.

CAVEATS
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.