13.13 SQL/ODBC Grammar: Keyword Conflicts
One of the interesting things about SQL/ODBC grammar is the use of keywords. Keywords are words reserved by the DBMS to define, manipulate and access database objects.
Some examples are the words CREATE, GRANT, TABLE, SELECT, ORDER and BY.
Generally you should avoid using keywords as object names.
For example, naming a table TABLE or a column in a table GRANT may cause you needless problems and complexities in your applications, essentially because the SQL/ODBC command:
SELECT GRANT FROM TABLE
may upset many SQL/ODBC grammar parsers. Strictly speaking you can use a SQL/ODBC grammar format known as double quote delimited strings and do this:
SELECT "GRANT" FROM "TABLE"
However, the whole concept of using DBMS object names that conflict with DBMS keywords should best be avoided because the degree and consistency of support for the double quoted strings facility varies from DBMS to DBMS.
In fact a makes this recommendation:
"Even though it is syntactically possible, using keywords as objects names (with quoted identifiers), it is not recommended."
Visual LANSA has adopted the approach of not using conflicting object name/keyword identifiers by:
- quoting identifiers that may be SQL keywords when generating SQL statements, and where necessary,
- automatically (and transparently) renaming DBMS objects that conflict with DBMS reserved keywords.
Refer to Portability Considerations in and Portability Considerations in for details on when DBMS objects names are converted. Obviously this facility was primarily provided for the upward compatibility of existing applications. Whenever possible you should avoid using field/column, file/table or library/collection names that conflict with DBMS reserved keywords in any new applications that you create.
This means that if you define a physical file (that is, a table) named TABLE, then the following will happen:
- When Visual LANSA creates the table it will automatically rename it to T_BLE so that there is no object name/keyword conflict at the DBMS level.
- The name TABLE can be used throughout LANSA and it will be automatically and transparently converted to T_BLE as required.