MACROMEDIA COLDFUSION 5-DEVELOPING, Руководство по разработке

Страница: 116 / 438

96 Chapter 6 Making Variables Dynamic
Dynamic SQL
Embedding SQL queries that use dynamic parameters is a powerful mechanism for
linking variable inputs to database queries. However, in more sophisticated
applications, you often want user inputs to determine not only the content of queries
but also the structure of queries.
Dynamic SQL allows you to dynamically determine (based on runtime parameters)
which parts of a SQL statement are sent to the database. So if a user leaves a search
field empty, for example, you can simply omit the part of the WHERE clause that
refers to that field. Or, if a user does not specify a sort order, you can omit the entire
ORDER BY clause.
Implementing dynamic SQL
You implement dynamic SQL in ColdFusion by using
cfif, cfelse, cfelseif
tags
to control how the SQL statement is constructed, for example:
<cfquery name=" queryname " datasource=" datasourcename " >
... Base SQL statement(s)
<cfif value operator value >
...
additional SQL statement(s)
</cfif>
</cfquery>
The following code creates an application that lets a user search the CompanyInfo
database for employees by first name, last name, minimum salary, contract status, or
any combination of these criteria.
WHERE Course_ID=<cfqueryparam
value="#Course_ID#"
cfsqltype="CF_SQL_INTEGER">
</cfquery>
Only select rows where the Course_ID
column equals the value of Course_ID
CFML local variable. Validate that the
variable value is an integer and, if
appropriate for the database driver, use a
bind parameter to associate the value
with the SQL statement.
<cfoutput query="getFirst">
<p>
Department Number: #number#<br>
Description: #descript#
</p>
</cfoutput>
For each row that matches the query,
output the contents of the number and
descript columns.
Code Description