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
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
Method Signature
QueryExecute(sql=[string], params=[any], options=[struct])
Arguments
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"
]
);
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 - Boxlang5+
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 );
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)
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
_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
Last updated
Was this helpful?