Data Store REST API

The Data Store REST API provides remote Data Store management. Generally, the Data Store REST API service works with JSON data.

Configuration

For working with Data Stores, use the following URL: /api/v1/datastores.

https://custom-name.workfusion.com/workfusion/api/v1/datastores

API security

For authentication details, see WorkFusion REST API | API security.

Operations

For Data Store queries, observe the following rules:

• Add the ds_ prefix to the Data Store name.
• If a Data Store name includes UPPER-CASE characters, wrap the entire name in double quotes, for example: "ds_01_WL_CL_HT_DD".
• Use the Microsoft SQL syntax.

Request

Resource path	HTTP method	Parameters	Permissions	Example URL
/execute	POST	query (any kind of SQL query to execute)—required, query param drop\ query—restricted	Manage Data Stores	/api/v1/datastores/execute?query=DELETE FROM "ds_testDataStore"
/select	GET	query (SQL select query)—required, query param maxRows (fetch row count limit)—required, query param	View Data Stores Manage Data Stores	/api/v1/datastores/select?query=SELECT * FROM "ds_testDataStore"&maxRows=5
/{name}/createOrUpdate	POST	{name} (Data Store name)—required, path param dataStoreData—required, body param, consists of: columns (key-value map "name:type")—required originalCampaignUuid (optional) automationUseCaseId (optional)	isAuthenticated	/api/v1/datastores/testDataStore/createOrUpdate Body (Content-Type: application/json) { "columns": { "name":"TEXT" } }
/{name}/insert	POST	{name} (Data Store name)—required, path param rowData—required, body param, consists of: headers (array)—required values (array)—required	Manage Data Stores	/api/v1/datastores/testDataStore/insert
/{name}/delete	DELETE	{name} (Data Store name)—required, path param	Manage Data Stores	/api/v1/datastores/testDataStore/delete

For correct API work, when making requests, pay attention to the following recommendations:

• The select request should contain the Content-Type header.

  httpGet.addHeader("Content-Type", "application/json")

• The select query parameter should be encoded.

  URLEncoder.encode(raw, "UTF-8").replaceAll("\\+", "%20")

• The execute request should contain an empty encodedFormEntity body.

  httpPost.setEntity(new UrlEncodedFormEntity(new ArrayList<>()))

CreateOrUpdate

Path:

/api/v1/datastores/datastore_name/createOrUpdate

Body:

{
    "columns": {
        "name": "TEXT",
        "surname": "TEXT"
    }
}

Insert

Path:

/api/v1/datastores/datastore_name/insert

Body:

{
    "headers": [
        "name",
        "surname"
    ],
    "values": [
        "value1",
        "value2"
    ]
}

Response

The response consists of three parts:

• responseStatus: possible statuses are SUCCESS and FAILURE
• body: response data DTO
• errors: a collection of business errors in case of a failed request

Examples:

/api/v1/datastores/select?query=SELECT * FROM "ds_testDataStore"&maxRows=5

Success:

{
    "responseStatus": "SUCCESS",
    "body": {
        "rowData": [
            {
                "columnDescriptions": [
                    {
                        "type": 4,
                        "name": "ds_testDataStore_id"
                    },
                    {
                        "type": 12,
                        "name": "column1"
                    },
                    {
                        "type": 4,
                        "name": "column2"
                    }
                ],
                "rowData": [
                    1,
                    "value1",
                    5
                ]
            }
        ]
    },
    "errors": []
}

Failure:

{
    "responseStatus": "FAILURE",
    "body": null,
    "errors": [
        {
            "code": 0,
            "message": "StatementCallback; bad SQL grammar [SELECT COUNT(*) FROM [ds_country_risk_may_2018]]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'ds_country_risk_may_2018'"
        }
    ]
}

Access Data Store service in Manual Tasks

You can insert Data Store REST API calls in Manual Tasks using AJAX. For more details, see Allowed Data Store queries.

In that case, the service URL is as follows:

/workfusion/public/datastores

Security is provided by:

• hitId task parameter
• Allowed Data Store queries set in the Manual Task Designer
• Task status (works for active tasks only)