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. Unlike previous version of ColdFusion, which used ODBC, OLEDB, and native drivers to connect to databases, ColdFusion MX handles all database connections via JDBC (Java Database Connectivity). There are currently four distinct types of JDBC drivers, appropriately named type 1, 2, 3, and 4. Each JDBC type works in a slightly different way:
A JDBC call is converted to a client-specific API call.
A JDBC call is first translated to an independent net protocol, then to a database-specific call.
A JDBC call is converted to the network protocol used by the specific database.
ColdFusion MX comes with JDBC drivers for connecting to several popular databases. Depending on the version of ColdFusion MX you have, drivers may include MS Access, MS SQL Server, Oracle, various flavors of DB2, Informix, MySQL, and Sybase. Additionally, you may connect to almost any other database for which you have a third-party JDBC driver or an ODBC driver (through a JDBC Type 3 socket connection). ColdFusion MX ships with Type 2, Type 3, and Type 4 JDBC drivers. For more information on JDBC driver types, see the book Administering and Configuring ColdFusion MX that comes with the ColdFusion documentation, or visit Sun’s web site at http://java.sun.com/products/jdbc/driverdesc.html.
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 Administering and Configuring ColdFusion MX book (part of the ColdFusion documentation) for your edition of ColdFusion.
Open the ColdFusion Administrator in your browser.
Click on the Datasources link under the Data & Services section.
Enter a name for your data source. The name you choose is up to you, but it should be something meaningful. Names can only contain letters, numbers, underscores, Unicode currency symbols, and spaces. 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 a connection via a third party JDBC driver, choose Other. If you want to setup a connection through an ODBC driver, choose ODBC Socket.
After you have provided a name for the data source and chosen the driver/connection type, click the Add button. This takes you to a driver-specific page for the driver or connection type you chose.
Depending on the driver/connection type you chose, this 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), username, password, and server/port (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 Administering and Configuring ColdFusion MX book.
After you have finished entering the basic setup information, click on the Show Advanced 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 MX should access the database and the actions it should be allowed to perform.
Once you have finished configuring any advanced settings, click the Submit button in the middle 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 “OK” 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 Macromedia Servers Knowledge Base TechNotes that may help you troubleshoot the problem. These can be found at http://www.macromedia.com/v1/support/knowledgebase/searchform.cfm.
 If you are adding a data source for a Microsoft Access database, and you wish to be able to retrieve data from Memo fields, you’ll need to check the box labeled “Enable long text retrieval (CLOB)”. This box is unchecked by default.