Arnica Web SQL Filter standard specifies the structure of SQL filter (SQL WHERE clause), reformatted to avoid excessive encoding when the filter is passed with web request either as HTTP query string parameter, or a value of HTML form parameter, or a value of XML or JSON element. The filter is then parsed to the server to reconstruct WHERE clause for a specific database.
The filter consists of individual expressions connected with Boolean operators. Each expression is enclosed in curly parentheses, e.g.:
({Expression1} AND {Expression2}) OR {Expression3}
Each expression may consist of two or three parts:
{Column Operator Value}
For example:
({ProductName EQ pen} AND {StockQuantity GE 20} AND {CreationDateStamp GE 20110101000000}) AND {ProductID NOTNULL}
The Value part of the expression may or may not be included, depending on the Operator. For example, operators NULL and NOTNULL do not require value. If used, the value must be in Arnica WebFramework internal character format, i.e. Boolean values are presented as 1 or 0, datetime values are presented as character with the following pattern: YYYYMMDDHHMMSS or yyyy-mm-ddThh:mm:ss. Values should not be included in quotes or double-quotes or other delimiters.
Expression part part ColumnName represents the name of the physical column, against which the filter is applied.
Expression part Operator represents the operator, connecting the column and the value. Possible operators are:
Filter Operator | SQL Operator | Applicable to Data Types |
EQ | = | Numeric, String, Text, Boolean, Datetime |
GE | >= | Numeric |
GT | > | Numeric |
INCLUDE | LIKE %value% | String, Text |
INLIST |
| Numeric, String, Text, |
LE | <= | Numeric |
LIKE | LIKE value | String, Text |
LT | < | Numeric |
NE | <> | Numeric, String, Text, Boolean, Datetime |
NOTNULL | IS NOT NULL | Numeric, String, Text, Boolean, Datetime |
NULL | IS NULL | Numeric, String, Text, Boolean, Datetime |
START | LIKE value% | String, Text |