Executes a SQL query, returns the result.

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

queryExecute Argument Reference

sql String

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.
  • cfsqltype
  • list

options Struct

Struct containing query options, all cfquery tag attributes are supported except the name attribute.
  • 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 ); 

Fork me on GitHub