sqltest: Formats SQL condition tests

The sqltest tag inserts a condition test into SQL code. It tests a column against a variable. This tag is used in SQL Methods.

Syntax

sqltest tag syntax:

      <dtml-sqltest Variable|expr="VariableExpression">

The sqltest tag is a singleton. It inserts a SQL condition test statement. It is used to build SQL queries. The sqltest tag correctly escapes the inserted variable. The named variable or variable expression is tested against a SQL column using the specified comparison operation.

Attributes

type=string
The type of the variable. Valid types include: string, int, float and nb. nb means non-blank string, and should be used instead of string unless you want to test for blank values. The type attribute is required and is used to properly escape inserted variable.
column=string
The name of the SQL column to test against. This attribute defaults to the variable name.
multiple=boolean
If true, then the variable may be a sequence of values to test the column against.
optional=boolean
If true, then the test is optional and will not be rendered if the variable is empty or non-existent.
op=string
The comparison operation. Valid comparisons include:
eq
equal to
gt
greater than
lt
less than
ne
not equal to
ge
greater than or equal to
le
less than or equal to

The comparison defaults to equal to. If the comparison is not recognized it is used anyway. Thus you can use comparisons such as like.

Examples

Basic usage:

      select * from employees
        where <dtml-sqltest name type="nb">

If the name variable is Bob then this renders:

      select * from employees
        where name = 'Bob'

Multiple values:

      select * from employees
        where <dtml-sqltest empid type=int multiple>

If the empid variable is (12,14,17) then this renders:

      select * from employees
        where empid in (12, 14, 17)

See Also

sqlgroup tag

sqlvar tag