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 Microsoft SQL Server 6.0 Guide 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:

Refer to Portability Considerations in The Repository and  Portability Considerations in The Database 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: