Name
Last updated on
Jan. 11, 2022
|
Also applies to ColdFusion
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
|
|
Required |
Description |
|
sql |
Yes |
SQL string to execute. |
|
params |
No |
Array or Struct of parameter values. |
|
options |
No |
Struct containing query options. Values are:
For a list of supported queryOptions, see the documentation for <cfquery>. All except the name option are supported. |
See also
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
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
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>