Description

Apart from the generic script syntax for the tag, there is another script alternative to cfquery tag, provided by the QueryExecute function. This function simplifies the query execution in the CFScript block.  

This function lets you pass unnamed parameters to the query. Use '?' (question mark) as the place-holder for the parameters. The values to the parameters should be passed as an array toparams.  

For more information on using the unnamed parameters, see this document.  

Note that the following points are applicable:  

  • If there are any Query parameters, they must be passed as the second argument of the function. It can be either a struct or an array.
  • If you need to provide additional attribute as a Query parameter (like CFSQLType, list, and separator), you can pass these as struct against that column name.
  • The Query and datasource properties will be passed as the third argument.

 The Query tag is also available as a ColdFusion function apart from the generic script syntax for the CFQuery tag. This function simplifies the query execution in the CFScript block.

Syntax

QueryExecute(sql_str, queryParams, queryOptions);

Parameters

Name Required Description
sql_str Yes SQL string to execute.
queryParams No Array or Struct of parameter values.
queryOptions No

Struct containing query options. Values are:

  • datasource
  • cachename
  • dbtype

History 

ColdFusion 11: Added this function

For a list of supported queryOptions, see the documentation for <cfquery>. All except the name option are supported.

Example 1

<cfset qoptions = {result="myresult", datasource="artGallery", fetchclientinfo="yes"}>

<cfset myquery = QueryExecute("select * from art where ARTID < 5", [] ,qoptions)>
<cfdump var="#myQuery#" >

<cfset myquery1 = QueryExecute("select * from art where ARTID < ?", [4] ,qoptions)>
<cfdump var="#myQuery1#" >

<cfset myquery2 = QueryExecute("select * from art where ARTID < :artid and artistid=:aid ", {artid={value=100}, aid=2} ,qoptions)>
<cfdump var="#myQuery2#" >

Example 2

QueryExecute ("select from Employees where empid=1");

QueryExecute("select from Employee where country=:country and citizenship=:country", {country='USA'});

QueryExecute("select from Employee where country=:country and citizenship=:country", {country={value='USA',
CFSQLType='CF_SQL_CLOB', list=true, other‐queryParam_attributes}, });

QueryExecute("select from Employee where country=:country and citizenship=:country", {country='USA'},
{datasource=”cfartgallery”, cachename=”employees”})

QueryExecute ("select from Artists where artistid=? and country=?", [1, “USA”], {datasource="cfartgallery"});

Example 3

The example searches for all employees whose emp id is between 4-14 and displays the results.

<cfscript>
       myResult=QueryExecute(("SELECT * FROM EMPLOYEES WHERE EMP_ID BETWEEN :low AND :high"),{low=4,high=14},
       {datasource="cfdocexamples"});
       WriteDump(myResult);
</cfscript>

Output

Example 4

This example searches for records in the table for a particular id and name.

<cfscript>
       myResult=QueryExecute("SELECT * FROM MYEMPLOYEES WHERE ID=:ID AND Name=:NAME",{ID="004",Name="Jill"},
       {datasource="cfdocexamples"});
       WriteDump(myResult);
</cfscript>

Output

Example 5

This example searches for all employees working in sales and stays in either Newton or San Francisco.

<cfscript>
       myResult=QueryExecute(("SELECT * FROM EMPLOYEES WHERE DEPARTMENT='Sales' AND LOCATION in (?,?)"),["Newton","San Francisco"],
       {datasource="cfdocexamples"});
       WriteDump(myResult);
</cfscript>

Output

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy