SQL Queries
POST
POST /rest/api/queryRequest Body
SQL statement wrapped into the following JSON object:
{ "sql": "SELECT * FROM SYS.Tables" }Query Parameters
All parameters are optional.
| Parameter | Type | Default value | Description |
|---|---|---|---|
array | boolean | TRUE | If TRUE, returns a 2D array, otherwise returns an array of objects (see examples below) |
headers | boolean | FALSE | Works only with |
pagination | boolean | FALSE | Activates the pagination mode |
requestid | string | "" | Allows to use a cursor with the provided identifier which has been buffered in a previous request |
limit | long |
-1
| Restricts the number of results returned from the SQL statement |
offset | long | -1 | Excludes the number of results returned from the SQL statement |
timeout | long | 600000 | Timeout in milliseconds before closing connection to the CData Virtuality Server (counted after the last use of the connection). To change the default value, set the desired one via the |
Examples
Example 1
POST http://<cdatavirtuality-server-address>:8080/rest/api/query?array=true&timeout=1200000Body (application/json):
{ "sql": "SELECT * FROM SYS.Tables" }
STATUS 200
[
["datavirtuality","SYS","Columns","Table",null,true,false,"tid:2cb59cfd55db-9bd42fbd-00000001",-1,null,true,false,1],
["datavirtuality","SYS","DataTypes","Table",null,true,false,"tid:2cb59cfd55db-6130d2cf-00000021",-1,null,true,false,2]
]Example 2
POST http://<cdatavirtuality-server-address>:8080/rest/api/query?array=true&headers=trueBody (application/json):
{ "sql": "SELECT * FROM SYS.Tables" }
STATUS 200
[
["VDBName","SchemaName","Name","Type","NameInSource","IsPhysical","SupportsUpdates","UID","Cardinality","Description","IsSystem","IsMaterialized","OID"],
["datavirtuality","SYS","Columns","Table",null,true,false,"tid:2cb59cfd55db-9bd42fbd-00000001",-1,null,true,false,1],
["datavirtuality","SYS","DataTypes","Table",null,true,false,"tid:2cb59cfd55db-6130d2cf-00000021",-1,null,true,false,2]
]Example 3
POST http://<cdatavirtuality-server-address>:8080/rest/api/query?array=falseBody (application/json):
{ "sql": "SELECT * FROM SYS.Tables" }
STATUS 200
[
{"VDBName":"datavirtuality","SchemaName":"SYS","Name":"Columns","Type":"Table","NameInSource":null,"IsPhysical":true,"SupportsUpdates":false,"UID":"tid:2cb59cfd55db-9bd42fbd-00000001","Cardinality":-1,"Description":null,"IsSystem":true,"IsMaterialized":false,"OID":1},
{"VDBName":"datavirtuality","SchemaName":"SYS","Name":"DataTypes","Type":"Table","NameInSource":null,"IsPhysical":true,"SupportsUpdates":false,"UID":"tid:2cb59cfd55db-6130d2cf-00000021","Cardinality":-1,"Description":null,"IsSystem":true,"IsMaterialized":false,"OID":2}
]GET
While we recommend using the POST method (as it is more secure), the GET method can also be used, e.g., for demo purposes and in some special cases.
GET /rest/api/queryQuery Parameters
All parameters, except sql, are optional.
| Parameter | Type | Default value | Description |
|---|---|---|---|
sql | string | Encoded SQL statement | |
array | boolean | TRUE | If TRUE, returns a 2D array, otherwise returns an array of objects (see example below) |
headers | boolean | FALSE | Works only with |
pagination | boolean | FALSE | Activates the pagination mode |
requestid | string | "" | Allows to use a cursor with the provided identifier which has been buffered in a previous request |
limit | long |
-1
| Restricts the number of results returned from the SQL statement |
offset | long | -1 | Excludes the number of results returned from the SQL statement |
timeout | long | 600000 | Timeout in milliseconds before closing connection to the CData Virtuality Server (counted after the last use of the connection). To change the default value, set the desired one via the |
Examples
Here is an example of a call to query an endpoint using the GET method:
GET http://<cdatavirtuality-server-address>:8080/rest/api/query?sql=select%201&array=false&timeout=1200000
STATUS 200
[{"expr1":1}]Executing Stored Procedures
You can call stored procedures as well - just remember to escape double quotes in your SQL statement:
{ "sql": "CALL SYSADMIN.addUser(\"name\" => 'test2', \"pwd\" => 'string_pwd', \"role_name\" => 'admin-role')" }A typical response in such cases is an empty array with STATUS 200.
Calling a stored procedure that returns a result set is typically done by wrapping the call with a SELECT statement:
{ "sql": "SELECT * FROM (CALL SYSADMIN.getCurrentDWH()) temp" }Timeout
If a query times out, the following happens:
- The
200 Okresponse is returned; - The data obtained before the query timed out are returned in JSON;
- At the end of the data, the following error message is added as a new field in the JSON file:
The datasource is closed by timeout. Please, provide a timeout either explicitly in your request or in rest.timeout system property(for information onrest.timeout, see System Properties); - The same error is recorded in the log.