Learning ColdFusion 9: Using CFQuery (And Other Service Tags) In CFScript
Posted August 6, 2009 at 2:19 PM
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:
Launch code in new window » Download code as text file »
- 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:
Launch code in new window » Download code as text file »
- 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:
Launch code in new window » Download code as text file »
- 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.
Launch code in new window » Download code as text file »
- <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.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Other Searches | Print Page
Newer Post
Using A Proxy To Give Any Component Method-Chaining Abilities In ColdFusion
Older Post
Assigning Variables Within A CFLoop Condition In ColdFusion
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.
