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

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, 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 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=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 the $null value for this statement. Normally taken from the $null variable; this option overrides it without disturbing it. Added in version 3.01.966300000 20000814. (See Parameter Substitution, here). To set the null value to no-values, use NONULL.

  • NONULL Set the $null value to empty (no values). Normally taken from the $null variable; this option overrides it without disturbing it. 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.



Copyright © Thunderstone Software     Last updated: Dec 10 2018
Copyright © 2019 Thunderstone Software LLC. All rights reserved.