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

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…

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 …

Fixing DPI Scaling Issues in Skype for Business on Windows 10

My setup for my day job these days is a Surface Pro 4 and either an LG 34UC87M-B or a Dell P2715Q monitor, depending on where I'm working. This is a fantastic setup, but some applications have trouble dealing with the high pixel density and don't scale appropriately.
One case in point is Skype for Business. For some reason it scales correctly as I move between the Surface screen and the external monitor when I use the Dell, but on the LG monitor Skype is either massive on the external monitor, or tiny on the Surface screen.
After a big of digging around I came across a solution that worked for me, which is to change a setting in Skype's manifest file (who knew there was one?). On my machine the file is here: C:\Program Files\Microsoft Office\Office16\LYNC.EXE.MANIFEST
And the setting in question is this:
<dpiAware>True/PM</dpiAware>
Which I changed to this: <dpiAware>False/PM</dpiAware>
Note that you'll probably have to edit the file as administr…