QueryExecute

Execute an SQL query and returns the results.

Parameters

The parameters argument can be used to bind parameters to the SQL query. You can use either an array of binding parameters or a struct of named binding parameters. The SQL must have the parameters bound using the syntax ? for positional parameters or :name for named parameters.

Example:

 queryExecute( sql: "SELECT * FROM users WHERE id = ?", params: [ 1 ] );
 queryExecute( sql: "SELECT * FROM users WHERE id = :id", params: { id: 1 } );
 

You can also treat each named parameter to not only be a key-value pair, but also a struct with additional options:

• value:any - The value to bind to the parameter

• type:string - The type of the value, defaults to "varchar"

• maxLength:numeric - The maximum length of the value, only applicable for string types

• scale:numeric - The scale of the value, only applicable for decimal types, defaults to 0

• null:boolean - Whether the value is null, defaults to false

• list:boolean - Whether the value is a list, defaults to false

• separator:string - The separator to use for list values, defaults to ","

Example:

 queryExecute( sql: "SELECT * FROM users WHERE id = :id", params: { id: { value: 1, type: "integer" } } );
 queryExecute( sql: "SELECT * FROM users WHERE id IN (:ids)", params: { ids: { value: [ 1, 2, 3 ], type: "integer", list: true, separator: "," } } );
 

Options

The available options for this BIF are:

• cache:boolean - Whether to cache the query results, defaults to false

• cacheKey:string - Your own cache key, if not specified, the SQL will be used as the cache key

• cacheTimeout:timespan|seconds - The timeout for the cache, defaults to 0 (no timeout)

• cacheLastAccessTimeout:timespan|seconds - The timeout for the last access to the cache, defaults to 0 (no timeout)

• cacheProvider:string - The cache provider to use, defaults to the default cache provider

• columnKey:string - The column to use as the key when returntype is "struct"

• datasource:string - The datasource name to use for the query, if not specified, the default datasource will be used

• dbtype:string - The database type to use for the query, this is either for query of queries or HQL. Mutually exclusive with datasource

• fetchsize:numeric - Number of rows to fetch from database at once, defaults to all rows (0)

• maxrows:numeric - Maximum number of rows to return

• password:string - Override the datasource password for this query only. NOTE: Not all JDBC drivers support this; will fall back to unpooled connection if unsupported

• result - The name of the variable to store the results of the query

• returntype - The return type: "query", "array", "struct"

• timeout - Query timeout in seconds

• username:string - Override the datasource username for this query only. NOTE: Not all JDBC drivers support this; will fall back to unpooled connection if unsupported

Method Signature

QueryExecute(sql=[string], params=[any], options=[struct])

Arguments

Argument	Type	Required	Description	Default
sql	string	true	The SQL to execute
params	any	false	An array of binding parameters or a struct of named binding parameters	[]
options	struct	false	A struct of query options	{}

Examples

Simple Example

SQL only example. Assumes that a default datasource has been specified (by setting the variable this.datasource in Application.bx).

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

Using Named Placeholders

Use :structKeyName in your sql along with a struct of key/value pairs:

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

Using Positional Placeholders

You can pass placeholders by position using an array of parameters and the question mark ? symbol:

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

Return Type Options

By default, queryExecute() will return a query object with the results of the SQL statement. To return results as an array of structs, you can specify the returnType: "array":

queryExecute("SELECT * FROM Employees", [], { returnType: "array" });

[
	{
		"empid": 1,
		"firstname": "John",
		"lastname": "Doe",
		"country": "USA"
	},
	{
		"empid": 2,
		"firstname": "Jane",
		"lastname": "Smith",
		"country": "Canada"
	}
]

You can also return results as a key-value struct by specifying the returnType and columnKey options:

queryExecute("SELECT * FROM Employees", [], { returnType: "struct", columnKey: "empid" });

{
	"1": {
		"empid": 1,
		"firstname": "John",
		"lastname": "Doe",
		"country": "USA"
	},
	"2": {
		"empid": 2,
		"firstname": "Jane",
		"lastname": "Smith",
		"country": "Canada"
	}
}

Caching a query

You can enable query caching by setting cache to true. By default, the cached query will never expire, but you can use cacheTimeout to specify a timeout duration via the createTimespan BIF.

qryResult = queryExecute(
  "SELECT * FROM Employees", 
  [],
  {
    "cache": true,
    "cacheTimeout": createTimespan( 0, 0, 0, 2 )
  }
);

This will mark the query for cache eviction after 2 seconds. You can also use cacheLastAccessTimeout to specify a timeout based on the last access time of the query:

qryResult = queryExecute(
  "SELECT * FROM Employees", 
  [],
  {
    "cache": true,
    "cacheLastAccessTimeout": createTimespan( 0, 0, 0, 2 )
  }
);

See our caching documentation for more information on caching.

Query of Queries

Query a local database variable without going through your database

Run Example

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

Return Query as an Array of Structs - Boxlang5+

Return a query object converted into an array of structs.

Run Example

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

Result: [ { firstname: "Han" } ]

Return Query as an Array of Structs - Boxlang 4.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-of-entities"
} );
writedump( subUsers );

Result: [ { firstname: "Han" } ]

Return Query as a Struct of Structs

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

Run Example

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

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

Additional Examples

Run Example

_test = queryNew( "_id, _need, _forWorld", "integer, varchar, varchar", [ 
	[
		1,
		"plant",
		"agri"
	],
	[
		2,
		"save",
		"water"
	]
] );
queryResult = queryExecute( sql="SELECT * FROM _test WHERE _need = :need", params={
	NEED : {
		VALUE : "plant",
		TYPE : "varchar"
	}
}, options={
	DBTYPE : "query"
} );
dump( queryResult );

queryExecute( sql="insert into user (name) values (:name)", params={ 
	NAME : {
		VALUE : "boxlang",
		TYPE : "varchar"
	}
}, options={
	DBTYPE : "query",
	RESULT : "insertResult"
} );
dump( insertResult.GENERATEDKEY );

Related

• IsInTransaction

• IsWithinTransaction

• PreserveSingleQuotes

• TransactionCommit

• TransactionRollback

• TransactionSetSavepoint