# CFQUERYPARAM

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.

### Syntax

    <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"
        >
        AND/OR ...additional criteria of the WHERE clause...
    </CFQUERY>

#### VALUE

Required. Specifies the actual value that ColdFusion passes to the right of the comparison operator in a where clause. See Usage section for details.

#### CFSQLTYPE

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 |

#### MAXLENGTH

Optional. Maximum length of the parameter. The default value is the length of the string specified in the VALUE attribute.

#### SCALE

Optional. Number of decimal places of the parameter. The default value is zero. Applicable for CF_SQL_NUMERIC and CF_SQL_DECIMAL.

#### NULL

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.

### Usage

The CFQUERYPARAM is designed to do the following things:

- Allows the use of SQL bind parameters.
- Allows long text fields to be updated from an SQL statement.
- Improves performance.

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 types 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, data values can be converted to a numeric value.
- For types CF_SQL_DATE, CF_SQL_TIME and CF_SQL_TIMESTAMP, data values can be converted to a date supported by the target data source.
- For all other types, if the MAXLENGTH attribute is used, data value cannot exceed the maximum length specified.

The SQL syntax generated by the ColdFusion server is dependent on the target database.

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

### Example

    <!--------------------------------------------------------------------
    This example shows the use of CFQUERYPARAM when valid input is given in
    Course_ID.
    ----------------------------------------------------------------------->
    <HTML>
    <HEAD>
    <TITLE>CFQUERYPARAM Example</TITLE>
    </HEAD>

    <BODY>
    <h3>CFQUERYPARAM Example</h3>
    <CFSET Course_ID=12>
    <CFQUERY NAME="getFirst" DATASOURCE="cfsnippets">
        SELECT *
        FROM courses
        WHERE Course_ID=<CFQUERYPARAM VALUE="#Course_ID#"
        CFSQLType="CF_SQL_INTEGER">
    </CFQUERY>
    <CFOUTPUT QUERY="getFirst">
    <P>Course Number: #number#<br>
     Description: #descript#
    </P>
    </CFOUTPUT>
    </BODY>
    </HTML>

    <!----------------------------------------------------------------------
    This example shows the use of CFQUERYPARAM when invalid numeric data is
    in Course_ID.
    ----------------------------------------------------------------------->
    <HTML>
    <HEAD>
    <TITLE>CFQUERYPARAM Example</TITLE>
    </HEAD>

    <BODY>
    <h3>CFQUERYPARAM Example With Bad Numeric Data</h3>
    <CFSET Course_ID="12; DELETE courses WHERE Course_ID=20">
    <CFQUERY NAME="getFirst" DATASOURCE="cfsnippets">
        SELECT *
        FROM courses
        WHERE Course_ID=<CFQUERYPARAM VALUE="#Course_ID#"
        CFSQLType="CF_SQL_INTEGER">
    </CFQUERY>
    <CFOUTPUT QUERY="getFirst">
    <P>Course Number: #number#<br>
     Description: #descript#
    </P>
    </CFOUTPUT>
    </BODY>
    </HTML>

The CFQUERYPARAM tag returns the following error message when this example is executed.

    VALUE
    Invalid data '12; DELETE courses WHERE Course_ID=20' for
            CFSQLTYPE 'CF_SQL_INTEGER'.

    <!----------------------------------------------------------------------
    This example shows the use of CFQUERYPARAM when invalid string data is in
    Course_ID.
    ----------------------------------------------------------------------->
    <HTML>
    <HEAD>
    <TITLE>CFQUERYPARAM Example</TITLE>
    </HEAD>

    <BODY>
    <h3>CFQUERYPARAM Example with Bad String Input</h3>

    <CFSET LastName="Peterson; DELETE employees WHERE LastName='Peterson'">
    <-----------------------------------------------------------------------
    Note that for string input you must specify the MAXLENGTH attribute for
    validation.
    ----------------------------------------------------------------------->
    <CFQUERY NAME="getFirst" DATASOURCE="cfsnippets">
        SELECT *
        FROM employees
        WHERE LastName=<CFQUERYPARAM VALUE="#LastName#"
        CFSQLType="CF_SQL_VARCHAR"
        MAXLENGTH="17">
    </CFQUERY>
    <CFOUTPUT QUERY="getFirst">
    <P>Course Number: #FirstName# #LastName#
     Description: #Department#
    </P>
    </CFOUTPUT>
    </BODY>
    </HTML>

    The CFQUERYPARAM tag returns the following error message when this
    example is executed.
    VALUE
    Invalid data 'Peterson; DELETE employees WHERE
            LastName='Peterson'' value exceeds MAXLENGTH setting '17'.

###