Adding Data Sources for ColdFusion

The process for adding a data source in the ColdFusion Administrator is the same, whether you use an ODBC, OLE DB, or native driver. The following procedure adds an ODBC data source that uses a SQL Server driver.

To add a data source to ColdFusion:

Open the ColdFusion Administrator.
Under Data Sources, click a link to specify a type of data source to add:
- ODBC
- OLE DB (available only on Windows NT/2000)
- Native
  The Data Sources page displays Data Source Names (DSNs) of that type that are available to ColdFusion:
Enter a name for the new data source and select a driver from the drop-down list.

Note
Do not name a ColdFusion data source Registry or Cookie; these words are reserved for use by ColdFusion.
Click Add.
The Create Data Source page displays:
Enter the appropriate information about the new data source.
The information that you enter depends on the platform on which you installed ColdFusion and on the driver you specify. For information about options for your driver, see the section for your specific database.

Note
For the Server field, if you connect to a local SQL Server database using the Microsoft SQL Server driver, enclose the word local in parentheses: (local).
(Optional) Click CF Settings and specify ColdFusion-specific settings.

For more information, see "ColdFusion Settings" on page 118.
Click Create to create the data source.
ColdFusion automatically verifies that it can connect to the data source.

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

Note
You may have to supply a username and password for data sources that you want to verify. To define a username and password for a data source, edit the data source properties.

If you are creating a UNIX data source, you may have to set environment variables for your database client library by editing the ColdFusion start script in <installdir>/coldfusion/bin. For more information on editing the ColdFusion start script for your particular database, see Advanced ColdFusion Server Administration.

ColdFusion Settings

To define a number of advanced ODBC and ColdFusion settings in the ColdFusion Administrator, select a data source and click CF Settings . The settings apply to all platforms. The following table describes the ColdFusion data source settings:

Setting

Description

Login Timeout
The number of seconds before ColdFusion times out the database connection login attempt.

Limit Connections
Select this check box, then specify the number of simultaneous connections to allow for the current data source.
Note: If you enable Limit Connections without specifying a limit for simultaneous connections, ColdFusion defaults to unlimited connections.

ColdFusion Login
Enter a username and password for accessing the ODBC data source. Any username and password specified in a cfquery or other data access tag overrides the values specified in the ColdFusion login.
When you create a data source using a native database driver, you use the username and password options to pass login information to the database.

Disable database connections Disables connections to the data source. Use this setting to perform administrative tasks with the database. You cannot establish new connections to a disabled data source. ColdFusion automatically disconnects and cleans up idle connections. ColdFusion notifies any connection currently in use to clean itself up when the current transaction is complete. To restore the connection to the data source, clear this check box.

Maintain database connections ColdFusion establishes a connection to a data source for every operation that requires it. To improve performance by caching the database connection, select this check box.

Connection Timeout
Specify the maximum number of minutes after the database connection is made that you want ColdFusion to cache a connection after it is used.
This is different from the server setting to Limit database connection inactive time, located under Server Settings > Caching. The latter setting is server wide and only releases cached connections that have been inactive (not used) for the specified period of time.
The Connection Timeout setting does not return a connection to the cache after a specified period of time, regardless of how infrequently it is used.
The default is "" or 0, which means the connection timeout is never enforced.

Restrict SQL Operations to
Select SQL operations to restrict for the current data source. To allow all SQL operations, clear all check boxes. To allow specific SQL operations, select the check boxes.

Long Text Retrieval Settings
Retrieves all the data for a long text field. If you use this setting, order the columns in your select statement with the long text fields referenced last.
ColdFusion retrieves the column with the long text field, and subsequent columns in the select statement, as unbound data. Unbound data take longer to retrieve than bound data; therefore, this setting can affect performance.

Long Text Buffer Size
If you do not select the Enable retrieval of long text option, specifies the maximum number of characters to retrieve from a long text field is not set. In this case, ColdFusion binds text columns to a buffer whose size you specify in this field. If you specify a size that is not supported by your database, a SQL execution error occurs when you try to retrieve the data.
If you select the Enable retrieval of long text option, this is the buffer size ColdFusion uses to retrieve the unbound text column until all data is returned.

Connection String
Use this setting to pass driver-specific connection attributes for ODBC data sources. You can:

Specify connection attributes that cannot be defined in the odbc.ini settings

Override odbc.ini settings

Make ODBC connections dynamically when there is no data source defined in the odbc.ini settings

Setting	Description
Login Timeout	The number of seconds before ColdFusion times out the database connection login attempt.
Limit Connections	Select this check box, then specify the number of simultaneous connections to allow for the current data source. Note: If you enable Limit Connections without specifying a limit for simultaneous connections, ColdFusion defaults to unlimited connections.
ColdFusion Login	Enter a username and password for accessing the ODBC data source. Any username and password specified in a `cfquery` or other data access tag overrides the values specified in the ColdFusion login. When you create a data source using a native database driver, you use the username and password options to pass login information to the database. Disable database connections Disables connections to the data source. Use this setting to perform administrative tasks with the database. You cannot establish new connections to a disabled data source. ColdFusion automatically disconnects and cleans up idle connections. ColdFusion notifies any connection currently in use to clean itself up when the current transaction is complete. To restore the connection to the data source, clear this check box. Maintain database connections ColdFusion establishes a connection to a data source for every operation that requires it. To improve performance by caching the database connection, select this check box.
Connection Timeout	Specify the maximum number of minutes after the database connection is made that you want ColdFusion to cache a connection after it is used. This is different from the server setting to Limit database connection inactive time, located under Server Settings > Caching. The latter setting is server wide and only releases cached connections that have been inactive (not used) for the specified period of time. The Connection Timeout setting does not return a connection to the cache after a specified period of time, regardless of how infrequently it is used. The default is "" or 0, which means the connection timeout is never enforced.
Restrict SQL Operations to	Select SQL operations to restrict for the current data source. To allow all SQL operations, clear all check boxes. To allow specific SQL operations, select the check boxes.
Long Text Retrieval Settings	Retrieves all the data for a long text field. If you use this setting, order the columns in your select statement with the long text fields referenced last. ColdFusion retrieves the column with the long text field, and subsequent columns in the select statement, as unbound data. Unbound data take longer to retrieve than bound data; therefore, this setting can affect performance.
Long Text Buffer Size	If you do not select the Enable retrieval of long text option, specifies the maximum number of characters to retrieve from a long text field is not set. In this case, ColdFusion binds text columns to a buffer whose size you specify in this field. If you specify a size that is not supported by your database, a SQL execution error occurs when you try to retrieve the data. If you select the Enable retrieval of long text option, this is the buffer size ColdFusion uses to retrieve the unbound text column until all data is returned.
Connection String	Use this setting to pass driver-specific connection attributes for ODBC data sources. You can: Specify connection attributes that cannot be defined in the odbc.ini settings Override odbc.ini settings Make ODBC connections dynamically when there is no data source defined in the odbc.ini settings

LiveDocs comments are not longer enabled for ColdFusion 5.0. Please use one of the following resources instead.

Version 5.0

Installing and Configuring ColdFusion Server
Managing Data Sources