Basic SQL keywords

The following keywords are supported by the DBMS. They are not case sensitive.

select-statement : SELECT select-list FROM table-name [ WHERE search-condition ] [ ORDER BY sort-order ]

The RDbView class uses a select-statement to specify what data should be present in the Rowset, and how to present it. It is passed as a parameter to the RDbView::Prepare() function, wrapped in a TDbQuery object.

select-list : * | column-name-comma-list

Specifying * requests that all columns for the table be returned in the Rowset, in an undefined order; otherwise a comma separated list specifies which columns to return, and the order that the columns appear in the Rowset.

table-name column-name

The table-name should be a table which exists in the database. Column names should refer to columns which exist in the specified table.

search-condition : boolean-term [ OR search-condition ] boolean-term : boolean-factor [ AND boolean-term ] boolean-factor : [ NOT ] boolean-primary boolean-primary : predicate | ( search-condition )

This specifies a condition which a row must meet to be present in the generated Rowset. A trivial search condition is just a single predicate, more complex search conditions are constructed by combining predicates using the keywords AND, OR and NOT, and using parentheses to override the standard precedence of these operators. Without brackets, the order of precedence is NOT, AND then OR. e.g.

a=1 or not b=2 and c=3

is equivalent to

(a=1 or ((not b=2) and c=3))

A search-condition is also used by RDbRowSet::FindL() and RDbRowConstraint::Open(), specifying which rows of the Rowset will be returned by RDbRowSet::FindL() or matched by RDbRowSet::MatchL().

predicate : comparison-predicate | like-predicate | null-predicate

These are the building blocks of the search condition. Each predicate tests one condition of a column in the selected table.

Comparison predicate

comparison-predicate : column-name comparison-operator literal comparison-operator : < | > | <= | >= | = | <>

Compare a column value with a supplied literal value. Numeric columns (including bit columns) are compared numerically, text columns are compared lexically (see TDbTextComparison) and date columns are compared historically. Binary columns cannot be compared. The literal must be of the same type (numeric, string, date) as the column.

Literals

literal : string-literal numeric-literal date-literal

A string-literal is a character string enclosed in single quote characters '. To include a single literal quote character ' in a string-literal, use two literal quote characters ''.

A numeric-literal is any sequence of characters which can be interpreted by TLex::Val() as a valid decimal integral or floating point number.

A date-literal is a character string enclosed by the # character, which can be interpreted by TTime::Parse() as a valid date.

Like predicate

like-predicate : column-name [ NOT ] LIKE pattern-value pattern-value : string-literal

Test whether or not a text column matches a pattern string. The wildcard characters used in the pattern-value are not standard SQL, instead the wildcard characters are used: ? for matching any single character and * for matching zero or more characters. See the description of the TDesC::Match() function.

Null predicate

null-predicate : column-name IS [ NOT ] NULL

Test whether or not a column is Null. This predicate can be applied to all column types.

sort-order : sort-specification-comma-list sort-specification : column-name [ ASC | DESC ]

Without an ORDER BY clause in the select statement the order that rows are presented is undefined. The columns specified in the sort-order can be ordered in ascending (the default) or descending order, and should appear in the sort-order in decreasing order of precedence. e.g.

surname, first_name

will order the rows by the column surname, and any rows with identical surnames will then be ordered by the column first_name.