Programming ColdFusion MX, 2nd Edition

Advanced SQL

This section moves beyond the basic database manipulation techniques we discussed in earlier chapters. Here, we’ll cover methods for creating dynamic SQL, creating and modifying database tables using SQL, using aggregate and scalar functions, performing table unions and joins, and several other database-manipulation techniques. These are the kinds of operations that allow you to interact with databases at a higher level. Most advanced applications such as shopping carts, threaded discussion lists, and business-to-business applications use one or more of the techniques described in this section.

Dynamic SQL

An extremely powerful feature of ColdFusion is the ability to generate dynamic SQL queries based on a variety of inputs. In Chapter 3, you learned how to pass a single dynamic value in an SQL statement:

SELECT Name, Title, Department
FROM EmployeeDirectory
WHERE ID = #ID#

We extend this concept a bit in Example 11-9 to allow for completely dynamic SQL in the WHERE clause of a SQL statement.

Example 11-9. HTML form for searching database records

<!--- Query the EmployeeDirectory table for a list of departments ---> <cfquery name="GetDepartments" datasource="ProgrammingCF"> SELECT DISTINCT Department FROM EmployeeDirectory ORDER BY Department </cfquery> <h2>Locate a User</h2> <form action="search.cfm" method="post"> <table> <tr> <td>Name:</td> <td><input type="text" name="Name" size="20" maxlength="80"></td> </tr> <tr> <td>Title:</td> <td><input type="text" name="Title" ...