Learning ColdFusion 9: Using CFQuery (And Other Service Tags) In CFScript
Posted August 6, 2009 at 2:19 PM by Ben Nadel
A number of people have already blogged about using CFQuery in CFScript with ColdFusion 9's CFScript enhancements; but, I thought it would be fun to do my own experimentation. In ColdFusion 9, service-based tags such as CFQuery are executed in CFScript as ColdFusion components. And, if you look in the custom tags installation folder, you can actually look through these service CFCs. If you have some time, I would recommend it as the architecture is very interesting. Ultimately, these ColdFusion service CFCs are just beefy wrappers for their corresponding CF tags.
These ColdFusion service objects make heavy use of the implicit getters and setters that ColdFusion 9 has implemented. If you look at the Query.cfc code, you will see that it has these properties defined at the top:
- property string name;
- property numeric blockfactor;
- property string cachedafter;
- property string cachedwithin;
- property string dataSource;
- property string dbtype;
- property boolean debug;
- property numeric maxRows;
- property string password;
- property string result;
- property numeric timeout;
- property string username;
- property string sql;
These are all the tag-level attributes that you can define with the implicit getters and setters. When you create a Query.cfc instance and call its constructor (explicitly or implicitly using the NEW operator), you can pass in any number of named arguments:
- new Query( sql = "SELECT * FROM girl" )
Because these ColdFusion service objects are made generic enough to work across services, pretty much all method calls to these objects must be made using name-based arguments, not ordered arguments.
In addition to the tag-level attributes, these ColdFusion service objects can take parameters. Parameters are the CFScript-equivalents of the child tags that would be associated with the parent ColdFusion tags. So, for Query.cfc, we can add parameters to represent our CFQueryParam tags. To add a param, use the AddParam() method:
- myQuery.addParam(
- name = "name",
- value = "Joanna",
- cfsqltype = "cf_sql_varchar"
- )
As with the constructor method, the AddParam() method uses named-arguments, not ordered arguments. This example here uses named query params (name argument). You can also used ordered query params by excluding the name argument from the addParam() method call; but, I think named query params are far superior for readability and maintainability, so I won't bother showing you ordered params.
Once you have all of your tag attributes and child parameters defined, you can execute the query using the Execute() method:
- results = myQuery.execute()
This method doesn't actually return the query itself - it returns a result object that contains the query and the data that would normally be stored in the tag-based scope.
Because the ColdFusion service objects are so generic, there's not really that much more to cover. We'll probably get the most out of this by looking at some examples. In the following demo, I'm going to query for a girl, insert two girls, and then re-query for the first girl again.
- <cfscript>
-
- // Create a new query service object. This can be used for
- // a single query or repeatedly for multiple queries.
- getJoanna = new Query();
-
- // Set the SQL statement. Becuse ColdFusion allows us to
- // wrap strings, we can keep the nice SQL formatting that we
- // are used to having in CF tags.
- //
- // NOTE: In this query, we are using the named CFQueryParam
- // for the girls name.
- getJoanna.setSql("
- SELECT
- id,
- name,
- hair
- FROM
- girl
- WHERE
- name = :name
- ");
-
- // Set the Query Param for our "name" query variable. If
- // you were not using named variables in the query (you
- // would be using ? for positional parameters), then you
- // would not include "name" argument in this method call.
- getJoanna.addParam(
- name = "name",
- value = "Joanna",
- cfsqltype = "cf_sql_varchar"
- );
-
- // Execute the query and store the results.
- joanna = getJoanna.execute();
-
- // Dump out the results.
- writeDump( joanna );
-
-
- // ------------------------------------------------------- //
- // ------------------------------------------------------- //
-
-
- // Create a new query service object to insert the girls.
- // While our first query was specific, this one we will
- // keep generic to use multiple times.
- insertGirl = new Query();
-
- // Set the new SQL to insert a girl.
- //
- // NOTE: There is a single space character after each
- // query param. It looks like the query strips out TABS from
- // the SQL statement and will corrupt he named params.
- insertGirl.setSql("
- INSERT INTO girl
- (
- name,
- hair
- ) VALUES (
- :name ,
- :hair
- )
- ");
-
- // Set the query params for our insert.
- insertGirl.addParam(
- name = "name",
- value = "Joanna",
- cfsqltype = "cf_sql_varchar"
- );
-
- // Set the query params for our insert.
- insertGirl.addParam(
- name = "hair",
- value = "Brown",
- cfsqltype = "cf_sql_varchar"
- );
-
- // Insert Joanna.
- insertGirl.execute();
-
-
- // Clear the params because we are going to add another
- // girl using the same query service.
- insertGirl.clearParams();
-
- // Set the query params for our NEXT insert.
- insertGirl.addParam(
- name = "name",
- value = "Tricia",
- cfsqltype = "cf_sql_varchar"
- );
-
- // Set the query params for our NEXT insert.
- insertGirl.addParam(
- name = "hair",
- value = "Brown",
- cfsqltype = "cf_sql_varchar"
- );
-
- // Insert Tricia.
- insertGirl.execute();
-
-
- // ------------------------------------------------------- //
- // ------------------------------------------------------- //
-
-
- // Now that have inserted Joanna, we are going to use are
- // existing Joanna query to re-execute the select.
- joanna = getJoanna.execute();
-
- // Dump out the results.
- writeOutput( "<br />" );
- writeDump( joanna );
-
-
- </cfscript>
When we run this code, we get the following output:
| | | | | |
| |  | | ||
| | | |
In the above code, there's a few key points to take away. For starters, when we create a Query.cfc instance, we aren't executing a ColdFusion query; we're creating a service wrapper for executing queries. This is an important distinction as it allows us to be able to use the same Query.cfc instance multiple times. You can use it more than once, as-is, by simply calling Execute() more than once (as we are doing with our getJoanna query); or, you can reset some - or all - of the Query.cfc properties before calling Execute(), thereby running an entirely new query from the existing object.
To reset ColdFusion service object properties you have a few options:
- Clear() - This will clear all of the tag level attributes and child params.
- ClearParams() - This will clear all of the child params, leaving the tag-level attributes as-is.
- ClearAttributes() - If called with no arguments, this will clear all of the tag-level properties. If called with a list of tag attributes (as a single argument), it will clear those tag-level properties.
Another big take away from this is that ColdFusion allows us to wrap string values across multiple lines. This means that you can still write well formatted SQL statements within a string value. Come on people, let's not devolve our coding style back to the days of ASP where we had huge SQL statements all one line! Don't do that... don't be that guy.
When it comes to adding query params to our query statement, you can see that I use named-params as denoted by the colon:
WHERE name = :name
The name of the query param is all non-space characters that follow the colon, which in this example, is "name". To assign that query param value, we have to add a query param with the given name using AddParam(). When working with query params, there is one huge caveat: you must have a space character (or end of string) after your named parameter! If you do not, then ColdFusion will not be able to parse out the named parameter properly and will throw a ColdFusion error like:
Named Sql parameter 'name,' used in the query not found in the query params. The SQL specified in the query:
INSERT INTO girl ( name, hair ) VALUES ( :name, :hair ).
As you can see, because my comma comes directly after the query param, ":name," ColdFusion thinks that the comma is part of the query param name. NOTE: This is a bug caused by the fact that they are parsing the query as a delimitted list, and not using a regular expression. Note also that this same error occurs if you try to use tabs or line breaks after your query param - it must be a space character!
Once you figure out the little caveats, the ColdFusion service objects all operate in a very similar manner. I think there's a sort of awesomeness to the fact that CFScript was augmented using native features of the language rather than compiler updates or something. It really demonstrates how powerful ColdFusion really is.
Reader Comments
Wow. That's even uglier code than I had feared. Welcome back to 1996 and writing queries in VBScript/ASP.
I'd be curious to see if this broke things:
SELECT 'I am not a :param' FROM foo WHERE name = :name
@Rick,
It funny you bring that up because I actually had an even worse thought when I was reviewing the CFC architecture. It uses PerserveSinqleQuotes() to write the code inbetween the CFQueryParam tags. My concern would be that this would open us up to SQL Injection attacks. I'll do some testing on that tonight, but I am pretty sure it will, depending on how the queries are build.
Hi Ben
Did you raise this bug with Adobe? I can't find anything similar in the bug tracker..?
@Rick
What's your gripe with this approach? How would you implement functionality for executing a query within a script block which would improve on this?
Personally I think the syntax is fine; the point at which this script-based stuff loses out to tags is when one might want to embed some logic into the body of the <cfquery> tag to dynamically build the SQL string. But that's just one of the trade-offs when using script, I guess; it's ont exactly a major one, either. I like being able to be able to do a query - should I need to - in script, rather than having to drop out of script to do it in a tag. Now *that* makes for godawful code.
I think you both might have a point about how possibly easy the current implementation might be to trick with the param names though. From what you say, Ben, the implementation didn't go through much thought (or at least "enough ~") before being put out there. The issue you've found is a bit "obvious", after all.
--
Adam
@Adam,
I have not logged it yet. I wanted to do a bit more testing to see one other thing, but then I will log it.
My concern is that I'm not sure if it's a problem with the way the Query.cfc object is built... or, is it the way ColdFusion compiles string values with line breaks?
What I would like is the ability to transition seamlessly between CFML and CFScript forward as well as backwards. So that, for example, we can do this:
component {
cfml {
<cfquery name="variables.people">
select * from Person
</cfquery%gt;
}
function init() {
variables.peopleCount = people.RecordCount;
}
}
@Justice,
Word up! I would like a <cftags> tag to break out of script like <cfscript> breaks into script.
Adam-
Reference: http://rickosborne.org/blog/index.php/2009/05/19/cf9-e4x-c4x/
One suspicion I had about SQL Injection is rare cases is true:
You can fix the query param bug by extending the Query.cfc object:
Thanks Ben, this example helped me figure out what I needed to get http requests working in cfscript... not that I like it or it's all that elegant, but it works and my example required it. Thanks again.
@Ted
By "it" I'm referring to Adobe's approach. Not sure how they could've handled it differently, but if I wasn't such a purist (all script or none on a page) I'd agree with @Justice; seems it would be cleaner and less code... which I assume is one of the cfscript goals.
@Ben: Regarding Rick's and Justice's comments above, did you see my recent blog entry on XML and CFML tag literals for cfscript?
I pinged you on twitter but you might have missed it :) Just wondering what you think!
@Justin,
I actually have it open as a Tab in Firefox... meaning to get to it. On brief skim, it looks very interesting. But, I'll get you better feedback later today.
Ok, cool stuff ;)
Is it just me or do you need spaces after your named :params?
For example:
myQry = new query();
myQry.setSql('
select id
from table
where id = :id
and deleted = 0
');
myQry.addParam( name="id", value=0, cfsqltype='cf_sql_integer' );
myQry.execute();
If my :id is followed by a line break, it throws an error saying:
"Named Sql parameter 'id and' used in the query not found in the queryparams"
Replacing the line break with a tab gives the same error.
If I inject a space before the line break / tab, it works. It seems the cf9 query cfc params regex is looking specifically for spaces, rather than any whitespace character(s).
If I replace the ":id" param with a "?" - ie. use positional params - it works fine with line breaks / tabs and no trailing space.
@Mike,
Yeah, you do need spaces sometimes after your named params. It's an unfortunate bug in the way the Query.cfc was built. I have a blog post on how to extend the Query.cfc component in order to get around it. If you are interested, it's posted in the comments above.
Thanks for the info. Here's my first test script using this query in cfscript stuff, maybe useful to someone.
- <cfscript>
- setSelections = 0;
- transid = 5;
- limited = "";
- ordered = "";
- userIDSelect = "";
- transTypeSelect = "";
- if(setSelections){
- limited = "limit 50";
- userIDSelect = "and userid = #oUser.getUserID()#";
- }else{
- ordered = "order by transactiondate desc";
- transTypeSelect = "and transactionTypeID = :transID ";
- }
- queryServ = new Query();
- queryServ.setName("transactions");
- queryServ.setDatasource("maindata");
- queryServ.setSQL("
- select *
- from transactions
- where 1=1
- #transTypeSelect#
- #userIDSelect#
- #ordered#
- #limited#
- ");
- queryServ.setMaxRows(20);
- queryServ.setResult("transactionsResult");
- if(len(transTypeSelect)){
- queryServ.addParam(
- name = "transID",
- cfsqltype = "cf_sql_integer",
- value = transid
- );
- }
- result = queryServ.execute();
- </cfscript>
Edit the 'setSelections' boolean for using dynamic addParam's or whatever. I was mostly trying to see if I could use dynamic queries in cfscript. Give it a play.
@Clint,
It's been a while since I've used CFC-based queries, so I am losing you at this point. What is this line for:
queryServ.setResult("transactionsResult");
Why are you setting this value right before you call execute()?
@Ben
Like I said, I was playing around with this stuff, my first attempt at queries in cfscript fashion. So actually I hadn't found a use for either setResult() or setName() yet. Neither of those seem to do anything useful so any light you can shed on it would illuminate my limited experience.
Cheers
@Clint,
In all honesty, my experience with script-based (or rather CFC-based) queries is not much more than experimental; I'm one of the people who code more in tags than script.
I just cracked open the CFCs to take a little refresher on how they run.
Ultimately, the all of these CFCs are just wrappers around the native Tag versions with a whole lot of using AttributeCollection to actually invoke the tags. When they get invoked, they store the result into a standardized TagResult object that has two properties: Prefix, Result.
This TagResult object gets built internally when you execute the primary tag (CFQuery, CFHTTP, etc.) - you won't have to worry about calling or creating that yourself; the Result value will be set as part of the internal logic.
It's this TagResult object that gets returned to the calling context (your code).
Now, what's ironically named is that the native CFQuery "Result" attribute actually becomes the TagResult's "Prefix" property and the query value becomes the TagResult's "Result" property :) Which can be a little confusing, to say the least.
Phew... I just find all of this easier in Tag-based queries :)
Thanks for your time, good information.
Ever since CF 9 came out I'm finding myself scripting everything as more tags become scriptable. Guess I'm just comfortable with it. Having to break out of cfscript to do anything tag based seems like an intrusive break in my flow. The only place I like to use tags now is in the view, whereas controllers, services, gateways, and beans are more logical in pure script form. I guess it just comes down to personal preference, plus for me it makes OO more native and intuitive. Or maybe it has something to do with all the Flex and Actionscripting as I do a lot of Flash integration work nowadays.
I like tagging and it's simplicity in power is what got me hooked in the first place, but as I become more seasoned in OO and patterns I guess I'm becoming more of a script purist. I'd never want to do away with tags though, scripting in the view just doesn't work. Seems to me then that tagging is for designing and scripting is for developing. Plus my staple framework, ColdBox, with the new 3.0 version is almost all script based and it kind of makes me work in that direction.
@Clint,
It's probably something that I would get used to if I just tried to stick to it a bit more. After all, I write script in Javascript which is where I spend a good deal of my time.
Probably what holds me back the most is that I still use CF8 in production so I can't truly use full CFScript in any active project just yet. Once we make the switch, I can start to think of that as more of a possibility.
Ben,
Since the Adobe docs are clear as mud. Wonder if you could shed light on using valueList with the result from query script. I can't seem to make it work. So I did the following. The first line works fine and is really what I want, but have been experimenting. Another interesting thing CF Builder throws error on second line.
instance.user.systemAudiences.addAll(systemAudiences.getResult()['SystemAudienceID']);
instance.user.systemAudiences = valueList(systemAudiences.getResult()['SystemAudienceID']);
@Kevin
try this:
query = new query(...);
instance.user.systemAudiences = [];
instance.user.systemAudiences.addAll( query.getResult().getColumn('SystemAudienceID') )
.getColumn() supports case insensitive column name or integer offset (1=first column)
be sure to javaCast('int',offset) as it only works with integers - not strings or doubles as cf usually uses.
@Kevin,
When dealing with valueList(), try using dot-notation instead of array notation. I know that sounds crazy, but I think they do slightly different things under the covers:
valueList( systemAudiences.getResult().SystemAudienceID );
See if that helps at all.
@Ben - only downside to that is your query columns must then be syntactically correct variable names.
ie. you cant have a column (or alias) with spaces
the .getColumn('column name') on the g.etResult() works around this limitation
@Mike,
This is true; but, I find it to be one of those things that perhaps helps you out in the long run. Having spaces in your column names is probably going to complicate all aspects of working with queries.
