Using Connection String Options

ColdFusion 5 allows you to specify a connection string for ODBC data sources. You can do this programmatically or in the ColdFusion Administrator.

About the connection string

You can use the connection string to do the following tasks:

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.

Some ODBC data sources let you pass driver-specific options. A database administrator (DBA) can use these options to see which applications are connected to the database server, and to identify who is running those applications. For example, many applications that connect to Microsoft SQL Server pass the attribue-value pairs APP="appname" and WSID="work station id" when connecting.

Consider the following cfquery, which specifies values in the connection string for the APP and WSID attributes:

<cfquery

  name="getInfo" datasource="2Northwind" dbtype="ODBC"

  connectstring="DRIVER={SQL SERVER};

        SERVER=(local); UID=sa; PWD=;

        DATABASE=Northwind;

    APP=ColdFusion5;WSID=Workstation_Moe"

>

  SELECT      *

  FROM         shippers

</cfquery>

The APP and WSID values are readily available when you run the above query. A SQL Server DBA can use Profiler to view this information in a trace:

Limiting DSN definitions

Another use of the connect string feature is to limit data source name (DSN) definitions. For example, if you are connecting to a server that has multiple databases defined, you might not want to define a ColdFusion DSN for each database. Instead, you can now use the connection string to supply the database name for the single DSN that you defined for that server. The connection string allows ColdFusion to support ODBC connections for databases that lack a data source definition in the odbc.ini settings. All information required by the particular ODBC driver to connect must be specified in the connection string.

Changes to the ColdFusion Administrator

The Settings page in the ColdFusion 5 Administrator includes a Connection String option to support the connect string feature. You can specify a connect string in the ColdFusion settings for an ODBC data source. If you specify a connectstring attribute for a tag that supports the attribute, then it overrides the Administrator setting.

Changes to CFML tags

A new connectstring attribute is now available in the following CFML tags:

cfquery
cfinsert
cfupdate
cfstoredproc
cfgridupdate

Using a connect string in a cached query

As with other query settings, when a query is cached, the connect string setting becomes part of that cached query. The cache is purged only if the query is changed, for example, if you change the data source name.

Use dynamic for dbtype attribute

When connecting to data sources 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 entries 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 DSN.

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.

Advanced ColdFusion Administration
Advanced Data Source Management