How to Connect to Databases

To access a database using ColdFusion, you need a defined data source. A data source is a named configuration (driver and settings) that points to a specific database. The database can be located on the same server as ColdFusion or on another server elsewhere in the network.

The connection behind the data source acts as a gateway or translator to the database that is servicing the application. Each database has its own particular format and mechanisms for storing and retrieving data. To make application development easier, a data source is defined using a database driver (software that knows how to talk to a specific database). A driver and associated configuration is known as a data source. Database connectivity in ColdFusion is possible through JDBC. The included JDBC drivers allow developers to pass Structured Query Language (SQL) statements to databases and retrieve results (if appropriate).

NOTE

Structured Query Language (SQL) is a standard programming language for interacting with a database. Interactions include retrieving, adding, updating, and deleting information. SQL also can be used for restructuring or modifying the schema and indexing properties of a database.

Although SQL is an ANSI standard, many database products support SQL with proprietary extensions to the standard language, known as a dialect. Examples include Oracle's PL-SQL and MS SQL Server's Transact-SQL.

Adding a Data Source

Data sources to be used with ColdFusion are defined in ColdFusion Administrator (or via the Administration API). You can also use ColdFusion Administrator to view and modify details of the data source connections registered for your server. The type of data source determines what options are available and to what extent they can be modified through the ColdFusion Administrator interface.

A logical name is assigned to a data source so that you can refer to it within tags (e.g., the <cfquery> tag). When processing a query, the data source informs ColdFusion which database to connect to and which default attributes to use for the connection.

ColdFusion data sources should abide by the following naming conventions:

• Data sources must be uniquely named.

• As with other ColdFusion variables, a data source name should begin with a letter.

• A data source name can contain any combination of letters, numbers, and underscores.

• A data source name should not contain any special characters (including spaces and periods).

Obviously, a data source must be visible in ColdFusion Administrator before you attempt to reference it within an application page.

The data source configuration options make reference to the location of the underlying database. In addition, they provide Administrator with the capability to set a series of default values for connection parameters. Several of these parameters are specific to the driver in question and can be used to tweak the performance and reliability of the database connection.

User name and password details define the default authentication for the ColdFusion server in the absence of any other instructions. A default password can be defined at the level of the driver, and it is superseded by anything placed in the ColdFusion login fields of Administrator. Administrator is, in turn, overridden by the use of a username or password attribute in the <cfquery> tag.

CAUTION

Do not specify the database administrator password as the authentication to be used for the data source. A malicious developer with the ability to modify code on the ColdFusion server would have total control of the database.

The best practice is to set up a user account for ColdFusion on the database with specific permissions for your application. These permissions should enable your code to perform only the required database operations on the application database, and nothing more.

SQL operations can be restricted through the data source. This enables administrators to grant access to the database but to restrict the types of SQL statements that can be passed. For example, you could enable SELECT statements for retrieving information but not allow any updates or deletions. Security-conscious administrators might prefer to implement these types of restrictions at the database level instead.

What About ODBC?

As of ColdFusion MX, ColdFusion is built on underlying Java technologies, and thus the data sources and drivers used are Java database drivers (JDBC).

Another very common database driver format is ODBC, originally popularized by Microsoft but now supported by numerous products and vendors. Although ColdFusion no longer uses ODBC drivers (prior versions of ColdFusion did), access to ODBC drivers is still possible via a bridgea special JDBC adapter. To use an ODBC driver via the bridge, that driver must already be definedperhaps by using the Windows Control Panel appletand then the virtual data source (using the included JDBC-ODBC driver) should be defined in ColdFusion Administrator.

NOTE

ODBC support is provided primarily for backward compatibility, so as to support databases that lack JDBC drivers. For performance reasons, direct JDBC use is usually the best choice.