QSQL
This API is intended to run on a data access process and execute the command sent in from the client.
Note that, by default, this API is only enabled in sandboxes. To enable it in non-sandboxes, set the KXI_ALLOWED_SBX_APIS in the RCs and DAPs in which you want to enable it (see Database configuration).
.kxi.qsql requests are subject to database-level entitlements, but are NOT subject to row-level entitlements. The following DAP log messages warn that both the API and row-level entitlements are enabled:
INFO KXAPI Initializing KXAPI utils, sc=da
WARN KXAPI .kxi.qsql API is not subject to row-level entitlements, consider disabling
Parameter
| Name | Type | Description |
|---|---|---|
| query | string | Required - the qSQL command to run on a target data access process. It accepts: a q expression, e.g. "instrument[AAABBB]"; a lambda, e.g. "{instrument[AAABBB]}" |
| agg | string | Optional - the qSQL command to run on the aggregator process, acting on the list of results provided by the data access processes. Defaults to raze if not specified. It accepts: single named unary functions, e.g. "distinct"; compositions of named unary functions, e.g. "'[distinct;raze]"; unary lambdas where the input parameter is the list of results from the data access processes, e.g. "{distinct raze x}" |
Note that, unlike the query parameter, agg does not accept simple q expressions, e.g. "distinct raze x". This is because the query does not take any input parameters, whereas the agg takes the result of query from the data access processes. Therefore agg must be an expression that can have a results variable applied to it, i.e. a named function or lambda.
Response
The response is dependent on the query parameter itself and the result of executing it on the target.
For issues with the query or responses checkout the troubleshooting section here.
Application Codes
| AC | Description |
|---|---|
| INPUT | If a non-string is passed in for the query argument |
| TYPE | A qSQL statement results in a type error. e.g "where id=`a", when id is an int column |
| LENGTH | A qSQL length error. e.g "where id=1 2", when column doesn't have a count of 2 |
Examples
For these examples assume the table t was created in a target process with
t:([]id:til 10)
and that a callback function cb is set to {res::(x;y)} which saves the response header and payload to a global
In this example we do a simple select from the target table t for the id of 4. Response and application codes are both OK.
Open a connection to the database
The h variable below is defined as an IPC connection to the Service Gateway. For example, `:insights-qe-gateway:5050 would connect to the query environment gateway within an insights namespace. For more information on how you can establish an tcp/ip connection from outside of your cluster see the external IPC connection page.
q)res:neg[h](`.kxi.qsql; enlist[`query]!enlist"select from t where id=4";`cb;()!())
q)res 0
rc| 0
ac| 0
q)res 1
id
--
4
Here we run a badly formatted query with a where clause that checks equality between a long column and a character resulting in a type error. Response payload is null and has a header with an RC of APP_DB and an AC of TYPE.
q)res:neg[h](`.kxi.qsql; enlist[`query]!enlist"select from t where id=`a";`cb;()!())
q)res 0
rc| 6
ac| 11
q)(::)~res 1
1b
For a query with an agg specified, assume a number of DAP tiers have a table tab defined:
tab:([]sym:10?`AA`BB;val:10?10)
q)res:neg[h](`.kxi.qsql; `query`agg!("select mxVal:max val by sym from tab";"{select max mxVal by sym from raze x}");`cb;()!())
q)res 1
sym| mxVal
---| -----
AA | 7
BB | 8