Gotches with Queries in Script

This post is more than 2 years old.

One of the features of ColdFusion 9 that I've haven't used quite a lot yet is the ability to write queries in script. It seems like everything I've done in CF9 database related used ORM. So when I finally had to revert to using a real query (ok, I know Hibernate uses real queries, but you get my meaning here) I ran into a few interesting issues right away. I've hit two issues I think people should be aware of - one serious - one a bit more subtle. First off, a quick shout out to Ben for his excellent blog article on the feature in general. I found that quicker than I found any documentation in the "official" docs.

Issue One

The first issue I ran into was a bit surprising. Surprising in that no one else had run into it and reported it yet. When using bound parameters you can either use positional or named parameters in your query. Positional parameters I find to be a bit hard to read. Any complex query with more than a few of these will be difficult to work with (imho), so I almost always use named parameters (well when I say almost always, I mean when I've used script based queries in other languages, like AIR). It also saves you some typing. If you want to search against multiple columns for example, using a named parameter means you only have to specify the parameter once:

<cfscript> q = new com.adobe.coldfusion.query(); q.setDatasource("cfartgallery"); q.setSQL("select * from art where artname like :search or description like :search"); q.addParam(name="search",value="%e%",cfsqltype="cf_sql_varchar");

r = q.execute();

writeDump(r); </cfscript>

As you can see, I'm searching against both the artname and the description column. This should run fine, right? Unfortunately it doesn't. When executed you get:

Named Sql parameter 'search' used in the query not found in the queryparams

The SQL specified in the query

select * from art where artname like :search or description like :search.

You can use addParam() on the query object to add a queryparams (params by name and/or params by position)

Well, when I see that, my first reaction is, um, I am using addParam. I did some digging to see if I could find out why this error was thrown. Don't forget that the script support for stuff like query (and mail, etc) is built with simple CFCs. You can find these in the com/adobe/coldfusion folder under your main customtags folder. (Which, by the way, means you shouldn't remove that default custom tag folder!) Luckily Adobe kept these CFCs unencrypted. Turns out that Adobe's code parses the SQL based on your params passed in. These params end up becoming child tags. The arguments used in addParam get passed to cfqueryparam as a structure. Since "name" is not a valid attribute, Adobe "cleaned" the values you passed by removing the name value. My query used the same name twice, so when it tried to replace the second bound parameter, it failed to link it up with the named parameter I sent in. Confusing? Well, I was able to fix it by simply delaying the cleaning until the bound parameters had all been replaced. I shared this with Adobe, but most likely this won't make it into the final release. I've been given permission to share the CFC which I've attached to this blog entry. If you use it, please make a backup of Adobe's code first. As always, I warrant nothing but my ability to get down and boogies hard.

If you feel skittish replacing core Adobe code with mine, you would fix the above query by duplicating the addParam.

<cfscript> q = new com.adobe.coldfusion.query(); q.setDatasource("cfartgallery"); q.setSQL("select * from art where artname like :search or description like :search"); q.addParam(name="search",value="%e%",cfsqltype="cf_sql_varchar"); q.addParam(name="search",value="%e%",cfsqltype="cf_sql_varchar");

r = q.execute();

writeDump(r); </cfscript>

Issue Two

This one was a doozy. Adobe's code makes use of string parsing to replace your bound parameters with tokens. Unfortunately the parsing can be broken if you use single quotes in your query. Here is an example:

<cfscript> q = new com.adobe.coldfusion.query(); q.setDatasource("cfartgallery"); q.setSQL("select * from art where artname = '' or description like :search"); q.addParam(name="search",value="%e%",cfsqltype="cf_sql_varchar");

r = q.execute();

writeDump(r); </cfscript>

See the first part of the where clause? The combination of this plus the bound parameter after it trips up the components and throws an error. The issue only seems to occur if you combine the single quote clause with a bound parameter. I didn't have time to try to fix the code here as the workaround is pretty easy:

q.setSQL("select * from art where artname = :blank or description like :search"); q.addParam(name="blank",value="",cfsqltype="cf_sql_varchar"); q.addParam(name="search",value="%e%",cfsqltype="cf_sql_varchar");

Anyway, watch out folks. If people continue to run into issues, please work with me and we can keep the modified query.cfc updated. I'm sure Adobe would appreciate that!

Download attached file.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Steve &aposCutter&apos Blades posted on 9/29/2009 at 6:21 PM

Trust you to find the fringe issues;) (that really aren't that fringe.) Thanks for the post and the updated cfc, this should help alleviate some issues.

Comment 2 by Ben Nadel posted on 9/29/2009 at 9:58 PM

Yeah, the parsing of queries in CFScript is rather junky. I can't believe that using the same name twice breaks it :) crazy.

Comment 3 by ZHU, Jia posted on 7/28/2010 at 3:54 PM

Hi Ray, thanks for the fix!

We also run into those problems and I'm just as surprised that not more people are reporting this?

For the second issue, I debugged into Adobe code and
it only happens when there are single quoted EMPTY strings involved, this is because of the way the function replaceDelimsWithMarkers in CustomTags/com/adobe/coldfusion/query.cfc works - it splits the statement by single quote using listtoarray, and only processes even numbered elements, which is *supposed* to be the single quoted content.

BUT, in case of empty string, ListToArray ignore the empty fields (empty strings) by default, this screws up the content interpretation.

All it takes to fix it is to use the attribute "includeEmptyFields" in the call of ListToArray function in CustomTags/com/adobe/coldfusion/query.cfc.

Line 345, change
var sqlArray = listtoarray(arguments.sql,"'");

to
var sqlArray = listtoarray(arguments.sql,"'", true, false);

This way empty content is not ignored.

Comment 4 by Raymond Camden posted on 7/31/2010 at 9:08 PM

@Zhu - I'd file this as a bug (http://cfbugs.adobe.com/cfb....

Comment 5 by ZHU, Jia posted on 8/2/2010 at 11:01 AM

Ray, thanks for reminding me, I meant to do it all along but got distracted along the way;-)

Now that it's filed as a bug, I think we can expect this to be fixed at next release!

Comment 6 by Alexei Martchenko posted on 7/11/2012 at 10:41 PM

From all objects Adobe created to behave as tags, query is the worse and cumbersome one.

Comment 7 by Raymond Camden posted on 7/11/2012 at 11:16 PM

Have you done queries in other script based languages? To be honest, this is about the same.

Comment 8 by Sean A posted on 1/23/2014 at 6:33 AM

Hi Raymond,

I am using application.cfm that stores all the database username and password.

I am unable to define the #dsn# #username# or #password#

I tried
q.setDatasource("dsn");
q.setUsername("username");
q.setpassword("password");

(also tried q.setDatasource("application.dsn");) and
(also tried q.setDatasource("#dsn#");)

Nothing seems to be working for me.

Comment 9 by Raymond Camden posted on 1/23/2014 at 6:49 AM

q.setDatasource('dsn') will not work as you are passing the literal value, dsn. If you have dsn as an application variables, then you need to either do

q.setDatasource(application.dsn)
or
q.setDatasource("#application.dsn#");

The first version is better imo.

Comment 10 by Link Worx Seo posted on 6/15/2016 at 11:26 AM

Why are you placing the: new com.adobe.coldfusion in front of the new query?

Comment 11 (In reply to #10) by Raymond Camden posted on 6/15/2016 at 12:26 PM

I believe because I didn't have a customtag path pointing to the folder where CF stored those CFCs. Plus I like it being obvious where it comes from.

As an FYI, you shouldn't be using these CFCs anymore if you use a CF version that supports queryExecute.

Comment 12 (In reply to #11) by Link Worx Seo posted on 6/15/2016 at 9:01 PM

I'm not using those anymore. I am scripting the information and today when doing a writeDump(); I noticed the same thing shown. Answered mu own question today. Guess I did not notice it before when doing writeDump(); or I just forgot. I know petty question.

Comment 13 by Charles Robertson posted on 4/11/2017 at 11:40 PM

Also, Ray, if you try and use:

q.addParam(name=“someTable.someColumn”,value=“foo”,cfsqltype=“cf_sql_varchar”);

It throws a wobbly in Railo, but is fine in ACF. Grrrrrrrrrrrr!

For some reason, Railo gets muddled by table column prefixes, amongst many other things. Railo, seriously need to polish up their regex on this tag. There are numerous issues, which Adam Cameron has also highlighted...

My solution is just to use positional parameters:

q.addParam(value=“foo”,cfsqltype=“cf_sql_varchar”);
q.addParam(value=“bar”,cfsqltype=“cf_sql_varchar”);

Comment 14 (In reply to #13) by Raymond Camden posted on 4/12/2017 at 1:29 AM

Now I'd just use queryExecute. :)

Comment 15 (In reply to #14) by Charles Robertson posted on 4/12/2017 at 11:26 AM

Ray. Thanks for the heads up.

I see that queryExecute() uses non named params, so this equates to:

queryService.addParam(value="bar",cfsqltype="cf_sql_varchar");

Which is perfect.

In fact, I am not totally sure what the point of named params is?
I am glad CF has removed this, because it creates unnecessary ambiguity.

Comment 16 (In reply to #15) by Raymond Camden posted on 4/12/2017 at 11:33 AM

Nope, it supports it. And as to why - named parameters let you provide more context to your code, so instead of having to remember a particular order, you can use names to refer to values. This also makes it easier to update your SQL later w/o worrying about changing the order of bound params.

Comment 17 (In reply to #16) by Raymond Camden posted on 4/12/2017 at 11:34 AM

See reference here: https://helpx.adobe.com/col...

Comment 18 (In reply to #16) by Charles Robertson posted on 4/12/2017 at 8:07 PM

Ray. Thanks for this information.
OK. That makes sense with respect to named parameters.

I didn't see the named parameter example, when I originally looked at the documentation for QueryExecute().

With this in mind, it maybe that the Railo 'table column prefix bug', affects QueryExecute(), as well.
I will test this later, and submit a bug report, if necessary.

I think that QueryExecute() is a better, more compact implementation than queryService.execute(), so I may start using it...