<CFQUERY NAME="query_name" DATASOURCE="ds_name" ...other attributes... > SELECT STATEMENT WHERE column_name= <CFQUERYPARAM VALUE="parameter value" CFSQLType="parameter type" MAXLENGTH="maximum parameter length" SCALE="number of decimal places" DBNAME="database name" NULL="Yes/No" LIST="Yes/No" SEPARATOR="separator character" > AND/OR ...additional criteria of the WHERE clause... </CFQUERY>
CFQUERYPARAM checks the data type of a query parameter. The CFQUERYPARAM tag is nested within a CFQUERY tag. More specifically, it is embedded within the query SQL statement. If you specify its optional parameters, CFQUERYPARAM also performs data validation.
Note: For data, you must specify the MAXLENGTH attribute in order to ensure that maximum length validation is enforced.
See the Usage section for details.
Required. Specifies the actual value that ColdFusion passes to the right of the comparison operator in a where clause. See Usage section for details.
Optional. This is the SQL type that the parameter (any type) will be bound to. The default value is CF_SQL_CHAR. The CFSQLTypes are as follows:
CF_SQL_BIGINT | CF_SQL_IDSTAMP | CF_SQL_REFCURSOR |
CF_SQL_BIT | CF_SQL_INTEGER | CF_SQL_SMALLINT |
CF_SQL_CHAR | CF_SQL_LONGVARCHAR | CF_SQL_TIME |
CF_SQL_DATE | CF_SQL_MONEY | CF_SQL_TIMESTAMP |
CF_SQL_DECIMAL | CF_SQL_MONEY4 | CF_SQL_TINYINT |
CF_SQL_DOUBLE | CF_SQL_NUMERIC | CF_SQL_VARCHAR |
CF_SQL_FLOAT | CF_SQL_REAL |
Optional. Maximum length of the parameter. The default value is the length of the string specified in the VALUE attribute.
Optional. Number of decimal places of the parameter. The default value is zero. Applicable for CF_SQL_NUMERIC and CF_SQL_DECIMAL.
Optional. Specify Yes or No. Indicates whether the parameter is passed as a NULL. If you specify Yes, the tag ignores the VALUE attribute. The default value is No.
Optional. Specify Yes or No. Indicates that the parameter value of the VALUE attribute is a list of values, separated by a separator character. The default value is No. See the SEPARATOR attribute for details.
Optional. Specifies the character that is to be used to separate the values in the list of parameter values specified by the VALUE attribute. The default separator is a comma. If you specify a list of values for the VALUE attribute, you must also specify the LIST attribute.
The CFQUERYPARAM is designed to do the following things:
The ColdFusion ODBC, DB2, Informix, Oracle 7 and Oracle 8 drivers support SQL bind parameters. However, at present, the ColdFusion Sybase 11 driver and Sybase native driver do not support SQL bind parameters.
If a database does not support bind parameters, ColdFusion still performs validation and substitutes the validated parameter value back into the string. If validation fails, an error message is returned. The validation rules follow:
For an ODBC, DB2, or Informix data source, the generated syntax of the SQL statement is as follows:
SELECT * FROM courses WHERE col1=?
For an Oracle 7 or Oracle 8 data source, the syntax of the SQL statement is as follows:
SELECT * FROM courses WHERE col1=:1
For a Sybase11 data source, the syntax of the SQL statement is as follows:
SELECT * FROM courses WHERE col1=10