# 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]
username=[string]
password=[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`.                                          |         |
| `username`               | `string`   | `false`  |                                                                                                |         |
| `password`               | `string`   | `false`  |                                                                                                |         |
| `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.                            |         |
| `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:

```html
<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:

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

By default, the results will be in [Query](https://boxlang.ortusbooks.com/boxlang-language/reference/types/query) format. [Array](https://boxlang.ortusbooks.com/boxlang-language/reference/types/array) and [Struct](https://boxlang.ortusbooks.com/boxlang-language/reference/types/struct) results are also supported:

```html
<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:

```html
<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:

```html
<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`](https://boxlang.ortusbooks.com/getting-started/configuration#boxlang.json):

```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:

```html
<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

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

```

### Query with some data

Create query object with some initial data

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

```

### Additional Examples

```java
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" );

```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://boxlang.ortusbooks.com/boxlang-language/reference/components/jdbc/query.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.