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 the link that specifies the type of data source that you want to add:
- ODBC
- OLE DB (available only on Windows NT/2000)
- Native
  The Data Sources page displays any 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, as 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 on this page depends on the platform on which you installed ColdFusion and on the driver you specify. For detailed information about options for your driver, see the section for your specific database.

Note
For the Server field, if you are connecting 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 detailed information about these settings, see "ColdFusion Settings" on page 120.

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 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.

Note

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

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 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 the CF Settings button. These 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 and then specify the number of simultaneous connections that you want 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.
Also, 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 if you need to perform administrative tasks with the database. You cannot establish new connections to a disabled data source. ColdFusion automatically disconnects and cleans up any idle connections. ColdFusion notifies any connection currently in use to clean itself up as soon as 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 no matter how frequently or infrequently it is used.
The default is "" or 0, which means the connection timeout is never enforced.

Restrict SQL Operations to
Select the SQL operations that you want 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 any 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.

Installing and Configuring ColdFusion Server
Managing Data Sources

Setting	Description
Login Timeout	The number of seconds before ColdFusion times out the database connection login attempt.
Limit Connections	Select this check box and then specify the number of simultaneous connections that you want 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. Also, 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 if you need to perform administrative tasks with the database. You cannot establish new connections to a disabled data source. ColdFusion automatically disconnects and cleans up any idle connections. ColdFusion notifies any connection currently in use to clean itself up as soon as 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 no matter how frequently or infrequently it is used. The default is "" or 0, which means the connection timeout is never enforced.
Restrict SQL Operations to	Select the SQL operations that you want 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 any 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.