Getting Data and Calling Stored Procedures

Retrieving Table Content

To access the content of a particular table, you can use the following GET request:

TEXT

GET https://<datavirtuality-server-address>/api/v2/data/{schema}/{table}

Here is an example:

TEXT

GET https://us.pipes.datavirtuality.com/api/v2/data/myschema/users

Result:
[{"id":1, "Name":"Sherlock"}, {"id":2, "Name":"John"}]

If you want to reduce the amount of data transferred, you might want to use an SQL query endpoint with array=TRUE . It uses a 2D array instead of the JSON objects collection (see Example 1 in SQL Queries).

Calling Stored Procedures

Stored procedures can be invoked from REST API as well. Calling parameters should be provided in the request's body. Stored procedures may return an empty array, an array with a single object, or an array with multiple objects:

TEXT

POST https://<datavirtuality-server-address>/api/v2/procedure/{schema}/{procedure}

Here are several examples:

1. Calling the MD5 procedure from the SYSADMIN schema:

TEXT

POST https://us.pipes.datavirtuality.com/api/v2/procedure/sysadmin/md5
Body: 
{"param" : "please md5 it"}
// or
[{"param" : "please md5 it"}]

Result:
[{"hash":"4BC3338E788464C21918D09A64AB6212"}]

2. Calling the tryCastTimestamp procedure from the UTILS schema:

TEXT

POST https://us.pipes.datavirtuality.com/api/v2/procedure/utils/tryCastTimestamp
Body:
{
    "originalValue": "2022-04-01",
    "format": "yyyy-MM-dd",
    "checkFormatRegex": null
}

Result:
[{"newValue": "2022-04-01 00:00:00.0"}]

You can also make a request with several subsequent procedure calls with different parameters in a single REST request:

TEXT

POST https://us.pipes.datavirtuality.com/api/v2/procedure/utils/tryCastTimestamp
Body:
[
    {
        "originalValue": "2022-04-01",
        "format": "yyyy-MM-dd"
    },
    {
        "originalValue": "2022-04-02",
        "format": "yyyy-MM-dd"
    }
]

Result:
[
{"tryCastTimestamp_1": [{"newValue": "2022-04-01 00:00:00.0"}]},
{"tryCastTimestamp_2": [{"newValue": "2022-04-02 00:00:00.0"}]}
]