Using Bind Parameters


Most databases provide a mechanism to bind SQL parameters to specific datatypes. Doing so prevents the database (and database drivers) from having to perform implicit type conversions. The major benefits of parameter binding are:

  • Improved performance

  • Support for large columns

  • From a ColdFusion perspective, preventing hacking via URL parameter manipulation

Parameter binding requires that a parameter be specified along with a datatype with which it is to be bound. The following are the names of the ColdFusion-supported types:

  • CF_SQL_BIGINT

  • CF_SQL_BIT

  • CF_SQL_BLOB

  • CF_SQL_CHAR

  • CF_SQL_CLOB

  • CF_SQL_DATE

  • CF_SQL_DECIMAL

  • CF_SQL_DOUBLE

  • CF_SQL_FLOAT

  • CF_SQL_IDSTAMP

  • CF_SQL_INTEGER

  • CF_SQL_LONGVARCHAR

  • CF_SQL_MONEY

  • CF_SQL_MONEY4

  • CF_SQL_NUMERIC

  • CF_SQL_REAL

  • CF_SQL_REFCURSOR

  • CF_SQL_SMALLINT

  • CF_SQL_TIME

  • CF_SQL_TIMESTAMP

  • CF_SQL_TINYINT

  • CF_SQL_VARCHAR

ColdFusion parameter binding is implemented via the <cfqueryparam> tag, which must be used in between <cfquery> and </cfquery> tags. The following query uses <cfqueryparam> to bind a URL parameter to a specific datatype (an integer):

 <cfquery datasource="DSN"          name="Product"> SELECT product_id, product_name FROM products WHERE product_id=<cfqueryparam value="#URL.product_id#"                                cfsqltype="CF_SQL_INTEGER"> </cfquery> 

The <cfqueryparam> cfsqltype attribute defaults to CF_SQL_CHAR, if not specified. Additional attributes may be used optionally to specify value lengths and ranges.

CAUTION

SQL query parameter binding is not supported by all databases and database drivers. Refer to the ColdFusion documentation to determine whether your database is supported.


NOTE

The exact syntax used by the database for query parameter binding varies dramatically from one database to the next. Fortunately, this is handled internally by the <cfqueryparam> tag so that you need not worry about it.




Macromedia ColdFusion MX 7 Certified Developer Study Guide
Macromedia ColdFusion MX 7 Certified Developer Study Guide
ISBN: 0321330110
EAN: 2147483647
Year: 2004
Pages: 389
Authors: Ben Forta

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net