Query service in cfscript

I still love the good old CFQUERY tag as it made querying databases really easy back in 1995. Its simplicity made Coldfusion shine compared to PHP, Java… whatever else. Good old days. Frankly speaking I do not use it much nowadays preferring Hibernate to handle data access and manipulation. I am one of the dudes who believes that ORM rocks despite its obvious overhead. There are, however, situations when you prefer to use raw SQL instead of ORM. Reporting that uses complex joins is just one of such situations as we query a slaved reporting database directly instead of accessing it through the ORM layer. It is faster and you can be much more precise selecting exactly the columns you need instead of using a Hibernate equivalent of SELECT * FROM … But all your components are tagless. How does it work then?

I’ve been rather stupid ignoring the new Query service introduced in Coldfusion 9. It rocks.
The simplest query in cfscript looks like this:

var queryService = new Query(); 
    queryService.setSQL("SELECT * from TABLENAME");
var qryResult = queryService.execute().getResult();

Variable qryResult is a ubiquitous Coldfusion query object containing all the usual properties such as the recordset, the SQL statement etc. Query service behavior resembles the behavior of the CFQUERY tag in details. It accepts any valid SQL and executes it. Let’s explore the Query service in details.

Datasource
Set a new datasource that differs from the one defined in your Application.cfc

var queryService = new Query(); 
    /* It is probably a good idea to parametrize the DSN name, I use "newDatasourceName" for the sake of simplicity*/
    queryService.setDataSource("newDatasourceName");
    queryService.setSQL("SELECT * from TABLENAME");
var qryResult = queryService.execute().getResult();

Query parameters
Query parameters are the cfscript equivalents of the good old CFQUERYPARAM tag and should be used always to verify the datatype of your parameter, to speed up your queries and to prevent a possibility of SQL injections.

var queryService = new Query(); 
    queryService.addParam(name="dept",value="marketing",cfsqltype="cf_sql_varchar"); 
    queryService.setDataSource("newDatasourceName");
    queryService.setSQL("SELECT * from TABLENAME WHERE dept=:dept");
var qryResult = queryService.execute().getResult();

Query attributes
setAttributes() method sets attributes for the Query service. It accepts all legacy CFQUERY parameters in a name=value form. Let’s sprinkle out Query service with some attributes:

var queryService = new Query(); 
    queryService.setAttributes(maxrows=100, 
              blockFactor=25,
              cachedwithin=CreateTimeSpan(0, 3, 0, 0),
              debug=true,
              timeout=5,
              username='sales',
              password='MaDiSoNaVeNuE'
    ); 	
    queryService.addParam(name="dept",value="marketing",cfsqltype="cf_sql_varchar"); 
    queryService.setDataSource("newDatasourceName");
    queryService.setSQL("SELECT * from TABLENAME WHERE dept=:dept");
var qryResult = queryService.execute().getResult();

Query name
Every animal needs a name. So are your queries. At least that is what Adobe suggests:

var queryService = new Query(); 
    queryService.setName("simpleQry");
    queryService.setDataSource("newDatasourceName");
    queryService.setSQL("SELECT * from TABLENAME");
var simpleQry = queryService.execute().getResult();

I think the setName() method is useless as you can assign query results to any variable explicitly anyways and because the query name attribute is ignored by Coldfusion unless you explicitly assign it. As an alternative you can also pass the name parameter to the setAttributes() method.

Query caching
Query caching allows you to persist query results in memory to avoid query re-execution within a given timespan. Please note that you can also set caching passing cachedwithin or cachedafter parameters to the setAttributes() method instead.

var queryService = new Query(); 
    queryService.setcachedwithin(CreateTimeSpan(0, 3, 0, 0)); 
    queryService.addParam(name="dept",value="marketing",cfsqltype="cf_sql_varchar"); 
    queryService.setDataSource("newDatasourceName");
    queryService.setSQL("SELECT * from TABLENAME WHERE dept=:dept");
var qryResult = queryService.execute().getResult();

Transactions
You can use group several queries in a transaction if your database supports transactions. Your data changes will be commited after the closing } if you don’t specify otherwise.

transaction {
 
    var queryService = new Query(); 
        queryService.addParam(name="dept",value="marketing",cfsqltype="cf_sql_varchar"); 
        queryService.setSQL("INSERT INTO TABLENAME (dept) VALUES (:dept) ");
        queryService.execute();
 
        queryService = new Query(); 
        queryService.setSQL("SELECT TOP 1 * from TABLENAME ORDER BY id DESC);
    var qryResult = queryService.execute().getResult();
 
}

P.S. This functionality is defined by a component query.cfc that can be find in /ColdFusion9/CustomTags/com/adobe/coldfusion/ directory. Open it and explore its methods – it’s all in clear. Have a good day!