Chapter 3: Make APPX Files Available to SQL
SQL Name Rules and Reserved Words
A table, column or view name can be written plain, the way ASQL writes them, or inside double quotes.
Plain names:
- start with a letter or an underscore,
- contain only letters, digits and underscores,
- are at most 32 characters long,
- can't be one of the reserved words listed below, and
- ignore capitals:
Region_Labelandregion_labelare the same column.
This edition's driver reports 64 (SQL_MAX_IDENTIFIER_LEN), but the gateway refuses a longer name with -20248 Identifier too long., quoted or not. See What Works Better in Later Releases for a later configuration with longer names.
These names were tried on this edition's gateway:
| Name | Result |
|---|---|
name_2, _name, RegionLabel | Accepted. |
2name | -20003 Syntax error. "2name" works. |
region-label, region label | -20003 Syntax error. Both work in double quotes. |
A name containing $, #, @ or . | -20003 Syntax error. It works in double quotes. |
| A name of 33 characters or more | -20248 Identifier too long. |
A table name that starts with a digit, such as 0SA_DMO_FILE | -20003 Syntax error. It works in double quotes. |
A name in double quotes can use any characters and can be a reserved word, but it keeps its exact capitals, and every statement has to quote it the same way. A column created as "suffix" is found by "suffix" but not by "SUFFIX", which fails with -20006 Column not found/specified. A plain name is read in lower case, so quote in lower case, the way ASQL writes names. Renaming a column is usually simpler than quoting it; see Rename a column before it is created.
Reserved words
The gateway refuses two kinds of words as plain names, and they behave differently:
- Refused everywhere, with
-20003 Syntax error. These are part of SQL itself, such as NUMBER, TYPE, DATE, ORDER and FILE. A column with one of these names has to be in double quotes in every statement that uses it, including every query, report and spreadsheet. Rename the column instead. - Refused only as a new name, with
-20049 Keyword used for a name. These fail where a table or view is created, but queries can use them without quotes. Quoting the name once in theCREATE TABLEstatement, in lower case, is enough.
This edition's gateway uses the Dharma 14.01 SQL server. It accepts most SQL function names, such as SUFFIX, PREFIX and LENGTH, which the 5.5.1 and 5.5.2 gateways (Dharma 9.01) refuse. It also refuses a few words those gateways accept, such as DAY, BOTH, LEADING and TRAILING. If you are moving from 5.5.1 or 5.5.2, check your column names against the list below: a column named DAY, for example, now has to be quoted in every query.
ASQL's Reserved Keywords list, shown in expert mode, is only a guide. It combines the ODBC standard's keyword list, ASQL's own list and the driver's list, so it includes many words this gateway accepts, such as SUFFIX, PREFIX and LENGTH. The tables below show what this edition's gateway refused when each word was tried as a column name.
Refused everywhere: 224 words. Without double quotes these fail in any statement, queries included, with -20003 Syntax error.
| Starts with | Words |
|---|---|
| A | ADD, AFTER, ALL, ALTER, AND, ANY, AS, ASC, AVG |
| B | BEFORE, BETWEEN, BIGINT, BINARY, BIT, BOTH, BY |
| C | CALL, CASCADE, CASE, CHECK, CLOSE, CLUSTERED, COALESCE, COLGROUP, COLLATE, COLUMN, COMMIT, COMPRESS, CONNECT, CONSTRAINT, CONTAINS, CONVERT, COUNT, CREATE, CROSS, CURRENT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, CURSOR |
| D | DATABASE, DATAPAGES, DATE, DAY, DAYOFYEAR, DBA, DEC, DECIMAL, DECLARE, DEFAULT, DEFINITION, DELETE, DESC, DISTINCT, DOUBLE, DROP |
| E | EACH, ELSE, END, ESCAPE, EXCLUSIVE, EXECUTE, EXISTS |
| F | FETCH, FILE, FLOAT, FOR, FOREIGN, FROM, FULL |
| G | GRANT, GROUP |
| H | HASH, HAVING, HOUR |
| I | IDENTIFIED, IDENTITY, IN, INDEX, INDEXPAGES, INDICATOR, INNER, INOUT, INT, INTEGER, INTERSECT, INTO, IS |
| J | JOIN |
| K | KEY |
| L | LEADING, LEFT, LIKE, LINK, LOCK, LONG, LVARBINARY, LVARCHAR |
| M | MAX, METADATA_ONLY, MIN, MINUS, MINUTE, MODE, MODIFY, MONEY, MONTH |
| N | NATIONAL, NATURAL, NCHAR, NEWROW, NOCOMPRESS, NOT, NOWAIT, NULL, NULLIF, NUMBER, NUMERIC |
| O | ODBC_CONVERT, OF, OLDROW, ON, OPEN, OPTION, OR, ORDER, OUT, OUTER |
| P | PCTFREE, PRECISION, PRIMARY, PRIVILEGES, PROCEDURE, PUBLIC |
| Q | QUARTER |
| R | RAW, REAL, REFERENCES, REFERENCING, RENAME, RESOURCE, RESTRICT, RESULT, RETURN, REVOKE, RIGHT, ROLLBACK, ROW, ROWID, ROWNUM |
| S | SECOND, SELECT, SESSION_USER, SET, SHARE, SIZE, SMALLINT, SOME, SQL_BIGINT, SQL_BINARY, SQL_BIT, SQL_CHAR, SQL_DATE, SQL_DECIMAL, SQL_DOUBLE, SQL_FLOAT, SQL_INTEGER, SQL_LONGVARBINARY, SQL_LONGVARCHAR, SQL_NUMERIC, SQL_REAL, SQL_SMALLINT, SQL_TIME, SQL_TIMESTAMP, SQL_TINYINT, SQL_TSI_DAY, SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_MINUTE, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_SECOND, SQL_TSI_WEEK, SQL_TSI_YEAR, SQL_VARBINARY, SQL_VARCHAR, STATEMENT, STATISTICS, STORAGE_ATTRIBUTES, STORAGE_MANAGER, STORE_IN_DHARMA, SUM, SYNONYM, SYSDATE, SYSTIME, SYSTIMESTAMP |
| T | TABLE, THEN, TIME, TIMESTAMP, TINYINT, TO, TRAILING, TRANSACTION, TRIGGER, TYPE |
| U | UID, UNION, UNIQUE, UPDATE, USER, USING |
| V | VALUES, VARBINARY, VARCHAR, VARYING, VIEW |
| W | WEEK, WHEN, WHERE, WITH, WORK |
| Y | YEAR |
Refused only as a new name: 44 words. Without double quotes these fail when a table or view is created, with -20049 Keyword used for a name. Queries can use them without quotes.
| Starts with | Words |
|---|---|
| A | A, AN, ARRAY |
| B | BEGIN, BIND, BINDING |
| C | CLEANUP, COMPLEX, CONTINUE, CVAR |
| D | DECLARATION, DESCRIBE, DESCRIPTOR, DHTYPE |
| E | EXEC, EXIT, EXPLICIT |
| F | FIELD, FOUND |
| G | GO, GOTO |
| I | IMMEDIATE, INPUT, INTERFACE |
| L | LIST |
| M | MAIN |
| O | OUTPUT |
| P | PREPARE |
| R | RECORD |
| S | SECTION, SERVICE, SHORT, SQL, SQLERROR, SQLWARNING, START, STOP |
| T | TIMEOUT, TPE |
| U | UNSIGNED, UUID |
| V | VARIABLES, VERSION |
| W | WHENEVER |