Skip to main content

CFQUERYPARAM Reuse With QueryRun() in Open BlueDragon

I'm working on an application with a friend of mine and we're taking full advantage of all the great new functions and features in Open BlueDragon. One of the new functions I wasn't sure I'd use all that much is QueryRun(), which lets you run a query in a CFSCRIPT block like this:

<cfscript>
  QueryRun(datasource, sqlStatement, queryParams);
</cfscript>

Let's look at a concrete example. Assume a table called "user" with fields of id, email, first_name, and last_name, and a datasource named "myDSN". To pull all the users, you'd do this:

<cfscript>
  getUsersSQL = "SELECT id, email, first_name, last_name FROM user";
  users = QueryRun("myDSN", getUsersSQL);
</cfscript>

Now if you want to pull a specific user--let's say by email--you can parameterize your queries by using an array of structs that represent the CFQUERYPARAM tags, where the key in each struct is the attribute name from CFQUERYPARAM, and the value for each key is the value of the CFQUERYPARAM attribute. Things start looking a bit Java-esque with the question marks, but here's how that works (and note use of the newly added implicit array and struct notation):

<cfscript>
  getUsersSQLParams = [{value = "matt@mattwoodward.com", cfsqltype = "cf_sql_varchar", maxlength = 100}];
  getUsersSQL = "SELECT id, email, first_name, last_name FROM user ";
  getUsersSQL &= "WHERE email = ?";
  users = QueryRun("myDSN", getUsersSQL, getUsersSQLParams);
</cfscript>

Let's go over what's going on here. First, in the struct contained in the getUsersSQLParams array, you can see the familiar attributes of value, cfsqltype, and maxlength from CFQUERYPARAM. Next, notice the ? in the SQL statement. Any question marks in your SQL statement will get replaced in the order in which they appear by the structs contained in the SQL parameters array. So in this case, that ? in the SQL statement essentially becomes a CFQUERYPARAM with the key/value pairs in the struct defined two lines above.

To reinforce this point, let's look at an example pulling a user by first name and last name:

<cfscript>
  getUsersSQLParams = [{value = "Matt", cfsqltype = "cf_sql_varchar", maxlength = 100},
                    {value = "Woodward", cfsqltype="cf_sql_varchar", maxlength = 100}];
  getUsersSQL = "SELECT id, email, first_name, last_name FROM user ";
  getUsersSQL &= "WHERE first_name = ? AND last_name = ?";
  users = QueryRun("myDSN", getUsersSQL, getUsersSQLParams);
</cfscript>

In that case I have two parameters in my SQL statement represented by two question marks, the first of which is replaced by the first struct in the SQL parameters array (corresponding to first_name), and the second is replaced by the second struct in the array (corresponding to last_name).

While working on this application I came across an added bonus with this way of doing things that I didn't really consider when the feature was first added to Open BlueDragon. Since your query parameters are stored in an array of structs as opposed to being added to each query as individual CFQUERYPARAM tags, you can re-use the query parameters across multiple queries.

When would this come in handy? Consider basic CRUD operations, specifically inserts and updates. In many cases the only difference between an insert and update is a "WHERE id = ?" clause, but using CFQUERY you wind up having to repeat a ton of CFQUERYPARAM tags between the two queries.

By using QueryRun() and the array of structs that represent the query parameters, these parameters can be reused between an insert and an update operation. This example is fairly trivial since the table is so small, but you can imagine how much redundancy you save on large tables.

<cffunction name="save" access="public" output="false" returntype="void">

  <cfargument name="user" type="User" required="true" />

  <cfscript>
    var commonSQLParams = [{value = arguments.user.getEmail(), cfsqltype = "cf_sql_varchar", maxlength = 100},
                           {value = arguments.user.getFirstName(), cfsqltype = "cf_sql_varchar", maxlength = 100},
                           {value = arguments.user.getLastName(), cfsqltype = "cf_sql_varchar", maxlength = 100}];

    // if the ID is 0 do an insert, otherwise do an update
    if (arguments.user.getID() == 0) {
      var insertUserSQL = "INSERT INTO user (email, first_name, last_name) ";
          insertUserSQL &= "VALUES (?, ?, ?)";
      var insertUser = QueryRun("myDSN", insertUserSQL, commonSQLParams);
    } else {
      ArrayAppend(commonSQLParams, {value = arguments.user.getID(), cfsqltype = "cf_sql_integer"});
      var updateUserSQL = "UPDATE user SET email = ?, first_name = ?, last_name = ? ";
          updateUserSQL &= "WHERE id = ?";
      var updateUser = QueryRun("myDSN", updateUserSQL, commonSQLParams);
    }
  </cfscript>
</cffunction>

Since the update statement only needed the one additional WHERE parameter of the ID, I can just append that to the end of the SQL parameters array I created from the data in the user object and re-use the rest of the parameters for both queries.

Personally I think that's pretty darn slick, and although at first the ? notation may seem a bit odd at first (at least if you aren't used to doing things that way in Java), you wind up with some very concise code when using QueryRun() due to the ability to reuse the SQL parameters across multiple queries.

Comments

arwilliamson said…
okay, i admit it, that is pretty darn slick - i didn't even think of that use case, which is probably the best use case one could possibly think of!

Popular posts from this blog

Installing and Configuring NextPVR as a Replacement for Windows Media Center

If you follow me on Google+ you'll know I had a recent rant about Windows Media Center, which after running fine for about a year suddenly decided as of January 29 it was done downloading the program guide and by extension was therefore done recording any TV shows.

I'll spare you more ranting and simply say that none of the suggestions I got (which I appreciate!) worked, and rather than spending more time figuring out why, I decided to try something different.

NextPVR is an awesome free (as in beer, not as in freedom unfortunately ...) PVR application for Windows that with a little bit of tweaking handily replaced Windows Media Center. It can even download guide data, which is apparently something WMC no longer feels like doing.

Background I wound up going down this road in a rather circuitous way. My initial goal for the weekend project was to get Raspbmc running on one of my Raspberry Pis. The latest version of XBMC has PVR functionality so I was anxious to try that out as a …

Setting Up Django On a Raspberry Pi

This past weekend I finally got a chance to set up one of my two Raspberry Pis to use as a Django server so I thought I'd share the steps I went through both to save someone else attempting to do this some time as well as get any feedback in case there are different/better ways to do any of this.

I'm running this from my house (URL forthcoming once I get the real Django app finalized and put on the Raspberry Pi) using dyndns.org. I don't cover that aspect of things in this post but I'm happy to write that up as well if people are interested.

General Comments and Assumptions

Using latest Raspbian “wheezy” distro as of 1/19/2013 (http://www.raspberrypi.org/downloads)We’lll be using Nginx (http://nginx.org) as the web server/proxy and Gunicorn (http://gunicorn.org) as the WSGI serverI used http://www.apreche.net/complete-single-server-django-stack-tutorial/ heavily as I was creating this, so many thanks to the author of that tutorial. If you’re looking for more details on …

The Definitive Guide to CouchDB Authentication and Security

With a bold title like that I suppose I should clarify a bit. I finally got frustrated enough with all the disparate and seemingly incomplete information on this topic to want to gather everything I know about this topic into a single place, both so I have it for my own reference but also in the hopes that it will help others.Since CouchDB is just an HTTP resource and can be secured at that level along the same lines as you'd secure any HTTP resource, I should also point out that I will not be covering things like putting a proxy in front of CouchDB, using SSL with CouchDB, or anything along those lines. This post is strictly limited to how authentication and security work within CouchDB itself.CouchDB security is powerful and granular but frankly it's also a bit quirky and counterintuitive. What I'm outlining here is my understanding of all of this after taking several runs at it, reading everything I could find on the Internet (yes, the whole Internet!), and a great deal…