Passes queries or SQL statements to a data source.
It is recommended that you use the cfqueryparam tag within
every cfquery tag, to help secure your databases from
unauthorized users
<cfquery>SQL</cfquery>
queryExecute(sql, params, options);
query: for querying an existing query object (i.e. Query of Queries); hql: for querying an ORM. NOTE: Supported SQL syntax varies depending on this value.
queryhql-1falsetruefalsefalsefalsefalsequeryShows how to use a cfqueryparam tag within cfquery.
<cfquery name="news">
SELECT id,title,story
FROM news
WHERE id = <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
</cfquery>CF 11+ Also see the Tags Implemented as Components section for another method of using
myQuery = queryExecute(
"SELECT myCol1, myCol2 FROM myTable
WHERE myCol1 = :myid
ORDER BY myCol1 ASC ",
{myid: 5},
{datasource = "myDSN"}
);
writeDump(myQuery);A dummy query is first created from scratch using queryNew, then sorted. A query of query is performed by specifying dbtype="query" and then using a query object variable name as in the FROM statement.
<!--- create a dummy query using queryNew --->
<cfset news = queryNew("id,title", "integer,varchar")>
<cfset queryAddRow(news)>
<cfset querySetCell(news, "id", "1")>
<cfset querySetCell(news, "title", "Dewey defeats Truman")>
<cfset queryAddRow(news)>
<cfset querySetCell(news, "id", "2")>
<cfset querySetCell(news, "title", "Men walk on Moon")>
<cfset writeDump(news)>
<!--- run QofQ (query of query) --->
<cfquery name="sortedNews" dbtype="query">
SELECT id, title FROM news
ORDER BY title DESC
</cfquery>
<cfset writeDump(sortedNews)>This syntax was implemented by script-based components in CF 9 & 10. It is superseded by queryExecute() in CF11.
queryObj = new Query(
name="qryDemo",
datasource="mydatasourcename",
sql = "SELECT col1, col2
FROM myTable
WHERE id=:id"
);
queryObj.addParam(name="id",value=arguments.id, cfsqltype="cf_sql_integer");
resultset=queryObj.execute().getResult();Lucee 5+ Return a query object converted into an array of structs.
<!--- create a dummy query using queryNew --->
<cfset users = queryNew("firstname", "varchar", [{"firstname":"Han"}])>
<cfset writeDump(users)>
<!--- run QofQ (query of query) --->
<cfquery name="subUsers" dbtype="query" returntype="array">
SELECT * FROM users
</cfquery>
<cfset writeDump(subUsers)>Expected Result: [ { firstname: "Han" } ]
Lucee 5+ Return a query object converted into a struct of structs. (Struct key is based on the "columnkey" parameter)
<!--- create a dummy query using queryNew --->
<cfset users = queryNew("id, firstname", "integer, varchar", [{"id":1, "firstname":"Han"}])>
<cfset writeDump(users)>
<!--- run QofQ (query of query) --->
<cfquery name="subUsers" dbtype="query" returntype="struct" columnkey="id">
SELECT * FROM users
</cfquery>
<cfset writeDump(subUsers)>Expected Result: { 1: { id: 1, firstname: "Han" } }
Inserts a new record into a table called myTable. The "ID" column is an auto-incremented primary key column.
<!--- insert the new record --->
<cfquery datasource="myDatasource" result="result">
INSERT INTO myTable ( name, email ) VALUES ( 'Joe', 'joe@domain.com' )
</cfquery>
<!--- output the new primary key --->
<cfdump var="#result.generatedKey#" />Signup for cfbreak to stay updated on the latest news from the ColdFusion / CFML community. One email, every friday.