QueryExecute()

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

SQL to execute

params
any, optional

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

A struct containing the query options

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

Alias: option, queryOptions

name
string, optional

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

Alias: queryName

Examples

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);

See also