Accessing Data Sources

There are two ways to access data sources:

Add data sources in the ColdFusion Administrator.
You assign a data source name and set all the information required to establish an ODBC connection. You then use the data source name in any CFML tags that establish database connections. This technique puts all the information about a ColdFusion Server's database connections in a single, easy-to-manage location.
Specify the database information directly in your CFML tag.
This way you accesses the data source dynamically. It eliminates the need for you to add a data source for each database on your server. It also allows a ColdFusion application to run on multiple servers without having to statically configure each server independently.

Adding data sources

You use the ColdFusion Administrator to add data sources to the ColdFusion Administrator.

When you add a data source, you assign it a name so that you can reference it within tags such as cfquery on application pages to query databases. During a query, the data source tells ColdFusion which database to connect to and what parameters to use for the connection.

Use the following procedure to add the CompanyInfo data source that you use in many examples in this book.

Note

By default, the ColdFusion setup installs the CompanyInfo and cfsnippets databases used in examples in this book and adds them to the available ODBC data sources. Therefore, this procedure should not be necessary to work with examples in this book.

To add a data source:

Start the ColdFusion Administrator.
On Windows, select Start > Programs > ColdFusion Server > ColdFusion Administrator. On UNIX, enter the URL hostname/CFIDE/administrator in your browser.
The Administrator prompts you for a password if you assigned one to the ColdFusion Server during the installation.
Enter a password to gain access to the Administrator.
Select ODBC under the Data Sources heading on the left menu.
Name the data source CompanyInfo.
On Windows Select Microsoft Access Driver (*.mdb) from the drop-down box to describe the ODBC driver. On UNIX, select the Merant Dbase/FoxPro driver.
Click Add.
In the Database File field, enter the full path of the database. (You can also use the Browse button to locate the file.).
On Windows specify the path to the company.mdb file, typically C:\CFusion\database\Company.mdb. On UNIX, specify the path to the CompanyInfo directory, typically /opt/coldfusion/database/CompanyInfo.
Click Create to create the CompanyInfo data source.
The data source is added to the data source list.
Locate CompanyInfo in the data source list.
Select Verify to run the verification test on the data source.
If the data source was created, you should see this message:
```
The connection to the data source was verified successfully.
```
Click Go Back to return to the data sources list.

For more information about managing data sources, see Advanced ColdFusion Administration.

Specifying a connection string

You can dynamically override ODBC connection information that you set in the ColdFusion Administrator. You can also specify connection attributes that are not set in the Administrator. To do so, use the connectstring attribute in any CFML tag that connects to a database: cfquery, cfinsert, cfupdate, cfgridupdate, and cfstoredproc.

For example, the following code creates a connection to a defined Microsoft SQLServer data source using a connect string to specify the Application and Work Station ID.

<cfupdate datasource = "mssql"

  connectstring = "APP=ColdFusion;WSID=fenway" 

  tablename = "department">

Note

Connect string properties are specific to the database you are connecting to. See the documentation for your database for a list of connect string properties.

Adding data source notes and considerations

When adding data sources to ColdFusion Server, keep these guidelines in mind:

Data source names should be all one word and begin with a letter.
Data source names can contain only letters, numbers, and the underscore.
Data source names should not contain special characters.
Although data source names are not case-sensitive, you should use a consistent capitalization scheme.
A data source must exist in the ColdFusion Administrator before you use it on an application page to retrieve data (unless you specify the data source dynamically).

Specifying data sources dynamically

To specify a data source dynamically, use the following attribute in the cfquery tag:

dbtype = "dynamic"

Specify all the required ODBC connection information, including the ODBC driver and the database location, in the connectstring attribute. For example, you could use the following code for a query that dynamically specifies the pubs database on a local Microsoft SQLServer:

<cfquery name = "datelist"

  dbtype = "dynamic"

  blockfactor = 100

  connectstring = "DRIVER={SQLSERVER};SERVER=(local);UID=sa;PWD=;

DATABASE=pubs">

  SELECT * FROM authors

</cfquery>

The following example uses a Microsoft Access database:

<cfquery name="titles"

  dbtype = "dynamic"

  ConnectString="DRIVER=Microsoft Access Driver (*.mdb);

DBQ=C:\CFusion\Database\cfsnippets.mdb;DriverId=281;

FIL=MS Access;MaxBufferSize=2048;PageTimeout=5">

  SELECT * FROM Courses

</cfquery>

Developing ColdFusion Applications
Querying a Database