You can specify a connection string for ODBC data sources in ColdFusion. You can use a connection string in your application, or manage it using settings in the ColdFusion Administrator on the ColdFusion Settings page. For details, see ColdFusion Settings.
You can use the connection string to do the following tasks:
Specify connection attributes that cannot be defined in the odbc.ini file.
Override settings in the odbc.ini file.
Make ODBC connections dynamically when no data source is defined in the odbc.ini file.
Some ODBC data sources allow you to pass driver-specific information. For example, many applications that connect to Microsoft SQL Server pass the APP="appname" when connecting. A database administrator uses the information to see which applications are connected to the database server and to identify who is running those applications.
You can also use the connection string to limit data source name (DSN) definitions. For example, if you connect to a SQL Server with multiple defined databases, you may not want to define a ColdFusion DSN for each database. Instead, you can use the connection string to supply the database name for the single DSN that you defined for the SQL Server.
The connection string allows ColdFusion to support ODBC connections for databases that do not have a data source definition in the odbc.ini file. All information required by the particular ODBC driver to connect must be specified in the connection string.
You can use the connectstring attribute in these CFML tags:
cfquery
cfinsert
cfupdate
cfstoredproc
cfgridupdate
When a query is cached, the connection string setting is part of the query. The cache is purged only if the query is changed, such as if the data source name changes.
When you connect to a data source dynamically with a connection string, the dbtype attribute for tags making dynamic connections is set to dbtype=dynamic. This feature allows a ColdFusion application to run on multiple servers without requiring odbc.ini Registry files on each server. You must specify all information required by the ODBC driver to connect in the connectstring attribute. For ODBC connections using the default dbtype (that is, dbtype=odbc), you can use the connectstring attribute to provide additional connection information or override connection information that is specified in the DNS.
Dynamic Connection Example
The following code is a dynamic connection. There is no data source
definition in the odbc.ini settings.
<cfquery name = "DATELIST"
dbtype=dynamic
blockfactor=100
connectstring="DRIVER={SQL SERVER};
SERVER=(local);
UID=sa;
PWD=;
DATABASE=pubs">
SELECT * FROM authors
</cfquery>
For dynamic connections, the ColdFusion Administrator Maintain Connect default value is enabled. If you need to change this, you must use regedit to add a pseudo __DYNAMIC__ key in the ColdFusion/CurrentVersion/DataSources Registry key and
specify a MaintainConnect value of 0.