Advanced ColdFusion Administration
Advanced Data Source Management

About ColdFusion database drivers

ColdFusion uses ODBC, OLE DB, and native database drivers. For detailed information about ODBC drivers, see Installing and Configuring ColdFusion Server

About OLE DB

OLE DB is a Microsoft specification for a set of interfaces designed to access data. Although ODBC is primarily used to access SQL data in a platform-independent manner, OLE DB is designed to access SQL and non-SQL data in an OLE Component Object Model (COM) environment.

ColdFusion developers can access a range of data stores through Microsoft OLE DB, including:

• MAPI-based data stores such as Microsoft Exchange and Lotus Mail
• Nonrelational data stores, such as Lotus Notes
• LDAP 2.0 data
• Data from OLE applications like word processors and spreadsheets
• Mainframe data
• HTML and text files, flat-file data

For more information, including a list of provider vendors, visit the Microsoft OLE DB site at http://www.microsoft.com/data/oledb/.

About OLE DB providers

Before ColdFusion can use OLE DB to access data stores, you must install an OLE DB provider, available from third-party vendors. The provider software handles data processing in response to requests from the OLE DB consumer, which in this case is ColdFusion.

ColdFusion uses an OLE DB provider to access an OLE DB data source. An OLE DB provider is a COM component that accepts calls to the OLE DB Application Programming Interface (API) and processes that request against the data source.

You can often achieve sultry performance levels by running an OLE DB provider, instead of an ODBC driver, to process SQL. This depends on how the provider implements the data call. Some providers route OLE DB calls through the ODBC Driver Manager, while others go directly to the database. Providers that go directly to the database are akin to native drivers in providing an alternative to ODBC. Providers are available for all the major relational DBMS products as well as the data stores previously mentioned.

Installing the OLE DB provider

Before you configure an OLE DB data source, you must have installed a recent version of the Microsoft Data Access Components (MDAC). MDAC includes two OLE DB providers-SQLOLEDB and MSDASQL. For Access databases, Microsoft makes available a Jet provider. For SQL Server, Microsoft offers MSDASQL and SQLOLEDB providers.

During its installation process, ColdFusion attempts to detect the MDAC version on your computer. If MDAC is absent or the identified version is 2.0 or earlier, ColdFusion installs MDAC version 2.5 and restarts the installation process. If you install MDAC on a Windows NT system, you get the MSDASQL and SQLOLEDB providers.

For updated versions of MDAC, visit the Microsoft Universal Data Access Download Page at http://www.microsoft.com/data/download.htm/.

You should be aware of the following characteristics in how ColdFusion handles OLE DB:

• The initial driver drop-down list box does not display all of the installed OLE DB providers. If you are creating a data source using a provider other than SQLOLEDB or Jet, such as MSDASQL or a MERANT OLE DB driver, you must select other from the drop-down list box.
• No matter which provider you select from the drop-down list box, you must still retype its name in the Provider field.
• When using MSDASQL, you must have an ODBC data source already defined for the database. Enter this ODBC DSN in the ProviderDSN text box.

The following procedure describes how to configure an OLE DB data source to a Microsoft SQL Server database on Windows NT, using SQLOLEDB as the provider.

To configure an OLE DB data source:

1. Open the ColdFusion Administrator.
2. Under Data Sources, click OLE DB.

   The OLE DB Data Sources page displays any existing OLE DB Data Source Names that are available to ColdFusion:

   

3. Enter a name for the new data source and select an OLE DB Provider from the drop-down list.
   

   Note Do not name a ColdFusion data source Registry or Cookie, as these words are reserved for use by ColdFusion.

   
   

   

4. Click Add.

   The Create OLE DB Interface Data Source page displays:

   

5. (Optional) Enter a description.
6. Enter the following connection information:
   • If SQLOLEDB is the provider  Enter SQLOLEDB as the Provider, specify the Server that hosts the database, and specify the name of the Default Database.

     

   
   

   Note For the Server field, if the database is a local SQL Server database, enclose the word local in parentheses: (local).

   
   
   • If Microsoft Jet is the provider  Enter Microsoft.Jet.versionnumber as the Provider (such as Microsoft.Jet.OLEDB.4.0), and specify the path to the Database File.

     

   • If you are using another provider  Enter its name as the Provider. Be aware that MSDASQL requires a predefined ODBC data source for the database to which you will connect. Enter the name of the ODBC data source in the Provider DSN field.

     

7. Click CF Settings and specify any ColdFusion-specific settings. For example, enter a username and password if required for the data source.

   

   
   

   Note The omission of required username and password information is a common reason why a data source fails to verify.

   
   
8. Click Create to create the new data source.

   ColdFusion automatically verifies that it can connect to the data source.

   

   If ColdFusion cannot verify the data source, the Status displays as Failed. You can run a cfquery against the failed data source to get more detailed information about the problem. You also can try embedding a username and password into the cfquery tag to see if the query works.

   If you are creating a UNIX data source, you might need to set environment variables for your database client library by editing the ColdFusion start script in <installdir>/coldfusion/bin. For detailed information about editing the ColdFusion start script for your particular database, see the section about your database.

About native drivers

The Enterprise Edition of ColdFusion Server includes support for DB2, Informix, Sybase System 11 through Sybase Adaptive Server 12.0, and Oracle 7.3.4, 8.0, and 8i databases through native database drivers on both Windows NT and UNIX platforms.

You might consider using native database drivers for the following reasons:

• Native drivers tend to offer better performance than their ODBC counterparts.
• Some stored procedure functionality is only available through native drivers. For example, you must use an Oracle native driver to use packages.

Software requirements for native drivers

Before you can use the ColdFusion native database drivers, you must install additional client software. Also, you must install the database client software and ColdFusion Server software on the same server.

The following table describes requirements for each database and each supported platform:

Database	Client Software	For more information
Oracle	Oracle 7.3.4, Oracle 8.0.x or Oracle 8.1.6 or higher	Installing and Configuring ColdFusion Server
Sybase	Sybase Open/Client 11.1.1, 11.9.2 or 12.0	"Connecting to Sybase Databases"
Informix	Informix 2.50 SDK or higher	"Connecting to Informix Databases"
IBM DB2	IBM DB2 Client Application Enabler version 5 or 6	"Connecting to DB2 Databases"

Copyright © 2001, Macromedia Inc. All rights reserved.

---