Edit

Data Store REST API

The Data Store REST service is used for remote DataStore management through WorkFusion application.

Generally, the REST service works with JSON data.

Configuration

caution

To work with Data Stores, use the /soap/v1/datastores URL.

URL examples:

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

API security

All API postings are made over a Secure Sockets Layer (SSL) connection, which encrypts communications between the user and web server to ensure data remains private.

note

All requests must be preceding with https://.

WorkFusion REST API supports basic (login and password hash) authentication to ensure that APIs are only accessible to those with the proper credentials.

Basic authentication

• username: Your username in Control Tower.
• password: Hash of your password in Control Tower.

REST API with SSO

When using REST API with SSO you cannot use your SSO ID and password. The recommended approach for using REST API with SSO enabled is the following:

1. Disable SSO: wf.sso.saml.enable=false
2. Restart Control Tower: wfmanager restart workfusion
3. Log in to Workfusion with pre-SSO ID and password.
4. Create a special REST API user and grant necessary permissions by assigning a proper role.
5. Enable SSO: wf.sso.saml.enable=true
6. Restart Control Tower: wfmanager restart workfusion
7. Use the /workfusion/api/dologin enpoint to log in. /workfusion/dologin is disabled once you turn on SSO.

Now, SSO is enabled, but you can use the REST API user ID and password for the REST API authentication.

Operations

Rules applied to the Data Store queries are as follows:

• You need to add the ds_ prefix to the Data Store name.
• If you have some UPPERCASE characters in the Data Store name, wrap the whole name in double quotes, for example: "ds_01_WL_CL_HT_DD".
• Use the PostgreSQL syntax.

Request

Name	Resource path	HttpMethod	Parameters	Permissions	Example URL
Execute	/execute	POST	query (any kind of SQL query to execute): required, query param.	Manage Data Stores	/soap/v1/datastores/execute?query=DELETE FROM "ds_testDataStore"
Select	/select	GET	query (SQL select query): required, query param. maxRows (fetch row count limit): required, query param.	View Data Stores Manage Data Stores	/soap/v1/datastores/select?query=SELECT * FROM "ds_testDataStore"&maxRows=5
CreateOrUpdate	/{name}/createOrUpdate	POST	{name}: Data Store name, required, path param. dataStoreData:required, body param, consists of: key-value map "name:type" columns (required), originalCampaignUuid (optional), automationUseCaseId (optional).	isAuthenticated	/soap/v1/datastores/testDataStore/createOrUpdate
Insert	/{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	/soap/v1/datastores/testDataStore/insert

The examples are as follows:

• CreateOrUpdate:

  /soap/v1/datastores/datastore_name/createOrUpdate
  

  Body:

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

• Insert:

  /soap/v1/datastores/datastore_name/insert
  

  Body:

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

• CURL with basic authentication: Replace the username with a proper username, userpasswordhash with a password hash (obtained from the MySQL ApplicationUser table, field password), and your.server.name with your actual server name. You should get first five records from the WFML_Job_Data Data Store.

  curl -u 'username:userpasswordhash' -X GET 'https://your.server.name/workfusion/soap/v1/datastores/select?query=SELECT%20%2A%20FROM%20%22ds_WFML_Job_Data%22&maxRows=5'
  

Response

Consists of three parts:

• responseStatus: "SUCCESS", "FAILURE"
• body: response data DTO
• errors: collection of business errors, if the request fails

The examples are as follows:

/soap/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 * from \"ds_testDataStores\"]; nested exception is org.postgresql.util.PSQLException: error: relation \"ds_testDataStores\" doesn't exist\n  Position: 15"
        }
    ]
}

Access Data Store service in Manual Tasks

You can insert Data Store REST API calls in Manual Tasks using AJAX. For details, refer to Allowed Data Store Queries.

In that case, the service URL is:

/workfusion/public/datastores

The security is provided by:

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

isAuthenticated()