09. Working with Databases

Many integrations require interaction with SQL databases, Flowgear provides two types of Connectors for this purpose.

• Query Connectors allow a query to be provided and the result set to be returned to the platform.
• Table Update Connectors allow for batch insert, update or upsert (insert or update) of rows using a user-specified composite custom key.

Native connectors are available for Microsoft SQL, MySQL, Oracle and PostgreSQL and generic connectors are offered for ODBC and OLEDB. See SQL Connectors for more information about the available connectors.

More about Table Update

When the AllowUpdate Property is enabled, the Table Update Nodes allow one or more fields to be set as key fields. For example, if you set the key field to id, then if a row contains an id that matches an existing row it will be updated rather than inserted.

You can also specify complex key fields - for example, you can specify id,email to match both the id and the email Property.

It's important to note that the fields you specify here do not need to be primary keys or indexed fields in the target database although performance will probably be better when they are.

Exercise 11: Working with Databases

In this exercise, we'll read contact information from one table in a database and then write those contacts to another table.

1. Add Microsoft SQL Query, renamed to Query to the Workflow.

2. Create a new Connection for the Microsoft SQL Query Node and set these Properties:

    Se​rver: CertificationTrainingDb.flowgear.net
    Da​tab​ase: CertificationTrainingDb
    User​I​d: CertificationTrainingDb
    Pas​swo​rd: Training2019!
   

   Test and save the Connection. You will only be able to connect to this server from within your Flowgear Cloud environment.

3. Back on the Workflow, open the Query Property. The list of available tables should display in the tree-view on the left-hand side.

4. Set the query to select top 10 * from Contacts.

   Test and save the Connection. You will only be able to connect to this server from within your Flowgear Cloud environment.

   Click Execute to see a preview of the data, click ✓ to commit the Property value.

5. Connect Start.RunNow → Microsoft SQL Query.

   Run the Workflow to confirm the expected data is being returned.

   We will now use Microsoft SQL Table Update to insert the data we retrieved from the Contacts table into the NewContacts table.

6. Add QuickMap and Microsoft SQL Table Update, renamed to TableUpdate to the Workflow.

7. Create and assign a Connection on the TableUpdate using the same credentials as the one for the Query Node.

8. Choose the Insert into NewContacts Node Sample for TableUpdate.

9. Connect Query.ResultXml → QuickMap.Source, QuickMap.Result → TableUpdate.TableXml.

10. In the QuickMap mapping (accessed from the View/Change link in QuickMap.Mapping), click Tools → Mirror Map to map all identically named fields.

11. Map Table → NewContacts.

12. Set a Map Expression on company_name to LEFT({company_name}, 15) to trim the field to a size that is within the limit for the target table.

13. Commit the changes by clicking ✓.

14. Back in the Workflow, set TableUpdate.KeyFields to id,email and ensure that both TableUpdate.AllowInsert and TableUpdate.AllowUpdate are checked.

15. Connect Query → QuickMap and QuickMap → TableUpdate.

    Run the Workflow to confirm that 10 rows have been inserted or updated in the NewContacts table.

Save your Workflow, then click Submit Exercise to grade it.