$loop variable is set in a looping
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
$next variable is also set at every iteration (when
looping): it is the number of
SKIPped rows plus
plus 1. This is a convenient way to number hits counting from 1, and
consistently across result pages with varying
SQL loop ends,
$next is set to the number of
SKIPped rows plus the number of iterated rows: the value to use
SKIP in the next
SQL statement, for the next result
Note that in version 7 and later, 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
$indexcount variable is set to an estimate of
how many total rows (i.e. as if no
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
BREAK). However, since
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
$loop. It is also not affected by the
<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:
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.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
$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
$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.
$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.returnedmax variables are set, similarly to
$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
Also in version 6 and later,
$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
used to compute the total hits for the ... of N hits message.
Also in version 6 and later,
$sqlresult.indexcount is set,
$indexcount, but is -1 (not 0) when unknown, to
distinguish from 0 (known but no results).
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
greater than or equal to 0, the function can be considered successful.
This is 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;
SQL_SUCCESS for 0 (success),
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;
$ret.token should be checked
<$query = "John">
<TR><TH> Name </TH> <TH> Address </TH> <TH> Phone </TH></TR>
<SQL "select Name, Address, Phone
where Name like $query">
<TD> $Name </TD>
<TD> $Address </TD>
<TD> $Phone </TD>
There are $loop matching customers.
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,
where clauses, etc. In particular, the SQL
command name (e.g.
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
$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
All other variables in the SQL command (e.g. field values for
INSERT) must be set.
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
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
SELECT statements: looking ahead can cause more than the
desired rows to be deleted/inserted/updated, so the look-ahead is not
In version 8 and later, the default behavior of $null changed; see above (here).
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.