QueryExecute()

edit

Passes SQL statements to a data source.

This is the equivalent to <cfquery> as a function, it uses the same underlying code.

QueryExecute( sql=string, params=any, options=struct, name=string );

Returns: any

Argument Description
sql
string, required 		edit

SQL to execute
params
any, optional 		edit

An array or structure containing parameter values.

When passing an array, use ? as place holders and pass them in the order they are referenced in the SQL.

When passing a struct, use :keyName, where keyName is the name of the key in the structure corresponding to the parameter.

The array or structure can be a structure with keys that match the names of the <cfqueryparam> names:

  • maxlength
  • list
  • scale
  • separator
  • null
  • cfsqltype|sqltype|type
  • value

Alias: param
options
struct, optional 		edit

A struct containing the query options

All the <cfquery> tag attributes are supported, except the name attribute.

Alias: option, queryOptions
name
string, optional 		edit

name of the query produced, visible in dumps or debugging output.

Alias: queryName

Examples

edit

SELECT

_test = queryNew(
    "_id, _need, _forWorld",
    "integer, varchar, varchar",
    [[01,'plant', 'agri'],[02, 'save','water']]
);
queryResult = QueryExecute(
    sql = "SELECT * FROM _test WHERE _need = :need",
    params = {
        need: {
            value: "plant",
            type: "varchar"
        }
    },
    options = {
        dbtype = "query"
    }
);
dump(queryResult);

INSERT

QueryExecute(
    sql = "insert into user (name) values (:name)",
    params = {
        name: {
            value: "lucee",
            type: "varchar"
        }
    },
    options = {
        dbtype = "query",
        // return the autoincrement generated key from database
        result: "insertResult"
    }
);
dump(insertResult.generatedKey);

Concise alternative with unnamed function arguments and removed optional dbtype:

QueryExecute(
    "insert into user (name) values (:name)",
    {"name": {"value": "lucee", "type": "varchar"}},
    {"result": "insertResult"}
);
dump(insertResult.generatedKey);

Related System Properties / Environment Variables

  • LUCEE_CASCADE_TO_RESULTSET - When a variable has no scope defined (example: `#myVar#` instead of `#variables.myVar#`), Lucee will also search available resultsets (CFML Standard) or not
    Type: boolean, Default: true
  • LUCEE_DATASOURCE_MSSQL_MODERN - Boolean value to enable modern MSSQL datasource handling
    Type: boolean, Default: false
  • LUCEE_DATASOURCE_POOL_VALIDATE - If enabled, Lucee will validate existing datasource connections reused from the datasource pool before using them. This protects from exceptions caused by connections dropped by the DB server but creates additional communication between Lucee and the DB server. Removed in 6.2
    Type: boolean, Deprecated: 6.2
  • LUCEE_QOQ_HSQLDB_DEBUG - Boolean value to enable debug logging for HSQLDB Query of Queries operations
    Type: boolean, Default: false
  • LUCEE_QOQ_HSQLDB_DISABLE - Boolean value to disable HSQLDB for Query of Queries, forcing use of alternative QoQ engine
    Type: boolean, Default: false
  • LUCEE_QOQ_PARALLELISM - Controls the parallelism level for Query of Queries operations
    Type: numeric, Default: true
  • LUCEE_QUERY_ALLOWEMPTYASNULL - In Lucee 5, an empty string passed into a query parameter with a numeric type was interpreted as null. In Lucee 6, this is no longer accepted and throws an exception. You can simulate the old behavior by setting this environment variable or SysProp to `true`. By setting the log level of the datasource log to `warn`, you will receive information in the log when the old behavior is used. This allows you to modify your code for the new behavior without encountering runtime issues with the existing code
    Type: boolean, Default: false
  • LUCEE_QUERY_RESULT_THRESHOLD - Enables automatic logging of database queries that return large result sets to help proactively identify potential OutOfMemory (OOM) issues. When set to a positive integer, Lucee will log a warning message to the datasource log category whenever a query returns a number of rows greater than or equal to the specified threshold. Set to 0 or leave unset to disable this feature (default behavior). Logs include execution time, row count, column count, threshold value, SQL query, and tag context. Helps identify problematic queries before they cause memory issues in production environments
    Type: numeric, Default: false, Introduced: 6.2.3.15

See also