Execute an SQL query and returns the results.
Method Signature
QueryExecute(sql=[String], params=[any], options=[struct])
Arguments
Argument
Type
Required
Description
Default
An array of binding parameters or a struct of named binding parameters
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
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 );
