Programming ColdFusion

Introducing CFQUERY

The CFQUERY tag is the main tag used by ColdFusion to interact with databases. Using CFQUERY, you can pass any Structured Query Language (SQL) statement to a data source registered with the ColdFusion Administrator. The content of your SQL statements determines what action is performed against the data source. The next section provides a quick primer on SQL.

The CFQUERY tag works by establishing a connection with the specified data source, passing a series of SQL commands, and returning query variables that contain information about the operation. The basic syntax for using the CFQUERY tag is as follows:

<CFQUERY NAME="query_name"
         DATASOURCE="datasource_name"
         DBTYPE="dbtype"
         CONNECTSTRING="connection_string">
SQL statements
</CFQUERY>

Each attribute in the opening CFQUERY tag specifies information about the data source and how ColdFusion should access it. The NAME attribute assigns a name to the query. Valid query names must begin with a letter and can contain only letters, numbers, and underscore characters. NAME is required when passing an SQL SELECT statement and is optional for all other SQL operations.^[5] The DATASOURCE attribute is required in all circumstances except when DBTYPE is Query or Dynamic and specifies the name of the data source (as it appears in the ColdFusion Administrator) to connect to when executing the query. The DBTYPE attribute is optional and specifies the type of database driver to use when connecting to the data source. Possible entries are:

ODBC (the default)

Connect to the data source using an ODBC driver.

OLEDB

Make the connection using an OLEDB driver.

Oracle73

Connect using the Oracle 7.3 native driver. This requires the 7.3.4.0.0 or later client libraries be installed on the ColdFusion server.

Oracle80

Connect using the Oracle 8 native driver. This requires the 8.0 or later client libraries be installed on the ColdFusion server.

Sybase11

Connect using the Sybase 11 native driver. This requires the 11.1.1 or later client libraries be installed on the ColdFusion server.

DB2

Connect using the DB2 5.2 native driver.

Informix73

Connect using the Informix 7.3 native driver. This requires the Informix SDK 2.5 or later or Informix-Connect 2.5 (or later) for Windows.

Query

Specifies the query should use an already existing query as the data source. If this option is used, you don’t need to specify a value for DATASOURCE. The ability to query a query was introduced in ColdFusion 5.0 and is discussed in Chapter 11.

Dynamic

Allows ColdFusion to make an ODBC connection to a data source without registering the data source in the ColdFusion Administrator. The ability to make a dynamic data source connection was added in ColdFusion 5.0. When making a dynamic connection, all information normally provided in the ColdFusion Administrator for the connection must be specified in the CONNECTSTRING attribute.

The CONNECTSTRING attribute was introduced in ColdFusion 5.0 and allows you to pass additional connection information to an ODBC data source that can’t be passed via the ColdFusion Administrator. CONNECTSTRING can also override connection information set for a data source already registered in the ColdFusion Administrator. For example, if you use SQL Server as your database, there is no way to specify the name of the application connecting to the data source in the ColdFusion Administrator. Using CONNECTSTRING, however, it is possible to pass this additional bit of information:

<CFQUERY NAME="GetEmployeeInfo"
         DATASOURCE="MySQLServer"
         CONNECTSTRING="APP=MyCFApp">

For specific connection string options, you should consult the documentation for your particular database. There are a number of additional attributes that can be used with the CFQUERY tag. For a complete list, see its tag reference in Appendix A.

Connecting to a Data Source Dynamically

A new feature in ColdFusion 5.0 is the ability to make a dynamic ODBC connection to a data source that isn’t registered with the ColdFusion Administrator. You make a dynamic connection by setting the DBTYPE attribute of the CFQUERY tag to Dynamic and specifying all the information required to connect to the data source in the CONNECTSTRING attribute. For example, to make a dynamic connection to an Access database named ProgrammingColdFusion.mdb, you use the following:

<CFQUERY NAME="GetEmployeeInfo"
         DBTYPE="dynamic" 
         CONNECTSTRING="DRIVER=Microsoft Access Driver (*.mdb);
                        DBQ=D:\databases\programmingcoldfusion.mdb;
                        FIL=MSAccess;
                        UID=myusername;
                        PWD=mypassword">

In this example, a connection is made using the Microsoft Access ODBC driver to a database named ProgrammingColdFusion.mdb, stored in D:\databases. The UID and PWD parameters specify the username and password, if any, for the database. Note that name/value pairs in the connection string are delimited by semicolons. Additionally, no spaces should be present between the parameter name, the equal sign, and the value associated with the parameter. If you find yourself encountering errors when trying to connect using a CONNECTSTRING, be sure each value is typed exactly as it is expected by your driver. For example, specifying “Microsoft Access Driver(*.mdb)” causes an error, as the correct driver name is “Microsoft Access Driver (*.mdb)” (note the space before the open parenthesis). Connecting to an enterprise level database is just as simple. For example, a connection to a SQL Server database looks something like this:

<CFQUERY NAME="GetEmployeeInfo"
         DBTYPE="dynamic"
         CONNECTSTRING="DRIVER={SQL SERVER};
                        SERVER=MySQLServer;
                        DATABASE=ProgrammingColdFusion;
                        UID=myusername;
                        PWD=mypassword">

By default, ColdFusion uses a special internally defined data source __dynamic__ to pool all dynamic connections. Connection pooling improves query performance as additional requests to the same data source use the same connection. You can define additional connection pools for your dynamic connections in two ways:

• Specify a name for the DATASOURCE attribute of the CFQUERY tag. Doing this when DBTYPE is Dynamic causes ColdFusion to create a new connection pool, usable by any dynamic connections that use the same DATASOURCE name.

• Create a bogus data source in the ColdFusion Administrator and reference it using the DATASOURCE attribute of the CFQUERY tag. To create a bogus data source in the ColdFusion Administrator, you need to enter the name of a database file, hostname, or IP address. For consistency, I suggest you use “dynamic”. This method allows you to set default values for your dynamic connections such as timeouts, passwords, restricted SQL operations, etc.

Due to the security risks of allowing developers (especially in hosted environments) to make dynamic data-source connections and to pass additional connection string parameters, both actions can be disabled in the Tag Restrictions section of the Security tab in the ColdFusion Administrator.

---

^[5] Although the NAME attribute is required only for SQL SELECT statements, you may wish to use it with all your queries. It makes debugging easier, especially for templates that contain multiple queries, because it allows you to identify each query by name in the debug output.