QueryExecute

Search

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.  

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,params,options)

Parameters

Name

Required

Description

sql

Yes

SQL string to execute.

params

No

Array or Struct of parameter values.

options

No

Struct containing query options. Values are:

  • datasource
  • cachename
  • dbtype
  • disableAutoGenKeys

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

See also

cfquery

History 

ColdFusion (2021 release): Added the attribute returnType, with the following return types:

  • Array
  • JSON/Array
  • Query (default)

ColdFusion (2018 release): Added the value disableAutoGenKeys in options.

ColdFusion 11: Added this function

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

All employees whose emp id is between 4-14
All employees whose emp id is between 4-14

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

Record for a particular id and name
Record for a particular id and name

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

Employees working in sales and stays in either Newton or San Francisco
Employees working in sales and stays in either Newton or San Francisco

Example 6

The sample below used the parameter disableAutoGenKeys.

<cfscript>
     q=QueryExecute("Select * from art where artid=1",{}{datasource="cfartgallery", disableAutoGenKeys="false"}
);
     writedump(q);
</cfscript>

Example 7

<cfscript>
    cfhttp(name="myQuery", 
           url="https://raw.githubusercontent.com/sauravg94/test-repo/master/MOCK_DATA.csv", 
           firstrowasheaders="true",
           method="GET");
    myResult=QueryExecute(("SELECT * from myQuery WHERE id BETWEEN :low AND :high"),{low=100,high=500},{dbtype="query"})
    writeDump(myResult)
</cfscript>

Example 8

<cfscript> 
  // QueryExecute return type array 
  qoptions = {result="myresult", datasource="cfartgallery",returntype="array"} 
  myquery = QueryExecute("select ARTID,ARTISTID,ARTNAME from art where ARTID < 2", [] ,qoptions); 
  writeOutput(myquery[1].ARTID &" ") 
  writeOutput(myquery[1].ARTISTID &" ") 
  writeOutput(myquery[1].ARTNAME &" ") 
</cfscript>

Example 9

<cfscript> 
  // QueryExecute return type "json/array" 
  qoptions = {result="myresult", datasource="cfartgallery",returntype="json/array"} 
  myquery = QueryExecute("select ARTID,ARTISTID,ARTNAME from art where ARTID < 2", [] ,qoptions); 
  record=deserializeJSON(myquery) 
  writeOutput(record[1].ARTID &" ") 
  writeOutput(record[1].ARTISTID &" ") 
  writeOutput(record[1].ARTNAME &" ") 
  writeOutput(myquery) 
</cfscript>

Example 10

<cfscript> 
  // QueryExecute return type default query 
  qoptions = {result="myresult", datasource="cfartgallery"} 
  myquery = QueryExecute("select ARTID,ARTISTID,ARTNAME from art where ARTID < 2", [] ,qoptions); 
  record=QueryGetRow(myQuery,1) 
  writeOutput(record.ARTID &" ") 
  writeOutput(record.ARTISTID &" ") 
  writeOutput(record.ARTNAME &" ") 
</cfscript>
Adobe logo

Sign in to your account

Quick links

View all your plans Manage your plans

Legal Notices    |    Online Privacy Policy