Special SQL Variables

The $loop variable is set in a looping (or syntax version 8 self-closed) SQL statement as it is in a LOOP, though any SKIPped rows are not counted in it (i.e. it starts at 0 regardless of `SKIP`). At the end of the loop it is thus the number of rows returned by the SQL statement.

The $next variable is also set at every iteration (when looping, or syntax version 8 self-closing): it is the number of SKIPped rows plus $loop plus 1. This is a convenient way to number hits counting from 1, and consistently across result pages with varying SKIP values. When the SQL loop ends, $next is set to the number of SKIPped rows plus the number of iterated rows: the value to use for SKIP in the next SQL statement, for the next result page.

Note that in version 7, when the statement is self-closing and thus not looping, $loop and $next are unmodified.

When the SQL statement is first executed, at the start of the first iteration, the $indexcount variable is set to an estimate of how many total rows (i.e. as if no SKIP or MAX) the query will return. Its value is the number of matching rows found in the index(es) used by the query (if determinable). It is only an estimate, since further processing may eliminate some rows from the final result set. The last value of $next, in contrast, is always an exact row count (provided the loop was not exited early due to MAX or BREAK). However, since $indexcount is set at the start of the statement, it can be used to quickly indicate the (probable) number of result rows without looping through them all to get $loop. It is also not affected by the MAX value, as $next and $loop are:

<SQL SKIP=$skip MAX=10
     "select Title from books where Title like $query">
  <IF $loop eq 0>  <!-- print this before the first row -->
    First 10 hits out of a possible $indexcount hits:
  </IF>
  $Title
</SQL>

Note that if no indexes exist, none are used by the query, or an index is used but in a way that precludes pre-counting (e.g. in "bubble-up" mode), then $indexcount will be 0. In version 6 and later, the $sqlresult... variables are available, and often more useful than $indexcount; see here.

In versions after 3.0.942800000 19991116, the $rows.min and $rows.max variables provide more information than $indexcount alone. They are set to the minimum and maximum number of total rows the query will return, respectively, or -1 and -2 (i.e. less than 0) if unknown. $rows.max is similar to $indexcount: an upper limit to the result row count (ignoring SKIP and MAX). However, $rows.max is updated once it is possible to reflect the final (post-processed) count, or set to less than 0 if no index/row count information is available. $rows.min is a lower limit, but also contains a one-row look-ahead: if $rows.min is greater than $next at any time (inside the <sql> loop or after the end), then at least one more row is definitely available. This makes it easy to check when to print a "Next page" link.

The $rows.min and $rows.max variables are updated every iteration and at the end of the SQL loop. Once the total count is known exactly, both variables are equal. Until then, they provide a "window" that bounds the result count, which closes as rows are processed until the exact count is known. If the exact count is known initially - as in the case of a fully indexed query - then $rows.min will be equal to $rows.max from the first row onwards. This helps eliminate the guessing game of when $indexcount is accurate or not.

In version 6 and later, the $sqlresult.returnedmin and $sqlresult.returnedmax variables are set, similarly to $rows.min and $rows.max. However, they are often more accurate and consistent, as their information is obtained directly from the Texis SQL engine, and thus are recommended over $rows.min/$rows.max.

Also in version 6 and later, $sqlresult.matchedmin and $sqlresult.matchedmax are set. These are similar to $sqlresult.returned..., but are the min/max total rows matched - i.e. by the WHERE clause, before likeprows, GROUP BY etc. reduce the returned result count. For example, in a LIKEP query, a maximum of 100 top-ranked results are generally returned (the default for likeprows), yet many more results might have actually matched the query. Thus, $sqlresult.returnedmin may not exceed 100, whereas $sqlresult.matchedmin may be much greater. The $sqlresult.returned... variables can thus be used to compute pagination links, and the $sqlresult.matched... variables used to compute the total hits for the ... of N hits message.

Also in version 6 and later, $sqlresult.indexcount is set, similarly to $indexcount, but is -1 (not 0) when unknown, to distinguish from 0 (known but no results).

The $null variable has significance for parameters, as noted above (see Parameter Substitution, here) - but only in version (or compatibilityversion) 7 and earlier (here).

In version (syntaxversion) 8 and later, $ret.code is set (after every iteration and at the end) to an integer code representing the SQL return code. This value is less than 0 if the function failed, 0 if it succeeded, and greater than 0 if it succeeded with additional information or a warning. Thus, if $ret.code is greater than or equal to 0, the function can be considered successful. This can be more reliable than checking $loop, as some successful statements may return no rows. The $ret.token variable is set to a standard SQL_... string token correspoding to the code; e.g. SQL_SUCCESS for 0 (success), SQL_NO_DATA_FOUND for 100 (success but no more results were found); SQL_ERROR for -1 (an error occurred). The variable $ret.msg is set to a human-readable message corresponding to the code. The message may change in a future release, or as multi-language support improves; only $ret.code and/or $ret.token should be checked programmatically.

EXAMPLE

<$query = "John">
<TABLE>
  <TR><TH> Name </TH> <TH> Address </TH> <TH> Phone </TH></TR>
  <SQL "select Name, Address, Phone
        from customer
        where Name like $query">
    <TR>
      <TD> $Name </TD>
      <TD> $Address </TD>
      <TD> $Phone </TD>
    </TR>
  </SQL>
</TABLE>
There are $loop matching customers.

CAVEATS
Multiple-argument SQL commands were added in version 2.1.899200000 19980630. Nestable SQL statements were added in version 2.1.873500000 19980905.

Column variables returned by the SQL command are cleared first before the loop starts, i.e. previous values are lost. However, if no rows are returned by the command, then the variables are not cleared, since it is unknown what variables would be returned.

Since variables in the SQL command become Texis parameters, they are only permitted where parameters are allowed, e.g. field values, arguments to where clauses, etc. In particular, the SQL command name (e.g. select, insert) cannot be a variable. This is for security (see Parameter Substitution, here).

Care must be taken when passing the SQL command as a variable instead of a string literal (see SQL Command Construction, here) that the command cannot be abused by the user, e.g. all user variables should only be SQL parameters.

It is important to note that unset variables (no values) are treated as variables with a single empty-string value ("") for the purposes of SQL parameters and checking against the NULL option.

The $indexcount variable is only an early estimate. If no index can be used to pre-count results, it will be 0. If post-processing of rows is required, it may be an overestimate.

Only variables that are parameters to a WHERE clause can legally be dropped when empty (or matching the NULL option). All other variables in the SQL command (e.g. field values for INSERT) must be set.

While SQL statements may be nested, this is generally not necessary and can degrade performance considerably if misused. A SQL join statement, or SQL statements in series, are usually better options.

Assigning to a returned variable while inside the loop will only modify the current value of the variable.

An exception to the one-row look-ahead nature of $rows.min / $sqlresult....min is non-SELECT statements: looking ahead can cause more than the desired rows to be deleted/inserted/updated, so the look-ahead is not done.

In version 8 and later, the default behavior of $null changed; see above (here).

The syntaxversion pragma (here) also affects this statement: if version 8 or later syntax is enabled, values never accumulate in looping statements, and the corresponding ROW flag is neither needed nor permitted.

SEE ALSO
DB, USER, PASS, SQLCACHE, vxinfo, pagelinks, LOOP, BREAK, TIMPORT