SQL - execute SQL statement

SYNOPSIS

<SQL [options] "SQL command" [...]>
  ... 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 p. ), 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.

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
  Don't 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.

• 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 (p. ). 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, p. ). Added in version 3.01.966300000 20000814.

• USER=user
  Set the username to access the database as. Normally taken from the <USER> statement value (p. ); 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 (p. ); 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, p. ). 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, p. ).

• 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 within a results top-level element. Added in version 4.00.1001000000 20010920.

  • 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:

  • :noutf8
    Do not translate data from ISO-8859-1 to UTF-8; assume data is already UTF-8.

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

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

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.

• Parameter Substitution
• SQL Command Construction
• Multi-value Variables
• Complex Field Names
• Special SQL Variables