New QueryRun() + QueryOfQueryRun() functions

Of all the things you most want to do buried deep within a CFSCRIPT block is manipulate databases. Invoking a tag while in script mode is impossible, unless you build your own CFC wrapper, which is clunky and horrendously inefficient.

OpenBlueDragon has just released the first couple of native functions that let you get in and around the power of queries but from a script.

• QueryRun()
• QueryOfQueryRun()

These operate just like CFQUERY but at a script level. So let us look at how you can use them. In the basic form, you can simply pass in a datasource and a SQL statement and it will return a standard query object.

<cfscript>
if ( QueryRun(ds,"select 1 from table where x=2").recordcount eq 0 ){
  //do something
}
</cfscript>

Okay, all fairly straightforward thus far, but what about doing something that requires a little bit more control over the variables to which you build up the string. In other words how do you CFQUERYPARAM your statement?

That is easy. You mark in your SQL statement where you wish your variable to be placed, using the ? character. Then you pass in an array of structs that describe each of the variables you wish to use.

Assume for sake of example, you wish to query between two dates, the code to do that would be:

<cfscript>

var sql = "select * from table1 where date1 > ? and date1 < ?";
var params = ArrayNew();
params[1] = StructNew();
params[1].value = CreateDate( 2007, 1, 1 );
params[1].cfsqltype = "CF_SQL_DATE";
params[2] = StructNew();
params[2].value = CreateDate( 2010, 1, 1 );
params[2].cfsqltype = "CF_SQL_DATE";

var qry = QueryRun( ds, sql, params );
</cfscript>

You can use all of the attributes available to CFQUERYPARAM to describe your variables, and you must have at least one structure in the array per ? in your SQL statement.

It is that simple. Performing the same thing with QueryOfQuery's, is used with the sister function, QueryOfQueryRun(), which is the same, minus the datasource parameter at the start.

This, long overdue, addition to the core language, will enable much tighter and faster CFC's to be written including removing all the hacks away that framework developers have had to do to provide database connectivity.

Available from today in the nightly build.