Many people begin their relationship with ColdFusion out of a need to provide web-based access to data stored in a database. This might be as simple as wanting to output the contents of a single database table as an HTML table or as sophisticated as a multipage report generated from several related tables in a database. Whatever your requirements, the methods for querying the data from the database and outputting the results to a user’s web browser using ColdFusion remain the same.
ColdFusion doesn’t stop with allowing you to query data from a database. Using ColdFusion, you can perform a wide range of database operations including adding, updating, and deleting records; adding new columns to existing tables; and creating, altering, and dropping existing tables. In this chapter, we cover the basics you need to know in order to use ColdFusion to interact with a database. Included in the discussion are configuring data sources, an introduction to SQL, and techniques for retrieving and displaying data.
In ColdFusion, the term data source refers to a connection between ColdFusion and an information source, such as a database, LDAP server, flat file, etc. This chapter focuses on connecting with one specific type of data source: databases. Before you can use ColdFusion to interact with a database, the database has to be set up as a data source that the ColdFusion server can recognize. There are three types of database connections supported by ColdFusion:
- ODBC
Open Database Connectivity (ODBC) is arguably the most common format for accessing databases in both client-server and Internet-based applications. ODBC drivers are available for virtually every RDBMS in existence. If you already have ODBC data sources set up on the system you installed ColdFusion on, they are automatically available for use by ColdFusion. Depending on the platform you are running ColdFusion on, any number of ODBC drivers may be installed by default. For specifics, consult the documentation for your particular flavor of ColdFusion.
- OLE DB
OLE DB is a relatively new driver technology from Microsoft designed to replace ODBC as the preferred method for accessing a variety of data sources. OLE DB is available only with the Windows version of ColdFusion and requires a special set of drivers (called providers) to implement. ColdFusion comes with OLE DB providers for accessing MS Access and MS SQL Server. Other third-party OLE DB drivers are available from a number of vendors.
- Native drivers
The Enterprise version of ColdFusion comes with native driver support for DB2 5.0 and 6.1, Informix 7.x and 9.x, Oracle 7.3, 8.0, and 8i, and Sybase System 11 and 12 databases. Native drivers sometimes offer better performance than ODBC drivers and often include support for features not implemented in the ODBC version of the driver. In order to use a native driver, you must have client software from the particular database vendor installed on your ColdFusion server.
Regardless of the method you choose for connecting your data source to ColdFusion, one thing remains the same. You must register the data source with the ColdFusion Administrator in order to take advantage of ColdFusion’s database management and security features. Registering the data-source name also tells ColdFusion which database to associate with a particular data-source name.
You should note that not all databases and connection methods are supported across all editions of ColdFusion. For the most up-to-date listing of supported database drivers, see the Advanced ColdFusion Server Administration book (part of the ColdFusion documentation) for your edition of ColdFusion.
To add a new data source via the ColdFusion Administrator, follow these steps:
Open the ColdFusion Administrator in your browser.
Click on the ODBC, OLEDB, or Native Drivers link under the Data Sources section to set up the appropriate type of connection for your data source.
Enter a name for your data source. The name you choose is up to you, but it should be something meaningful. Names must begin with a letter and can contain only letters, numbers, and the underscore ( _ ) character. Be sure not to use any ColdFusion tag, function, variable name, or other reserved word as your data-source name.
Choose your driver/provider type from the drop-down list of available types. If you are attempting to set up an ODBC connection for certain database types, such as DB2, you may not be able to add the data source through the ColdFusion Administrator. In such a case, you have to add the data source at the operating-system level and then use the ColdFusion Administrator to set any ColdFusion-specific parameters.
After you have provided a name for the data source and chosen the driver/provider type, click the Add button. This takes you to the Create Data Source page for the particular driver/provider you chose.
Depending on the driver/provider you chose, the Create Data Source page lets you enter information about the data source you want to add such as its name, location (if it is a file-based database such as MS Access), and hostname or IP address (if it is an enterprise-level database such as SQL Server, Oracle, or DB2). For more information on various configuration options, see the Advanced ColdFusion Server Administration book.
After you have finished entering the basic setup information, click on the CF Settings button in the lower right corner of the page. This takes you to a new page where you can enter information about how ColdFusion should access the database and the actions it should be allowed to perform.
Once you have finished configuring any ColdFusion specific settings, click the Create button at the bottom of the page. ColdFusion will attempt to register your data source and verify the connectivity. If verification fails, a message that lists likely causes for the problem is displayed. If you can connect to the data source successfully, you will be returned to the main data source configuration page. You should see the word “Verified” to the right of the data source you just created (under the Status column).
Once you have successfully created and verified the connection to your data source, you are ready to begin using it in your ColdFusion applications.
In the event you run into trouble trying to get a data source setup, consult the documentation that came with ColdFusion. Additionally, there are a number of Allaire Knowledge Base articles that may help you troubleshoot the problem. These can be found at http://www.allaire.com/support/knowledgebase/SearchForm.cfm. Some of the more common issues are included in the following list:
- Article 1397
“Troubleshooting Data Sources/Database Connectivity in Windows NT”
- Article 11328
“Troubleshooting Data Sources/Database Connectivity in Solaris”
- Article 14632
“Configuring OLE DB Data Sources in ColdFusion”
- Article 11426
“Solaris: How to Connect to SQLANYwhere
- Article 13104
“Solaris: Native Connection to Oracle 8i with CF 4.01”
- Article 16301
“Sybase OpenClient Problems with ColdFusion 4.5.1 and Redhat Linux”
- Article 16050
“Unknown Connection Error When Connecting to mySQL Databases Using myODBC on ColdFusion 4.5.x for Solaris”
- Article 4008
“Accessing a MS Access (or Other File System) Database on a Remote WIN32 System”
- Article 1150
“Connecting to Network Data Sources from Stand-alone Servers”
Get Programming ColdFusion now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
