SQL Queries

You are looking at an older version of the documentation. The latest version is found here.

POST

XML

POST /rest/api/query

Request Body

SQL statement wrapped into the following JSON object:

XML

{ "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 `array=TRUE` and controls whether the column headers will be included in the response or not
`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 Data Virtuality Server (counted after the last use of the connection). To change the default value, set the desired one via the `rest.timeout` system property in the standalone.conf.props(.bat) file

Examples

Example 1

XML

POST http://<datavirtuality-server-address>:8080/rest/api/query?array=true&timeout=1200000

Body (application/json):

XML

{ "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

XML

POST http://<datavirtuality-server-address>:8080/rest/api/query?array=true&headers=true

Body (application/json):

XML

{ "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

XML

POST http://<datavirtuality-server-address>:8080/rest/api/query?array=false

Body (application/json):

XML

{ "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.

XML

GET /rest/api/query

Query 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 `array=TRUE` and controls whether the column headers will be included in the response or not
`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 Data Virtuality Server (counted after the last use of the connection). To change the default value, set the desired one via the `rest.timeout` system property in the standalone.conf.props(.bat) file

Examples

Here is an example of a call to query an endpoint using the GET method:

XML

GET http://<datavirtuality-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

{ "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

{ "sql": "SELECT * FROM (CALL SYSADMIN.getCurrentDWH()) temp" }