By Justin Fidler
Chief Technology Officer
Bantu, Inc.

Introduction

This document explores how CFQUERYPARAM works, specifically related to Oracle. Most other major relational databases, such as Microsoft SQL Server or Informix, also implement bind variables. Smaller "desktop" databases like Access may not. Check with your database vendor, as well as the drivers, to see if bind variables are supported.

In Oracle, ColdFusion's Native Drivers (provided in the Enterprise version) support bind variables. While I haven't personally tested the ODBC Oracle drivers for this support, the bind variable support in the database itself should work back to at least Oracle 7.3.

• The Problem
• CFQUERYPARAM
• Bind Variables
• When to Use Bind Variables
• Stored Procedures
• Conclusion

The Problem

<CFQUERY DATASOURCE="DSN_NAME">
SELECT username
FROM users
WHERE user_id=#SESSION.USER_ID#
</CFQUERY>

Based on session variable, ColdFusion will substitute the current user's ID. (For this example, let's say his or her ID is 2236.). ColdFusion Server will then send the following code to the database:

SELECT username FROM users WHERE user_id=2236

The Oracle database parses this statement and determines the best execution path. An execution path is the process that Oracle should follow in getting the query data. For example, it might notice a primary key index on users.user_id and opt to use that index.

On the other hand, it may notice that while that index does exist, the table has few enough rows that it's actually faster to table scan the base data table rather than performing index reads. Either way, it will then read the data table. The execution path it finds is almost always optimal, but it takes time to find it.

As you might imagine, the developers at Oracle realized this would be a performance hit if it has to parse every query all the time. So, the database has a cache of queries and optimal execution paths.

The problem is, for speed and accuracy in lookups of this cache, the database finds a match (i.e., a cache hit) only if the statement matches exactly, down to the case of the fields in the query. This occurs behind the scenes, and you as a programmer have no control over it.

So, "SELECT count(*) from users" and "select count(*) from users" are two separate entries in the cache. The case-sensitivity part of these two statements is not a big concern. At Bantu.com, we have a standard capitalization procedure for queries (capitalize keywords, but not field names).

However, the part of the statement that varies (i.e., "(*)") can cause signification problems. For the CFQUERY statement above, substituting the SESSION.USER_ID is going to result in a lot of queries in the cache. Because each user has its own user ID, there will be a statement in the cache for each. This is not good.

To help solve this problem Allaire added the CFQUERYPARAM tag that passes information to databases, such as Oracle, as a bind variable.

CFQUERYPARAM

SELECT username FROM users WHERE user_id=:1

The ":1" is a substitution variable (parameter reference). The execution path is the same whether the userID is 2236 or 39393. Because there's no use in parsing it more than once, ColdFusion can use the cached query in the cache and thus conserve server resources.

To do this, you'd put the CFQUERYPARAM tag inline in the CFQUERY:

<CFQUERY DATASOURCE="DSN_NAME">
SELECT username
FROM users
WHERE user_id=<CFQUERYPARAM VALUE="#SESSION.USER_ID#" CFSQLTYPE="CF_SQL_NUMERIC">
</CFQUERY>

The CF_SQL_NUMERIC type will work for all integers and real numbers. (I've tested it with very large integers, and one place of decimals. On an e-commerce site, where decimal precision is more important, make sure to test if you're using decimal numbers with more than one place of precision).

If it's a character field, you should use CFSQLTYPE=CF_SQL_VARCHAR. There's something very important to note about character fields: do not enclose it in single quotes. If you make this mistake, Oracle will throw an error. For instance, the query:

<CFSET l_username="justin">
SELECT user_id
FROM users
WHERE logon_id='#l_username#'

would be converted to:

SELECT user_id
FROM users
WHERE logon_id=<CFQUERYPARAM VALUE="#l_username#" CFSQLTYPE="CF_SQL_VARCHAR">

Note there are no longer single quotes surrounding the username. You should also remove and fix any PreserveSingleQuotes function calls in your CFQUERY tags if you're converting them to bind queries.

The ColdFusion documentation makes mention of 20 different CFSQLTYPEs. None of them are explained very well, and I've found that CF_SQL_NUMERIC and CF_SQL_VARCHAR work just fine with Oracle.

You can use multiple CFQUERYPARAMs in the same statement for deletes, inserts, and updates. Regarding deletes and updates, examine the code below:

<CFQUERY DATASOURCE="DSN_NAME">
UPDATE users
SET settings_flag=<CFQUERYPARAM VALUE="#l_read_flag#" CFSQLTYPE="CF_SQL_NUMERIC">
WHERE user_id=<CFQUERYPARAM VALUE="#l_user_id#" CFSQLTYPE="CF_SQL_NUMERIC">
</CFQUERY>

Notice that I used a parameter in the actual value section (the SET statement) as well as in the WHERE clause. The only time you shouldn't do this is if you're always setting it to a constant value.

If you're doing date conversion, you need to force character-date conversion as a string bind variable. For example:

<CFSET l_In_date="#Now()#">
<CFQUERY name="qry_calendar" datasource="DSN_NAME">
SELECT event_id
FROM calendar
WHERE start_date > TO_DATE(<CFQUERYPARAM VALUE="#DateFormat(l_In_Date,"MMDDYYYY")#" 
CFSQLTYPE="CF_SQL_VARCHAR">,'MMDDYYYY')
</cfquery>

The line l_In_Date, which is a ColdFusion date data type, uses the ColdFusion function DateFormat to convert it to a specific string with format MMDDYYYY. Next, it tells Oracle to produce an Oracle data type out of a specified string format (also MMDDYYY).

Bantu.com doesn't use implicit date conversion because it's system dependent, and there are bugs in date conversion in the ColdFusion native database drivers. Since it's no longer a CF date data type once it's run through the DateFormat function, notice how the date is passed as a string.

Also note how single quotes are left off when passing it to TO_DATE. While it's possible to get implicit date conversion to work reliably and stably, I always use explicit date conversion. You may move the system to an Oracle database with a different default date format and that would cause lots of problems. As far as the performance hit, on the Oracle side TO_DATE is a very fast function. (It's actually what occurs behind the scenes anyway during an implicit date conversion).

Bind Variables

<CFQUERY DATASOURCE="DSN_NAME">
UPDATE users
SET settings_flag=<CFQUERYPARAM VALUE="" NULL="YES" CFSQLTYPE="CF_SQL_NUMERIC">
WHERE user_id=<CFQUERYPARAM VALUE="#l_user_id#" CFSQLTYPE="CF_SQL_NUMERIC">
</CFQUERY>

There are a few things to keep in mind when using null variables. The VALUE parameter must be an empty string. By empty string, I mean two sets of double quotes with nothing in between. If you have anything else in there, even just a blank space, it can throw an error.

Also, make sure to set NULL="YES" or it won't set it to NULL properly. Finally, make sure to use the proper CFSQLTYPE to match the field you're setting to NULL. This may seem a bit counter-intuitive because NULL has no data type, but you may run into problems if you don't.

When to Use Bind Variables

Unfortunately, you can't have ColdFusion cache a query that uses bind variables. Therefore, the CACHEDAFTER and CACHEDWITHIN parameters of CFQUERY aren't allowed if that query uses CFQUERYPARAM.

Stored Procedures

Before ColdFusion 4.5.1, the workaround to implement bind variables was to use stored procedures. You could code your database operation inside a stored procedure and then use CFSTOREDPROC and CFPROCPARAM. Because it received the information as parameters, Oracle can preparse the execution path because stored procedure queries in that format are done as bind variables. However, this isn't an easy option at all.

While it's nice to have all database operations encapsulated as stored procedures, this isn't realistic in most circumstances. Because the results need to be compiles into the database, it also requires a separate rollout process for stored procedures in addition to the existing rollout process for rolling out new CFML pages. On top of that, Oracle makes it much harder to return a query result set from a stored procedure.

Conclusion

Before Bantu.com started using bind variables, our databases had serious performance problems. Even though we had a 500MB SQL cache area in our database, it would use lots of CPU parsing execution plans, and the SQL cache would be full of execution plans for queries that were run rarely (per-user queries).

With CFQUERYPARAM, we can use bind variables so the SQL cache area is never full, and it contains entries that are frequently used because it's in bind variable format.

About the Author

As Chief Technology Officer, Justin Fidler oversees all aspects of technology infrastructure, systems development and operations at Bantu, Inc., the leading provider of Web-based instant communication to businesses, ISPs and Web sites. Founded in 1999, Bantu operates one of the largest interoperable instant messaging networks reaching more than 85 million users.

Justin was also CTO at IntraACTIVE, Inc., the predecessor company to Bantu, Inc., where he was responsible for the development of SiteKnowledge and InTandem, Web-based groupware products. Prior to IntraACTIVE, Inc. Justin developed systems for Oracle, NASA, the Department of Defense, and washingtonpost.com.

Justin holds a bachelor's degree in Information and Decision Systems from Carnegie Mellon University. He holds certifications from Allaire, Microsoft, and Novell.