Continuing the blog posts series on language enhancements, today, I am going to cover the Query functions and explain its various overloaded methods in detail. Before I discuss this feature, here is the list of language features added in ColdFusion Splendor.

Script syntax for Query tag is available in Splendor through the generic "Script support for tags". This generic solution works well for all the other tags except CFQuery. CFQuery, being a bit complex, necessitated a need to provide an easy to use script-syntax for query execution. So, to simplify it in a script block, the query functions have been added in Splendor. These are designed in-line with existing ORM execute functions. The general syntax of query functions are:
QueryExecute(sql_statement);
QueryExecute(sql_statement, queryParams);
QueryExecute(sql_statement, queryParams, queryOptions);

As shown above, query functions can be invoked by passing one, two, or three parameters. The first parameter, an SQL statement is mandatory and the other two (queryParams and queryOptions) are optional. 

Query params:

There are two different ways to pass a parameter to an SQL query: named and positional (unnamed). In named parameter, you use ":" as the placeholder and pass a struct to queryParam. For the positional one, "?" is used as the placeholder and the values of the parameters are passed as an array to queryParams. 

  //named parameter 
  QueryExecute("select * from Employee where country=:country and artistid=:id", {country='USA', id:1});
  
  // positional parameter
  QueryExecute("select * from Employee where country=:? and artistid=?", ['USA', 1]);

If required, the CFQueryParam attributes information can also be passed for executing a query. For this, the attributes of an individual queryParam are first defined in a struct and then this struct is passed, rather passing  a simple string or number, as a  value of that param. To understand it better, an example is shown below:

//queryparams with attributes
QueryExecute("select * from Employee where country=:country and empId=id, {id:101, country:{name="USA", CFSQLType='CF_SQL_CLOB', list=true }});

QueryOptions

QueryOptions is a struct which contains different attribute values of a Query tag. In the following example, a "datasource" attribute has been passed in the queryOptions parameter. Similarly, all the attributes (except name) can be passed to queryOptions before executing a query.

 QueryExecute("select * from Artists where artistid=:id", {id:1},{datasource= "cfartgallery"});

This feature has been covered in details in Splendor document. See the doc for more information.

5 Comments to “Language Enhancements in ColdFusion Splendor - CF Functions for Query tag”

  1. Carl Von Stetten
    I think in the second code example, the array being used for parameters should look like this: ['USA', 1] instead of ['USA', id:1].

    Also, it would have been great if this approach had been taken for CFStoredProc too, maybe as a StoredProcExecute() function.
  2. Carl Von Stetten
    Oops. I meant the second part of the first code example.
  3. Awdhesh
    @carl,thanks for pointing it out. Corrected it.
  4. Awdhesh Kumar
    @Carl, functional support of storedproc is a good idea. Can evaluate it for next release.
  5. Henry Ho
    QueryExecute() back ported for CF9 & CF10 here:
    https://github.com/misterdai/cfbackport/blob/master/cf11.cfm

Leave a Comment

Leave this field empty: