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 support for tags
• Member functions for CF data type/data structure
• Improved JSON serialization
• Easy to use CF functions for Query tag
• Elvis operator (?:)
• Promoting built-in CF function to first class 
• Miscellaneous new functions: QueryGetRow, ListEach and others. 

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.