For those that don't know, I am a cfscript nut. I write almost everything in script. I find it much better to read, understand and it just flows better.

So, you can only imagine my excitement when I found out about the ColdFusion 9 enhancements to cfscript. I had been creating my own functions for a while to add functionality to cfscript. But now they have gone and done it for me.

So, here is a first in a series of code examples for cfscript. This example I will focus on running queries. One thing you will notice in the example code that I don't define a datasource. This is because the datasource is defined in application.cfc. This is done by adding "this.datasource = 'cfbookclub';" to your application.cfc.

All these examples use the "cfbookclub" sample datasource that is instlled with CodFusion.

There are multiple ways to run a query inside cfscript.

This example runs a simple query and dumps the result:

view plain print about
1<cfscript>
2    myQry = new Query(); // new query object
3
    myQry.setSQL("select bookid, title, genre from app.books"); //set query
4
    qryRes = myQry.execute(); // execute query
5
    writedump(qryRes.getResult().recordcount, true);// get resultcount
6
    writedump(qryRes.getResult(), false); // dump result
7
</cfscript>

As you can see by following the comments it is pretty straight forward: Create Object, set sql, execute. I think that the challenging part is getting the result out. This is not as obvious as you would think. Looking at the code you might think that "qryRes" is the query result. Well this is partially accurate. It does "contain" the result set but you have to get it out. That is what the "qryRes.getResult()" is for. This gets the actual query result that you would normally get back from cfquery.

This example runs the same query but handles it a little differently.

view plain print about
1<cfscript>
2    myQry = new Query(); // new query object     
3
    qryRes = myQry.execute(sql="select bookid, title, genre from app.books").getResult();    
4    writedump(qryRes.recordcount, false); // get resultcount
5
    writedump(qryRes, false); // dump result
6
</cfscript>

As you can see in this example I set the sql statement as a param for the execute statement. I also use chaining and add the "getResult()" to the sequence. This will set the "qryRes" to be the query result; same result as cfquery.

In this example I take what I have learned from the other 2 examples and merge it all together.

view plain print about
1<cfscript>
2    myQry = new Query(datasource="cfartgallery", sql="select artid, artname, price from app.art").execute().getResult();
3    writedump(myQry.recordcount, false); // get resultcount
4
    writedump(myQry, false); // dump result
5
</cfscript>

This example I use chaining again. This allows me to basically do what I was doing before in multiple lines of code in a single line. I find this to be very efficient way to do it. However, if you are going to run multiple queries this would not be a good way to do it. This is because you would be creating a query object for every query instead of creating one object and reusing it.

So there ya have it, cfquery inside cfscript. Very quick and very easy. Granted, there is a bunch more you can do with it beyond these examples. The examples are intended to just give you a starting point to work off of.

Till next time...

--Dave