SQL Queries
POST
Here is what the syntax for a POST request looks like:
TEXT
POST https://<datavirtuality-server-address>/api/v2/query
Body: { "sql": "SELECT * FROM schema.table" }Here is an example:
TEXT
POST https://us.pipes.datavirtuality.com/api/v2/query
Body: { "sql": "SELECT * FROM SYS.Tables" }
Result:
[
["datavirtuality", "views", "users", "View", null, false, false, "tid:321470c0ca3a-06a68e08-000008ed", -1, null, false, false, "tid:cb62f78f-522f-4c35-a9bb-8dcd4f0fa9f2"],
["datavirtuality", "views", "users2", "View", null, false, false, "tid:321470c0ca3a-ce2b332a-0000095a", -1, null, false, false, "tid:cb62f78f-522f-4c35-a9bb-8dcd4f0fa9f2"]
]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 the use of 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 |
Examples
Example 1
TEXT
POST https://us.pipes.datavirtuality.com/api/v2/query?array=true
Body:
{ "sql": "SELECT * FROM SYS.Tables" }
Result:
[
["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
TEXT
POST https://us.pipes.datavirtuality.com/api/v2/query?array=true&headers=true
Body:
{ "sql": "SELECT * FROM SYS.Tables" }
Result:
[
["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
TEXT
POST https://us.pipes.datavirtuality.com/api/v2/query?array=false
Body:
{ "sql": "SELECT * FROM SYS.Tables" }
Result:
[
{"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. Here is the syntax for a GET request:
TEXT
GET https://<datavirtuality-server-address>/api/v2/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 |
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 |
Example
Here is an example of a call to query an endpoint using the GET method:
TEXT
GET https://us.pipes.datavirtuality.com/api/v2/query?sql=select%201&array=false
Result:
[{"expr1":1}]Executing Stored Procedures
You can call stored procedures as well - remember to escape double quotes in your SQL statement:
TEXT
{ "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:
TEXT
{ "sql": "SELECT * FROM (CALL SYSADMIN.getCurrentDWH()) temp" }