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.
