User Guide

cfqueryparam 353
Usage
Use the cfqueryparam tag in any SQL statement (for example, SELECT, INSERT, UPDATE,
and DELETE) that uses ColdFusion variables.
You cannot use the
cfquery cachedAfter or cachedWithin attributes with cfqueryparam.
For maximum validation of string data, specify the maxlength attribute.
This tag does the following:
• Allows the use of SQL bind parameters, which improves performance.
• Ensures that variable data matches the specified SQL type.
• Allows long text fields to be updated from a SQL statement.
• Escapes string variables in single-quotation marks.
To benefit from the enhanced performance of bind variables, you must use
cfqueryparam for all
ColdFusion variables, and your DBMS must support bind variables. If a DBMS does not support
bind parameters, ColdFusion validates and substitutes the validated parameter value back into the
string. If validation fails, it returns an error message.
The validation rules are as follows:
• For these types, a data value can be converted to a numeric value: CF_SQL_SMALLINT,
CF_SQL_INTEGER, CF_SQL_REAL, CF_SQL_FLOAT, CF_SQL_DOUBLE,
CF_SQL_TINYINT, CF_SQL_MONEY, CF_SQL_MONEY4, CF_SQL_DECIMAL,
CF_SQL_NUMERIC, and CF_SQL_BIGINT
• For these types, a data value can be converted to a date supported by the target data source:
CF_SQL_DATE, CF_SQL_TIME, CF_SQL_TIMESTAMP
• For all other types, if the maxLength attribute is used, a data value cannot exceed the
maximum length specified.
ColdFusion debug output shows the bind variables as question marks and lists the values beneath
the query, in order of usage.
Note: To insert an empty string into a Microsoft Access table using the SequelLink ODBC Socket or
SequelLink Access driver, the
CFSQLType attribute must specify CF_SQL_LONGVARCHAR.
The following table shows the mapping of ColdFusion SQL data types with JDBC SQL types
and those of the listed database management systems:
ColdFusion JDBC DB2 Informix Oracle MSSQL
CF_SQL_ARRAY ARRAY
CF_SQL_BIGINT BIGINT Bigint int8,
serial8
CF_SQL_BINARY BINARY Char for Bit
Data
binary
timestamp
CF_SQL_BIT BIT boolean bit
CF_SQL_BLOB BLOB Blob blob blob, bfile