ODBC Naming Conventions  
 
 

The formatting of table and field names varies considerably across data sources. As a working standard for data sources, ODBC defines a naming convention that developers can follow in database construction. Adhering to the standard makes your code more portable, giving you increased deployment flexibility and significantly decreasing the cost of scaling applications to higher-performance database systems.

The standard requires that names begin with a letter and consist only of letters, numbers, and the underscore character. ColdFusion uses the same standard for identifying query columns. Therefore, the tables and queries you use with ODBC and ColdFusion must also adhere to this standard.

Although ODBC allows spaces in table names, ColdFusion does not. You must use aliases to access tables and field names that contain spaces. In addition, some data sources require you to specify table owner and table qualifier information to access the data source.

It is possible to sidestep the ODBC/ColdFusion naming requirements by enclosing references to nonstandard names with the backquote ( ` ) character and by using the SQL "AS" keyword to alias nonstandard names into standard names. However, these techniques are burdensome and lead to the creation of non-portable code that is significantly less readable.

 
 
  Table owners and qualifiers  
 

The ODBC data sources for which ColdFusion bundles desktop drivers generally allow you to access their tables by simply naming them in the SQL statement. A principal exception is SQL Server, which requires extended information.

The following statement is intended to return all the records from a SQL Server table called Orders. To accomplish this, the table name must be preceded by the database name (orderdb) and the database owner (dbo).

SELECT *
    FROM orderdb.dbo.order

Other client/server databases like Oracle and Sybase have their own requirements. Check the product documentation for the correct qualifiers.

The Microsoft Excel driver requires the backquote (`) and dollar sign ($) to qualify a worksheet name in a statement. The example below returns all the rows from a worksheet called "sheet1" in an Excel workbook that has been added as an ODBC data source.

SELECT *
    FROM `sheet1$`


 
 
BackUp LevelNext
 
 

allaire     AllaireDoc@allaire.com
    Copyright © 1998, Allaire Corporation. All rights reserved.