Query

Execute a SQL query to the default or specified datasource.

Component Signature

<bx:Query name=[string]
datasource=[string]
returnType=[string]
columnKey=[string]
dbtype=[string]
maxRows=[integer]
blockfactor=[integer]
fetchsize=[integer]
timeout=[integer]
cache=[boolean]
cacheTimeout=[duration]
cacheLastAccessTimeout=[duration]
cacheKey=[string]
cacheProvider=[string]
result=[string]
clientInfo=[struct] />

Attributes

Atrribute	Type	Required	Description	Default
name	string	false	The name of the variable to store the query results in.
datasource	string	false	The datasource to execute the query against.
returnType	string	false	The type of the result to return. One of: query, struct, array.	query
columnKey	string	false	The key to use for the column names in the result struct.
dbtype	string	false	The type of query to execute. One of: query, hql.
maxRows	integer	false	The maximum number of rows to return. -1 for no limit.	-1
blockfactor	integer	false	Maximum rows per block to fetch from the server. Ranges from 1-100.	[ortus.boxlang.runtime.validation.dynamic.Min@3181fbe7, ortus.boxlang.runtime.validation.dynamic.Max@c8e99]
fetchsize	integer	false	The number of rows to fetch at a time. Ranges from 1-100.
timeout	integer	false	The timeout for the query in seconds.
cache	boolean	false	Whether or not to cache the results of the query.	false
cacheTimeout	duration	false	The timeout for the cached query, using a duration object like createTimespan( 0, 1, 0, 0 ).
cacheLastAccessTimeout	duration	false	The timeout for the cached query, using a duration object like createTimespan( 0, 1, 0, 0 ).
cacheKey	string	false	The key to use for the cached query.
cacheProvider	string	false	String name of the cache provider to use. Defaults to the default cache provider.
result	string	false	The name of the variable to store the query result in.
clientInfo	struct	false

Examples

Simple Query Usage

A simple query needs nothing more than a SQL statement:

<bx:query name="totalUserCount">
    UPDATE users SET modifiedTime=GETDATE() WHERE id=1
</bx:query>

Though, for SELECT queries, a variable name is necessary to acquire the results object:

<bx:query name="totalUserCount">
    SELECT COUNT(*) FROM users
</bx:query>

By default, the results will be in Query format. Array and Struct results are also supported:

<bx:query name="users" returnType="array">
    SELECT name, email FROM users
</bx:query>

For struct results, use columnKey to define the column name which will form the struct key in the resulting struct object:

<bx:query name="user" returnType="struct" columnKey="name">
    SELECT name, email FROM users
    WHERE id=1
</bx:query>
<bx:output>
    #variables.user[ "Michael" ].firstname#
    #variables.user[ "Michael" ].email#
</bx:output>

Caching Query Results

Queries can be cached by specifying cache=true and a cache timespan:

<bx:query
    name="totalUserCount"
    cache="true"
    cacheTimeout="#createTimespan( 0, 0, 0, 2 )#"
>
    SELECT * FROM users
</bx:query>

You can customize the cache usage by creating a custom cache configuration in your boxlang.json:

// boxlang.json
{	
    "caches": {
		// JDBC query store
		"bxQuery": {
			"provider": "BoxCacheProvider",
			"properties": {
				"evictCount": 1,
				"evictionPolicy": "LRU",
				"freeMemoryPercentageThreshold": 0,
				"maxObjects": 500,
				// 30 minutes if not used
				"defaultLastAccessTimeout": 1800,
				// 60 minutes default
				"defaultTimeout": 3600,
				"objectStore": "ConcurrentSoftReferenceStore",
				"reapFrequency": 120,
				"resetTimeoutOnAccess": false,
				"useLastAccessTimeouts": true
			}
		}
	}
}

Then reference the cache name in the query cacheProvider attribute:

<bx:query
    name="totalUserCount"
    cache="true"
    cacheTimeout="#createTimespan( 0, 0, 0, 2 )#"
    cacheProvider="bxQuery"
>
    SELECT * FROM users
</bx:query>

Empty query

Create an empty query object

<bx:script>
	myQuery = query();
</bx:script>

Query with some data

Create query object with some initial data

<bx:script>
	myQuery = query( foo=[
		1,
		2,
		3
	], bar=[
		"a",
		"b",
		"c"
	] );
</bx:script>

Additional Examples

myquery = query( columnName1=[ 
	1,
	2,
	3
], columnName2=[
	4,
	5,
	6
] );
dump( myquery );
column = "size";
values = [
	"small",
	"medium",
	"large"
];
myquery = query( "#column#"=values, column=values );
dump( myquery );
myquery = query( columnName=[] );
dump( var=myquery, label="empty query" );
myquery = query();
dump( var=myquery, label="no-argument query" );