Create an ARRAY of rows from all records in a table and send it as a payload in a single HTTP call
In this example, we create a Flow that reads all the records from a database table, creates a JSON ARRAY that includes all the records, and sends it as a payload to the API endpoint in a single HTTP call.
In Connections
Access Connections here.
Step 1. Create a source database Connection.
Step 2. Create a destination HTTP Connection.
Step 3. Create a JSON Format with all the default settings.
In Flows
Access Flows here.
Step 4. Create a Database to Web Service
Flow.
Step 5. Add a new source-to-destination transformation. Select FROM
Connection created in step 1 and TO
Connection and Format created in steps 2 and 3. Select the source table name and the destination endpoint. Optionally, you can modify the Source SQL under MAPPING
.
Step 6. Modify the per-field Mapping if needed.
Step 7. Test the transformation.
Assuming that the following fields are in the source table (or fields returned by the Source Query): id
, first_name
, last_name
, dob
the output from the test transformation will look like this:
[{"id":1, "first_name":"Joe", "last_name":"Doe", "dob":"01/01/2000",
....
{"id":2, "first_name":"Jane", "last_name":"Doe", "dob":"01/01/2001"]
If you are happy with the output and the payload has the expected structure, then you are done and you can save the Flow.
Otherwise, use one of the available transformations to modify the payload. Repeat step 7 until it's done.
If the payload has a complicated nested structure, you might consider using JavaScript to create a nested JSON or XML document.
Create a payload from each record in a table and send it to the HTTP endpoint, one record at a time
In this example, we create a Flow that reads records from a database table (actually SELECT
statement), modifies the custom payload by replacing {tokens}
with the values read from the database, and sends the HTTP request using a new payload, one record at a time.
Step 1. Create a database Connection.
Step 2. Create an HTTP Connection.
In Flows
Access Flows here.
Step 3. Create a Flow Call HTTP endpoint
.
Step 4. Use the Connection created in step 2 as a Connection for the Flow created in step 3.
Step 5. Select Parameters
tab and enter the payload using {tokens}
, where the tokens are field names in a source table.
Assuming that the following fields are in the source table: id
, first_name
, last_name
, dob
and the payload looks like the following:
{
"source": "test",
"userId": {id},
"firstName": {first_name},
"lastName": {last_name},
"dateOfBirth": {dob}
}
The output after merging the data with the template will look like this:
{
"source": "test",
"userId": 1,
"firstName": "Joe",
"lastName": "Doe",
"dateOfBirth": "01/01/2000"
}
Step 6. Create a nested flow
.
Step 7. Add Flow created in steps 3 to 5 to the nested flow
and click edit condition
.
Step 8. Set Loop Type
to SQL, Connection
to the Connection created in step 1 and enter the driving SQL in the Loop Script
field.
The field names in the SELECT
statement are case-sensitive.
Modify URL parameters using data in each record in a table and make an HTTP call, one record at a time
In this example, we create a Flow that reads records from a database table (actually SELECT
statement), modifies the URL with parameters for the HTTP endpoint by replacing {tokens}
with the values read from the database, and sends the HTTP request using a new URL, one record at a time.
Step 1. Create a database Connection.
Step 2. Create an HTTP Connection.
When creating a Connection, use {tokens}
as a part of the URL to customize URL parameters.
Assuming that the following fields are in the source table: id
, first_name
, last_name
, dob
and the tokenized URL looks like the following:
https://myapi.com/?id={id}&firstName={first_name}&lastName={lastName}&dob={dob}
The updated URL for each record in a table will look like this:
https://myapi.com/?id=1&firstName=Joe&lastName=Doe&dob=01/01/2000
In Flows
Access Flows here.
Step 3. Create a Flow Call HTTP endpoint
.
Step 4. Use the Connection created in step 2 as a Connection for the Flow created in step 3.
Step 5. Create a nested flow
.
Step 6. Add Flow created in steps 3 to 5 to the nested flow
and click edit condition
.
Step 7. Set Loop Type
to SQL, Connection
to the Connection created in step 1 and enter the driving SQL in the Loop Script
field.
The field names in the SELECT
statement are case-sensitive.
Comments
0 comments
Please sign in to leave a comment.