Some ODBC Applications May Not Be Able to Handle Special Characters in Table/Column Names

Resolving The Problem

When using ODBC, it is important to be aware that operating system identifiers (table names, column names, and so on) allow special characters that ODBC and common implementations of the SQL specification do not. Using identifiers with these characters may lead to unpredictable results in many PC applications. To circumvent the problem either use the recommended SQL naming convention for table and field names or consider creating an SQL ALIAS over the target table and use a standard name for the alias.

Background

The ODBC specification (and the SQL specification) states that names must be in the format of " letter[digit | letter | _]...". The only special character allowed is an underscore.

To improve backwards support for files using older naming conventions (such as System/36 and System/38), the System i™ system extended the SQL syntax to include other characters such as percent (%), ampersand (&), period (.), at ('@'), pound ('#'), forward slash ('/'), backslash ('\'), EBCDIC "not" (which has no matching ASCII character), and so on. Note that the DB2® Universal Database for iSeries SQL Reference recommends that these characters not be used:

"$, @, #, and all other variant characters should not be used in identifiers because the code points used to represent them vary depending on the CCSID of the string in which they are contained. If they are used, unpredictable results may occur. "

Because many OS/400 files use this older naming convention, Client Access ODBC extended the ODBC specification (through Client Access V3R1M1 APAR SA50965) to allow three extra characters: $, #, and @.

Special Characters Used with Microsoft Office

The special naming allowed on the System i system may cause problems with SQL-based applications such as ODBC. This can be confusing because, even within the Microsoft Office suite of products, different products produce SQL statements in different ways. Some products will handle illegal characters in table/column names while others will not. In particular, if a period is used in a name, then the application must use "delimited identifiers".

Microsoft Query and Microsoft Access can be used as an example. For this example we will try to access an operating system database file called N.FILE which contains two fields, FIRSTNAME and LASTNAME.

Microsoft Query will generate the following SQL Statement:

SELECT N.FIRSTNAME, N.LASTNAME FROM LIBNAME.N.FILE

MS Access will generate the following SQL Statement:

SELECT "FIRSTNAME","LASTNAME" FROM "LIBNAME"."N.FILE"

The statement generated by Microsoft Query will fail with an SQL5001, Column qualifier or table N undefined. (-5001). Microsoft Access handles the illegal character in the file name because Microsoft Access double quotes library names, table names, and column names. Since the names are double quoted (a delimited identifier), the actual characters in the names are not 'looked at'. Microsoft Query does not double quote the names (an ordinary identifier), so the query fails.

In both cases, native SQL and ODBC are both working as designed. The failure is expected. Microsoft Access succeeds where an application would normally fail because it was 'cautious' enough to double quote all names.

Tip for Microsoft Excel and Query Users

To query a table name that contains a period, use the SQL button in the Microsoft Query toolbar to manually type the SQL statement. This allows you to qualify the table name with double quotes. Other options include using other query tools such as Client Access data transfer or creating an SQL alias over the table.

While this document is written for Client Access Express, the same rules apply to all Client Access products.