cfquery in cfml with parameters

ColdFusion 9 allows you to write your queries in CFML. Whilst it's not as nice as using cfquery, if you want to know what it's like to be a PHP developer (sorry - cheap shot) here is the syntax

<cfscript>
q = new query();
// don't need to set datasource if using this.datasource in Application.cfc 
q.setDatasource( "cfartgallery" );
// build the SQL statement 
q.setSQL( "select * from Art where artistid = :artistid and issold = :issold" );
// this is the equivalent of cfqueryparam 
q.addParam( name="artistid", value=2, cfsqltype="CF_SQL_INTEGER" );
q.addParam( name="issold", value=1, cfsqltype="CF_SQL_BIT" );
// run the query and get a query object
result = q.execute();
// dump query object
WriteDump( result );
// dump recordset
WriteDump( result.getResult() );
// dump query recordcount
WriteDump( result.getResult().recordcount );
</cfscript>

If you prefer you can also use positional parameters instead of named parameters like this:

q.setDatasource( "cfartgallery" );
q.setSQL( "select * from Art where artistid =:? and issold =: ? " );
q.addParam( value=2, cfsqltype="CF_SQL_INTEGER" );
q.addParam( value=1, cfsqltype="CF_SQL_BIT" );
result = q.execute();

Alternatively you can write it like this:

var params = {};
params.datasource = "cfartgallery";
params.SQL = "select * from Art where artistid = :artistid";
q = new query(argumentCollection=params);
q.addParam(name="artistid", value=2, cfsqltype="CF_SQL_BIT");
result = q.execute();

 Hat tip to Brad Wood's post http://www.codersrevolution.com/index.cfm/2009/2/5/CF-9-new-cfscript-syntax and Dave Ferguson http://blog.dkferguson.com/index.cfm/2009/7/14/ColdFusion-9--CFScript-Examples--QUERY

• Posted in:
• SQL
• ColdFusion

4 comments

1. Hahahahaha, good laugh there, that cheap shot ;-)
   
   CFSCRIPT's been getting a lot of heat lately, but then again it never gained momentum because you had to get in and out of CFSCRIPT blocks when there was something you wanted done that CFSCRIPT didn't provide. So I stopped using it.
   
   Hehehe, still chuckling over that cheap shot - so true, so true ;-)
   
   Comment by Sebastiaan – July 15, 2009
2. Handy to know as I can't find this in the docs ( yet ). I hope ColdFusion Builder will prompt with a code assist when you type: cfsqltype=" like it does with the tags.
   
   Comment by Paul Kukiel – July 15, 2009
3. @Sebastiann - I await the flames :)
   
   It'll be great to know that you can use cfscript or cfml to write code. There are a couple of tags I haven't worked out how you do yet, like cfdirectory and cfflush, but most of them seem to be supported.
   
   @Paul, I had a quick look in the docs and couldn't see it. So ended up looking at Brad Wood's post: www.codersrevolution.com/index.cfm/2009/2/5/CF-9-new-cfscript-syntax
   , dumping out the q = new query() and doing some backwards engineering with guesswork :)
   
   Comment by John Whish – July 15, 2009
4. Strange error with an INSERT statement with this technique. If you have a CRLF between the last param and the closing )"; you get an error where it thinks the last param is not supplied.
   
   Edge case I know but it's how like to layout my code.
   
   Just saying...
   
   Comment by dickbob – April 23, 2010