Tip using ColdFusion query in cfscript and something to watch out for

Tags: Coldfusion, ColdFusion 9

I have been meaning to blog about this for awhile now, and thought that now is as good as time as any.

While having a conversation with another developer I had noticed that he had been using ColdFusion 9, and that he had some components that were still tag based, especially when dealing with cfqueries. He would switch between cfscript and tag based code only when he was doing any form of queries against the database.

And when I asked about why he might no be using another method he wasn't aware that you could do this, and I thought it would be a great idea to let others know about it.

However this is also one small pitfall that has and keeps tripping me up.

I will discuss more about that in a moment, but first lets look at what he was trying to do.

In his tag based code he was doing a lot of conditional stuff to build the query, which is why I asked the question, and his code looked something like this.

public any function testFunction() {
</cfscript>
<cfquery name="local.someVar" datasource="someDatasource">
   select column1, column2 from table1
   <cfif arguments.someCondition>
    where isActive = 1
   </cfif
</cfquery>
<cfscript>
}

Now I have kept this very verbose so that you can get the idea of what he was doing, now although there is nothing wrong with this and you may prefer to do this, personally I think switching between tag and cfscript just looks messy.

The solution to this in cfscript would be to do something like this instead.

var queryService = new query();
   queryService.setDatasource("someDatasource");
   queryService.setSQL("select column1, column2 from table1");
   var result = queryService.execute();
   return result.getResult();

Now this is the basics of how it can be written, and as you can see it hasn't allowed for the conditional statement. So what we need to do is modify it every so slightly.

var queryService = new query();
savecontent variable="queryStatement" {
writeOutput("select column1, column2 from table1");
if(arguments.someCondition) {
   writeOutput(" where isActive = 1");
}
}
queryService.setDatasource("someDatasource");
queryService.setSQL(queryStatement);
var result = queryService.execute();
return result.getResult();

Now granted if you look at this code, it might seem messy or just not as clean as the tag equivalent. But you do get used to it, and its kind of funky in its own little way.

So this is not anything spectacular, just that when you are used to using tag based for so long. It is easy to forget that there are options to building the query in cfscript.

Now I did mention there is also a gotcha, which is nothing major but it is something that has and does keep tripping me up. And that is for some reason when you design the string as in the very first basic example, ColdFusion does seem to be very picky with things that it things shouldn't be in there.

Here is an example of what I mean.

var queryService = new query();
queryService.setDatasource("someDatasource");
queryService.setSQL("
   select column1, column2
   from table1
   where isActive =: isActive
   order by column1
");
queryService.addParam(name="isActive", value=arguments.activeFlag), cfsqltype="CF_SQL_BIT");
var result = queryService.execute();
return result.getResult();

Now although this actually looks fine, ColdFusion will throw an error here. And believe it or not it is the SQL on multiple lines, for some reason when doing the where clause like this will throw an exception about the params being passed.

The solution to fix this is just to move the entire where clause onto the one line, and it will then run without any dramas.


TweetBacks

Tip using ColdFusion query in cfscript and something to watch out for http://fb.me/TcF2stQ7 Aug 11, 2011

Comments (Moderated) | Add Comment

  • Raymond Camden's Gravatar Don't forget the CFC implementation of Query is unencrypted CFML. Going to replicate here and see if I can fix.
    # Posted By Raymond Camden | 8/11/11 6:44 AM
  • Raymond Camden's Gravatar Andy - it worked fine for me when I moved the addParam line before the execute. You have it _after_ the execute which is wrong.
    # Posted By Raymond Camden | 8/11/11 6:46 AM
  • Andrew Scott's Gravatar Ray, yeah I should have had a look at the code for the query. But I am sure the last time I thought I had it was encrypted.

    As for the error, yeah that was a copy and paste error on my part. But the actual string I have found that it can and does error if the order by precedes the param but on the next line. I can give you a URL to look at with this error if you ping me privately.
    # Posted By Andrew Scott | 8/11/11 6:51 AM
  • Andrew Scott's Gravatar Have now fixed the post to have the param where it should be.
    # Posted By Andrew Scott | 8/11/11 6:55 AM
  • Raymond Camden's Gravatar Yeah, if you want to ping me, please do. I can't reproduce this using the cfartgallery db. You are using 901, right? Updates to the CFCs were pushed there.
    # Posted By Raymond Camden | 8/11/11 7:00 AM
  • Andrew Scott's Gravatar Thanks Ray, it looks like if you use spaces to indent there is no problem. However if you indent using spaces then it throws the error.
    # Posted By Andrew Scott | 8/11/11 7:17 AM
  • Phillip Molaro's Gravatar Hey, I've been using cfquery in cfscript for a lot of things over the past year. I got sick of seeing CFCs that toggles in and out of script b/c I or others didn't know how to query in script.

    I've run into this same issue as well. It happens in 9.0 and 9.0.1. From my debugging, it seems to be the query param. If the param is at the end of a broken line of SQL (as 95% od devs would prefer), the cf doesn't read it properly. What I've found is to put a space after my last param (or just not put it at the end at all) and cf gets it. I assume cf is doing a trim somewhere inside and it mucks up the statement. Without the space it seems to read:
    ... WHERE foo = :pMyParamORDER BY bar

    Instead of:
    ... WHERE foo = :pMyParam ORDER BY bar

    You putting it on one line actually just puts in the space. Hope that helps some.

    I ran into another issue trying to make my var be null. I wanted a param that, based on some conditional statement was either a value or null. So, for example, in a function, if(isDefined(arguments.xyz)) param = xyz else param = '<> null'
    I paraphrased that code of course, but I Never got that to work.
    # Posted By Phillip Molaro | 8/11/11 8:22 AM
  • Andrew Scott's Gravatar It should be noted that Ray has identified this as a bug in ColdFusion, and the query component. It has submitted the fix to Adobe, so lets hope it gets released soon.

    The problem is when you use tabs to indent the SQL, if you use spaces you will not see the problem.

    Here is Rays snippet from an email he sent me.

    The fix is here:

    var delimters = SPACE & "," & COMMA & "," & RIGHTPARENTHESIS;
    namedparam = trim(listfirst(sqlArray[i],delimters));

    See how it's trying to get the named param at the beginning? I just made it:

    var delimters = SPACE & "," & COMMA & "," & RIGHTPARENTHESIS & ","
    & chr(10);
    namedparam = trim(listfirst(sqlArray[i],delimters));

    and it worked.
    # Posted By Andrew Scott | 8/11/11 8:37 AM
  • Phillip Molaro's Gravatar Sounds good. I assumed it was a bug. Since you are adding chr(10), shouldn't it also include chr(13) for a carriage return? I could either or the combination of both tripping it up.
    # Posted By Phillip Molaro | 8/11/11 9:03 AM
  • Andrew Scott's Gravatar @phillip that is probably a good question, and something +Ray Camden might be able to answer as he was the one who tracked the exact problem down.
    # Posted By Andrew Scott | 8/11/11 9:07 AM
  • Raymond Camden's Gravatar Phillip, if you could whip up a quick demo (using cfartgallery) where my fix isn't enough, I'd appreciate it. Bit busy now so can't look at this for a bit.
    # Posted By Raymond Camden | 8/11/11 9:09 AM
  • Phillip Molaro's Gravatar sure, I'll see if I can reproduce that as an issue. I'm not sure it would be, I was just thinking I have had issues in the past with text pasted in from Word, or other places where I had to remove or add both in combination. It may totally not apply here, but I'll try. Thx!
    # Posted By Phillip Molaro | 8/11/11 9:13 AM

Comments (Moderated) | Add Comment