SQL Syntax

[This is preliminary documentation and subject to change.]

The SQL query strings are restricted to the following format.

• To select a group of records.

  SELECT [DISTINCT]{column-list} FROM {table-list} [WHERE {operation-list}] [ORDER BY {column-list}]

• To delete records from a table.

  DELETE FROM {table} [WHERE {operation-list}]

• To modify existing records in a table.

  UPDATE {table-list} SET {column}= {constant} [, {column}= {constant}][, ...] [WHERE {operation-list}]

• To add records to a table.

  INSERT INTO {table} ({column-list}) VALUES ({constant-list}) [TEMPORARY]

• To add a table.

  CREATE TABLE {table} ( {column} [CHAR [( {size} )] | CHARACTER [( {size} )] | LONGCHAR | SHORT | INT | INTEGER | LONG | OBJECT ] [NOT NULL] [TEMPORARY][LOCALIZABLE] [, column...][, ...] PRIMARY KEY column [, column][, ...] )[HOLD]

• To remove a table.

  DROP TABLE {table}

• To add a column.

  ALTER TABLE {table} ADD {column} [CHAR [( {size} )] | CHARACTER [( {size} )] | LONGCHAR | SHORT | INT | INTEGER | LONG | OBJECT ] [NOT NULL] [TEMPORARY][LOCALIZABLE][HOLD]

• To hold and free temporary tables.

  ALTER TABLE {table name} HOLD

  ALTER TABLE {table name} FREE

  The user can use the commands HOLD and FREE to control the life period of a temporary table or a temporary column. The hold count on a table is incremented for every SQL HOLD operation on that table and decremented for every SQL FREE operation on the table. When the last hold count is released on a table, all temporary columns become inaccessible. If all columns are temporary, the table becomes inaccessible.

Optional parameters are shown enclosed in brackets [ ]. When several choices are listed, the optional parameters are separated by a vertical bar.

A {constant} is either a string or an integer. A string must be enclosed in single quote marks 'example'. A {constant-list} is a comma-delimited list of one or more constants.

The LOCALIZABLE option sets a column attribute that indicates the column needs to be localized.

A {column} is a columnar reference to a value in a field of a table.

A {marker} is a parameter reference to a value supplied by a record submitted with the query. It is represented in the SQL statement by a question mark ?. For information regarding the use of parameters, see either the MsiViewExecute function or the Execute method.

A WHERE {operation-list} clause is optional and is a grouping of operations to be used to filter the selection. The operations must be of the following types:

• {column} = {column}
• {column} = | <> | > | < | >= | <= {constant}
• {column} = | <> | > | < | >= | <= {marker}
• {column} is null
• {column} is not null

For string values, only the = or <> operations are possible. Object value comparisons are limited to IS NULL and IS NOT NULL.

Individual operations can be grouped by AND or OR operators. Ordering can be imposed by use of parentheses ( ).

The ORDER BY clause is optional and causes an initial delay during sorting. Ordering by strings will group identical strings together, but it will not alphabetize the strings.

The DISTINCT clause is optional and does not repeat identical records in the returned result set.

A {table-list} is a comma-delimited list of one or more table names referred to as {table} in the join. Table names that clash with SQL keywords must be enclosed in two grave accent marks `` (ASCII value 96). Table names are limited to 31 characters and cannot include a period.

A {column-list} is a comma-delimited list of one or more table columns referred to as {column} selected. Ambiguous columns may be further qualified as {tablename.column}. Column names that clash with SQL keywords must be enclosed in quotation marks " ". If the column is qualified as {tablename.column} and if there is a need to escape the identifier, the table name and the column must be individually escaped as {`tablename`.`column`}. Column names cannot include a period.

The table and column names are case-sensitive, even if ODBC databases are used. However, SQL keywords are not case-sensitive. Only inner joins are supported, specified by a comparison of columns from different tables.

Tables may contain from 1 to 32 columns. Table and column names are not constrained except that they are limited to 31 characters. Columns that are part of the primary key(s) for a table must be defined first in priority order, followed by any non-primary key columns. Persistent columns must be defined before temporary columns. The sort sequence of a text column is undefined, however, identical text values will always group together.

Tables may not contain more than one column of type 'object'.