queryExecute

Executes a SQL query, returns the result.

queryExecute(sql [, params, options]) → returns query

queryExecute Argument Reference

sql string
Required

SQL string to execute.

params any

Array or Struct of parameter values. When passing an array use ? as place holders. When passing a struct use :keyName where keyName is the name of the key in the structure corresponding to the parameter. The struct or array can be a struct with keys such as the following.
Values:
  • cfsqltype
  • list

options struct

Struct containing query options, all cfquery tag attributes are supported except the name attribute.
Values:
  • datasource
  • cachename
  • dbtype

Examples sample code invoking the queryExecute function


Simple Example

SQL Only Example. Assums that a default datasource has been specified (by setting the variable this.datasource in Application.cfc)

qryResult = queryExecute("SELECT * FROM Employees");

Passing Query Parameters using Struct

Use :structKeyName in your sql then pass a struct with corresponding key names.

qryResult = queryExecute("SELECT * FROM Employees WHERE empid = :empid AND country = :country", {country="USA", empid=1});

Passing Query Parameters using Array

When passing with an array use the ? as a placeholder in your sql

qryResult = queryExecute("SELECT * FROM Employees WHERE empid = ? AND country = ?", [1,"USA"]);

Passing Query Parameters using Struct of Structs

If you need to pass the cfsqltype or other cfqueryparam attributes you can pass a struct for each param.

qryResult = queryExecute("SELECT * FROM Employees WHERE empid = :empid AND country = :country", {country={value="USA", cfsqltype="cf_sql_varchar"}, empid={value=1, cfsqltype="cf_sql_integer"}});

Specifying the Datasource

If you have not defined the datasource using this.datasource in Application.cfc or want to explicitly specify the datasource use the third argument.

qryResult = queryExecute("SELECT * FROM Employees", {}, {datasource="myDataSourceName"});

IN Query using lists

Query the database using a list of values

keys = '1,2,3,4,5,6,7';
data = queryExecute("select * from sometable where somecolumn in (:keys)",{keys={value=keys,list=true}}); 

Query of Queries

Query a local database variable without going through your database

users = queryNew( "firstname", "varchar", [{"firstname":"Han"}] );
subUsers = queryExecute( "select * from users", {}, { dbtype="query" } );
writedump( subUsers ); 

Return Query as an Array of Structs

Lucee 5+ Return a query object converted into an array of structs.

users = queryNew( "firstname", "varchar", [{"firstname":"Han"}] );
subUsers = queryExecute( "select * from users", {}, { dbtype="query", returntype="array" } );
writedump( subUsers ); 

Expected Result: [ { firstname: "Han" } ]


Return Query as a Struct of Structs

Lucee 5+ Return a query object converted into a struct of structs. (Struct key is based on the "columnkey" parameter)

users = queryNew( "id, firstname", "integer, varchar", [{"id":1, "firstname":"Han"}] );
subUsers = queryExecute( "select * from users", {}, { dbtype="query", returntype="struct", columnkey="id" } );
writedump( subUsers ); 

Expected Result: { 1: { id: 1, firstname: "Han" } }


Fork me on GitHub