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 …

Running a Django Application on Windows Server 2012 with IIS

This is a first for me since under normal circumstances we run all our Django applications on Linux with Nginx, but we're in the process of developing an application for another department and due to the requirements around this project, we'll be handing the code off to them to deploy. They don't have any experience with Linux or web servers other than IIS, so I recently took up the challenge of figuring out how to run Django applications on Windows Server 2012 with IIS.

Based on the dated or complete lack of information around this I'm assuming it's not something that's very common in the wild, so I thought I'd share what I came up with in case others need to do this.


This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

Assumptions and CaveatsThe operating system is Windows Server 2012 R2, 64-bit. If another variant of the operating system is being used, these instructions may not work properly.All of the soft…

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 …