![]() ![]() ![]() |
You can harness the real power of the CFQUERY tag when you dynamically customize the contents of a SQL statement by embedding dynamic parameters in the application page. Dynamic parameters (also called variables) include form fields, parameters passed in the URL, and CGI environment information.
The convention for including a dynamic parameter inside a SQL statement is to enclose it in pound signs, such as #State#. When ColdFusion reads text enclosed by # signs, it searches through all Form, URL, cookies, client, and CGI variables looking for one that matches the specified name. When it finds the name, it substitutes the appropriate value for the parameter reference. To fully identify a variable, enter the variable type with the variable name, such as #Form.State#.
The following table summarizes the primary sources from which you can draw dynamic parameters for use in your SQL queries:
Sources for Dynamic Parameters | |
---|---|
Source |
Description |
Form fields |
The most common way of passing parameters to an application page. When a user enters data in a form field, a parameter bearing the name of the form field (#Form.formfield# ) is passed to the application page. |
URL parameters |
Parameters that are embedded on the end of a URL (such as, /input.cfm?name=adam ). |
Server variables |
A variable that remains available to all application pages until the ColdFusion application server terminates. |
CGI environment |
An environment variable interpreted by the browser. Every request sent to an application page has several environment variables that relate to the context in which it was sent. The variables available depend on the browser and server software in use for a given request, such as CGE.UserAgent. |
Query objects |
Query columns you can reference once a query has been executed. Once a query has been run, its results can be used as dynamic parameters in other queries. For example, a query that returns a column called UserID can be referenced in the following form:queryname.UserID |
HTTP Cookies |
General mechanism for storing and retrieving information about the Web client (browser). |
Client variables |
Used to store persistent client variables in the system registry on the Web server. These variables are specific to an individual browser accessing your ColdFusion application. |
Session variables |
Variables available only for an individual session. Session variables are tied to an individual client and persist for as long as that Client ID maintains a session. |
Application variables |
Variables available only for an individual application. Application names are defined in the CFAPPLICATION tag, which is typically used in the Application.cfm file. |
If you created a form to allow end users to search for employees by last name, you could use the following SQL statement with dynamic parameters:
SELECT * FROM Employees WHERE LastName = '#Form.LastName#'
If the user entered "Rucker" for LastName, the SQL statement sent to the database would be:
SELECT * FROM Employees WHERE LastName = 'Rucker'
You will notice that the parameter Form.LastName was surrounded by single quotation marks (`). These are necessary for all literal strings of alphanumeric characters. You do not need to use quotation marks around numbers. You might wonder what happens when a parameter value that contains one or more single quote characters is substituted into a section of a SQL statement delimited by single quotes. Wouldn't this cause a SQL syntax error?
Not if handled properly. ODBC allows you to denote a single quote inside a quote-delimited string by using two consecutive single quote characters ( '' ). Since you can't possibly be expected to do this yourself, ColdFusion automatically replaces the single quote ( ' ) with two single quotes ( '' ) before including parameter values in SQL statements.
In special cases, you might want to suppress automatic escaping of single quotes. To do this, use the PreserveSingleQuotes function.
![]() ![]() ![]() |
AllaireDoc@allaire.com
Copyright © 1998, Allaire Corporation. All rights reserved.