Configuring a database connection
Introduction
To configure a database connection, add a connector shape to your process flow in the normal way and select the required source instance and source query for an existing database connector:
Having selected a source instance, you'll know if you're working with a database connector because the subsequent field requires you to choose a source queryrather than a source endpoint.
Generally, database connector settings work on the same principles as 'normal' connectors but there are differences, depending on whether you're using a query that receives or sends data.
Receive queries
When a connector shape is configured with a receive type query (i.e. you're receiving data from a database), you'll see settings sections for variables, error handling, and response handling:
These options are summarised below.
Receive options
Variables
Custom
Any query variables defined for the selected query are displayed here. Variables may be mandatory (denoted with an asterisk) so a value must be present at runtime, or optional.
Error handling
Retries
Sets the number of retries that will be attempted if a connection can't be made. You can define a value between 0 and 2. The default setting is 1.
Response handling
Save response in payload
Set this option to on to save the response from the completed operation IN the payload for subsequent processing.
This option provides the ability to access the response body via payload parameters.
By default, the response is saved in a field named response however, when the save response in payload option is toggled on, you can specify your preferred field name.
Response data
When a process flow runs using a receive type query, received data is returned in a payload. You can view this data in logs - for example:
Send queries
When a connector shape is configured with a send type query (i.e. you're sending data to a database), you'll see settings sections for variables, database, error handling, and response handling:
These options are summarised below.
Send options
Variables
Custom
Any query variables defined for the selected query are displayed here. Variables may be mandatory (denoted with an asterisk) so a value must be present at runtime, or optional.
Database
Override query
You can enter your own database query here which will be run instead of the source query already selected.
Before using an override query you should ensure that:
target columns exist in the database
target columns are configured (in the database) to accept
nullvalues.
Items path
If your incoming payload contains a single array of items, enter the field name here. In doing so, the process flow loops through each item in the array and performs multiple inserts as one operation.
For more information please see About item paths.
Error handling
Retries
Sets the number of retries that will be attempted if a connection can't be made. You can define a value between 0 and 2. The default setting is 1.
Response handling
Save response as payload
Set this option to on to save the response from the completed operation as a payload for subsequent processing.
Save response in payload
Set this option to on to save the response from the completed operation IN the payload for subsequent processing.
This option saves the response from your database, together with the payload that was sent in.
By default, the response is saved in a field named response however, when the save response in payload option is toggled on, you can specify your preferred field name.
About item paths
The items path field is used to run the associated query for multiple items (i.e. database rows) in a single operation, by looping through all items in a given array:
Here, specify the field name in your payload associated with the array to be targeted.
Example (simple array)
In the example below, our payload includes one products field with items in an array:
{
"products": [
{
"id": 4001,
"sku": "BAG-001",
"colour": "Blue",
"category": "Bags",
"quantity": 11
},
{
"id": 4002,
"sku": "BAG-002",
"colour": "Green",
"category": "Bags",
"quantity": 11
},
{
"id": 4003,
"sku": "BAG-003",
"colour": "Pink",
"category": "Bags",
"quantity": 12
},
{
"id": 4004,
"sku": "BAG-004",
"colour": "Red",
"category": "Bags",
"quantity": 10
}
]
}
To send all these items into our database, the items path should be set to:
products
Example (nested arrays)
In the example below, our payload includes a top-level products array with nested accessories and footwear arrays:
{
"products": [
{
"accessories": [
{
"id": 4,
"sku": "BAG-003",
"colour": "Green",
"category": "Bags",
"quantity": 11
},
{
"id": 5,
"sku": "BAG-004",
"colour": "Pink",
"category": "Bags",
"quantity": 12
}
]
},
{
"footwear": [
{
"id": 6,
"sku": "SHOE-001",
"colour": "Black",
"category": "Shoes",
"quantity": 20
},
{
"id": 7,
"sku": "SHOE-002",
"colour": "White",
"category": "Shoes",
"quantity": 15
},
{
"id": 8,
"sku": "BOOT-001",
"colour": "Brown",
"category": "Boots",
"quantity": 10
}
]
}
]
}
To send all accessories items into our database, the items path should be set to:
products.0.accessories
To send all footwear items into our database, the items path should be set to:
products.1.footwear
The items path field can't be used to target individual payload items. For this, you'd need an alternative approach - for example, you might choose to override query and provide a more specific query to target the required item.
Example (target specific item)
In the example below, our payload includes a top-level products array with nested accessories and footwear arrays:
{
"products": [
{
"accessories": [
{
"id": 4,
"sku": "BAG-003",
"colour": "Green",
"category": "Bags",
"quantity": 11
},
{
"id": 5,
"sku": "BAG-004",
"colour": "Pink",
"category": "Bags",
"quantity": 12
}
]
},
{
"footwear": [
{
"id": 6,
"sku": "SHOE-001",
"colour": "Black",
"category": "Shoes",
"quantity": 20
},
{
"id": 7,
"sku": "SHOE-002",
"colour": "White",
"category": "Shoes",
"quantity": 15
},
{
"id": 8,
"sku": "BOOT-001",
"colour": "Brown",
"category": "Boots",
"quantity": 10
}
]
}
]
}
To insert the FIRST item in the footwear array, we'd leave the items path field blank and enter the following override query:
INSERT INTO products (id, sku, colour, category, quantity) VALUES (:products.1.footwear.0.id, :products.1.footwear.0.sku, :products.1.footwear.0.colour, :products.1.footwear.0.category, :products.1.footwear.0.quantity)Response data
When a process flow runs using a send type query, the default behaviour is for the response from your database to be returned as the payload. You can view this in logs - for example:
Related pages
Last updated