Accessing Databases from a ColdFusion MX Application

You can use the data source by applying the <CFQUERY> tag in conjunction with SQL statements. Perform the following tasks to retrieve data from a database:

1. Use the <CFQUERY> tag to connect to a database.

2. Include SQL commands inside the <CFQUERY> block to specify the operations you want to perform on the data. The retrieved data, called the result set, is stored on that page as a query object.

3. Finally, reference the query object and use its data values in a tag that displays data, such as <CFOUTPUT>, <CFGRID>, or <CFGRAPH>.

Using the <CFQUERY> Tag

<CFQUERY> is one of the most frequently used CFML tags. You specify the name of the query object in the tag's NAME attribute. The syntax for the <CFQUERY> tag is

 <CFQUERY name="EmployeeList" datasource="EmployeeData">     SQL statements </CFQUERY> 

In this example, the query code tells ColdFusion to use the EmployeeData data source to connect to the database and store the retrieved data in the query object EmployeeList.

<CFQUERY> is a block tag—it has an opening <CFQUERY> and an ending </CFQUERY> tag. You need to enclose the attribute values in double quotes. Use the NAME attribute to name the query object. This helps you to reference it later on the page. The SQL statements inside the <CFQUERY> block execute the data processing requirements during the query.

Using SQL Statements in the <CFQUERY> Tag

Write the SQL statements that you want the database to execute within the beginning and ending <CFQUERY> tags. When the database processes the SQL statements, it sends the requested data (called the data set) to the ColdFusion server. ColdFusion creates a query object and places this data set in memory and assigns it the name that you defined in the NAME attribute.

An SQL statement always begins with an SQL verb. For example, to retrieve data from a database, you need to write a SELECT statement that lists the fields or columns that you want to select for the query. Begin the SELECT clause with the SELECT keyword followed by the names of the columns that you want to be returned. If there are multiple columns, separate each column with a comma. The syntax for the SELECT statement is

 SELECT table_name.column_name1,table_name.column_name2 

Use an asterisk (*) to select all columns in the table. The SELECT clause in this case is written as

 SELECT * 

You can use the DISTINCT keyword to suppress the display of duplicate records. The syntax for this is

 SELECT DISTINCT table_name.column_name1,table_name.column_name2 

Table 5.1 lists the keywords that identify commonly used SQL verbs.

Table 5.1: Common SQL Verbs

Keyword	Description
SELECT	Retrieves the specified records
INSERT	Adds a new row
UPDATE	Changes values in the specified rows
DELETE	Removes the specified rows

A FROM clause follows the SELECT statement. The FROM clause is a part of the SELECT statement too. It begins with the FROM keyword and is followed by the name of the table being used for the selection. To select columns from more than one table, use a comma to separate the names of the tables. For example, consider the following code:

 SELECT employeedata.employee_id, employeedata.employee_name,         employeedata.employee_salary, companyname.employee_Id FROM employeedata,companyname 

In this example, columns are being selected from two tables, employeedata and companyname.

Some common statement clauses are given in Table 5.2.

Table 5.2: Common Statement Clauses

Keyword	Description
FROM	Names the data tables for the operation
WHERE	Sets one or more conditions for the operation
ORDER BY	Sorts the result set in the specified order
GROUP BY	Groups the result set by the specified select list items

SQL uses operators to perform operations on the fields. The SELECT statement allows you to filter the results of a query to return only those records that meet the specified criteria. For example, you can use the following query to access all database records for employees in a specific department:

 SELECT * FROM employeedata WHERE DeptName='Technology' 

You can combine multiple conditions using the WHERE clause and the AND operator. The following example uses two conditions:

 SELECT * FROM employeedata WHERE DeptName='Technology'AND Title='Manager' 

Some common operators are listed in Table 5.3.

Table 5.3: Common SQL Operators

Keyword	Description
AND	Both conditions must be met
OR	At least one condition must be met
NOT	Exclude the condition following the keyword
LIKE	Matches with a pattern
IN	Matches with a list of values
BETWEEN	Matches with a range of values
=	Equal to
<>	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
+	Addition
-	Subtraction
/	Division
*	Multiplication

A database doesn't sort the records returned from an SQL query on its own. To obtain records in a particular order, write an SQL statement that sorts the records returned from the database. Use the ORDER BY clause in this statement.

For example, the following SQL statement returns the records of the table ordered by the LastName column:

 SELECT * FROM employeedata ORDER BY LastName 

You can combine multiple fields in the ORDER BY clause to perform additional sorting:

 SELECT * FROM employeedata ORDER BY DeptName, LastName 

This statement returns rows ordered by department, and then by last name within the department.

The following is the syntax for an SQL statement:

 SELECT column_names FROM table_names [WHERE search_condition] [GROUP BY group_expression] [HAVING condition] [ORDER BY order_condition [ASC|DESC]] 

The statements in square brackets are optional.

Note

There are various versions of SQL syntax. ColdFusion doesn't validate the SQL in <CFQUERY>, so you're free to use any syntax that's supported by your data source.

As mentioned earlier, ColdFusion isn't a case-sensitive programming environment. However, many databases are case-sensitive. This means that you must match exactly the case of all columns and table names in SQL queries.

For example, the following queries aren't equivalent in a case-sensitive database:

 SELECT employee_id FROM EMPLOYEEDATA 

and

 SELECT EMPLOYEE_ID FROM employeedata 

In a case-sensitive database, EMPLOYEEDATA and employeedata are two different tables.

Returning the Query Data

You can use the <CFOUTPUT> tag with the QUERY attribute to return data from the recordset to a page. ColdFusion loops through the code in the <CFOUTPUT> block, once for each row in the recordset that's returned from the database.

You need to reference specific column names within the <CFOUTPUT> block to return the data to the page. To format, place text, CFML tags, and HTML tags inside the <CFOUTPUT> block. Although the <CFOUTPUT> tag accepts a variety of optional attributes, you can use the QUERY attribute to define an existing query.

A <CFQUERY> tag must precede the <CFOUTPUT> tag that references its results. Both must be on the same page. Alternately, use the <CFINCLUDE> tag to include pages. Prefix the query variables with the name of the query inside a <CFOUTPUT> block that uses a <CFQUERY> attribute.

When you query a database with the <CFQUERY> tag, query properties are returned with the data too. The query properties are listed in Table 5.4.

Table 5.4: Query Properties

Property	Description
RecordCount	The total number of records returned by the query
ColumnList	A comma-delimited list of the query columns
CurrentRow	The current row of the query being processed by <CFOUTPUT>

When you're using query properties, reference the query property within a <CFOUTPUT> block to enable ColdFusion to return the query property value to the page. The query property reference should be used with pound signs (#). This ensures that the property name is replaced with its current value. Do not use the <CFOUTPUT> tag query when returning the RecordCount or ColumnList property. If you do use it, you'll get one copy of the output for each row. To avoid this, prefix the property with the name of the query instead:

 <CFOUTPUT   QUERY = "query_name"   GROUP = "query_column"   STARTROW = "start_row"   MAXROWS = "max_rows_output"> </CFOUTPUT> 

Using Query Results in Queries

The querying of queries, using the results of a previous query in any <CFQUERY> tag, is a special feature in ColdFusion. You can query a database once and use the results in several dependent queries.

Performing queries on query results has many benefits:

• An application accesses the same tables several times. These tables are often very large. You can reduce access time by using data that's already in the memory.

• You can join and perform unions on results from different data sources.

• You can perform various operations on a set of data. You can query a database once, and use the results to generate several different summary tables.

You can create a query using a query object from a ColdFusion tag or function that generates query results, such as the <CFHTTP>, <CFPOP>, <CFDIRECTORY>, and QUERY functions.

Use a limited subset of the SQL SELECT syntax to perform a query on a query. This language is a subset of the SQL92 standard and is known as CFSQL. SQL statements available to CFSQL include FROM, GROUP BY, ORDER BY, AS, WHERE, UNION, HAVING, DISTINCT, Boolean predicates (such as LIKE and NOT LIKE), aggregate functions (such as Count() and Sum()), and comparison operators (such as <= and <>).

To perform a query on a query, you need to perform these steps:

1. Specify the <CFQUERY> tag's DBTYPE attribute as QUERY. This informs the <CFQUERY> tag that a query is being performed against an existing query, not against a data source.

2. Specify the names of one or more existing queries as the table names in an SQL SELECT statement.

3. If the database content doesn't change rapidly, use the CACHEDWITHIN attribute to cache the query results of requests that are generated on the various pages.

The following is an example of creating a query in a query. Here, Query in a Query displays various records pertaining to members of an employee list:

 <html> <head> <title> Query in a Query</title> </head> <body> <h1>Employee List</h1> <!--- LastNameSearch initialization ---> <CFSET LastNameSearch = "TRUEMAN"> <!--- Normal query ---> <CFQUERY datasource = "EmployeeData" name = "EmpData"           cachedwithin=#CreateTimeSpan(0,2,0,0)#>     SELECT *     FROM Employee </CFQUERY> <!--- Query using query results ---> <CFQUERY dbtype = "query" name = "QueryinQuery" >     SELECT Emp_ID, FirstName, LastName     FROM EmpData     WHERE LastName = '#LastNameSearch#' </CFQUERY> Output using a query in query<br> <CFOUTPUT query = QueryinQuery>     #Emp_ID#: #FirstName# #LastName#<br> </CFOUTPUT> <br> Columns in the EmpData database query<br> <CFOUTPUT>     #EmpData.columnlist#<br> </CFOUTPUT> <br> Columns in the QueryinQuery query<br> <CFOUPUT>     #QueryinQuery.columnlist#<br> </CFOUTPUT> </body> </html> 

Using the <CFTRANSACTION> Tag

A database commit occurs at a regular interval. This is managed by the database internal mechanism. This change isn't written to the database until it's committed by the database.

Rollback undoes a change made to a database. You can roll back the change up to the point where you committed it. You cannot roll back a change after committing it.

Most databases support transactions that consist of one or more SQL statements. A transaction is a set of SQL statements that are either committed or rolled back together. The transactions are used to maintain data integrity in the database. For example, if a record is updated in one table, then corresponding details in a related table should also be updated or both the tables should remain in the original state before the update started. You can end a transaction by committing or rolling back all your changes within it. Transactions are useful when there are multiple related writes to a database. Before committing the writes, you should ensure that they're all error-free. Include all the writes within a single transaction and check for errors after each write. If any write causes an error, you can roll back all of them. If all writes occur successfully, you can commit the transaction.

The <CFTRANSACTION> tag allows you to implement database transactions for controlling rollback and commit. This tag treats all query operations within the <CFTRANSACTION> and </CFTRANSACTION> tags as a single transaction.

<CFTRANSACTION> accepts two optional attributes, ACTION and ISOLATION. ACTION specifies the action to be taken for a transaction. This option has three attributes: beginning of the block of code to execute, committing a pending transaction, and rolling back a pending transaction.

ISOLATION specifies the ODBC lock type to be used for the transaction. Not all databases/database drivers support all the isolation levels.

For example:

 <!--- CFTRANSACTION tag is being used with two queries being treated as a single query. If one fails, any changes made are rolled back ---> <CFTRANSACTION> <CFQUERY NAME="DeleteEmpData" DATASOURCE="Important">     DELETE FROM EmpDirectory     WHERE EMP_ID= #Form.EmpId#     </CFQUERY> <CFQUERY NAME="DeleteSomeRecs" DATASOURCE="Important">     DELETE FROM EmpManagers     WHERE EMP_ID=#Form.EmpId# </CFQUERY> <CFTRANSACTION> 

<CFTRANSACTION> can be nested to allow portions of a transaction to be committed or rolled back within the main CFTRANSACTION block. To commit a transaction within a nested <CFTRANSACTION> tag:

 <CFTRANSACTION ACTION="commit"/> 

The slash is an alternative syntax for an ending tag.