Introduction
This guide will provide details on how to utilise the SQL Query feature that is part of the Database V2 Connector.
The Connector has many new features though a less known feature is the ability to create a SQL Statement from user input. In addition, it allows the user to have complete control of the SQL that is provided to the Database engine. From https://help.boomi.com/docs/atomsphere/integration/connectors/int-database_v2_operation_36b0cb58-24e1-42b7-8e2d-7343edcc73ac "...During the import action for Insert, Get, Update, Delete operation, user can opt to provide the SQL query as part of the input request using the Enable SQL Query check box."
This has the benefits of:
- Predictability: There is no ambiguity of the SQL that will run and can make the Boomi process easier to debug
- Performance: Since the SQL statement is provided, there is no overhead in having the runtime craft the statement based on user input
- Simplicity: Providing the SQL helps to understand the data requirement(s) with no misinterpretation.
Prerequisites/Audience
You need access to the Database V2 Connector and a database where you can issue SQL statements (including select and delete) if you want to try out the instructions in this article.
The details are aimed at anyone with any level of Boomi experience and a mimimum level of SQL knowledge.
Note that the instructions in this article utilise a MariaDB database; please adapt for your own engine.
The Process
Demo
The following provides a demo of the process in action.
Overview
This guide will provide details on how to utilise the SQL Query feature that is part of the Database V2 Connector (see https://help.boomi.com/docs/Atomsphere/Integration/Connectors/int-Database_V2_operation_36b0cb58-24e1-42b7-8e2d-7343edcc73ac for details)
Installation
The process is available as a Bundle at https://platform.boomi.com/BoomiLabs.html#pub_bundles;/tab=my_bundles/bundle=0c3461e8-5ff0-4ac5-9cca-8382dc55efb3/bundleOwner=true. Refer to https://help.boomi.com/docs/Atomsphere/Bundles/bundles_Installing_a_Bundle on how to install a Bundle.
Ensure you update the Database Connection with the necessary details including a schema.
You also need to update the (xref) Dynamic SQL Cross Reference table to have valid fields that together form a valid SQL. See the process for a sample.
Description
The sample process utilises the GET operation to build a SQL statement to get order details from the database. The SQL statements are broken up into the following fields where a Cross Reference table is used:
- Field: select field(s)
- Table: from table
- Criterion: one or more criterion (where criterion)
- Id: unique identifier that allows the record to be extracted from the Cross Reference table.
For instance, given the following values, the SQL created isselect * from crm.orderDet where orderDate is not null limit 10
| Id | Field | Table | Criterion |
|---|---|---|---|
| 1 | * | crm.orderDet | orderDate is not null limit 10 |
This SQL statement is sent to the database engine via the SQLQuery attribute that is part of the Database V2 Request JSON Profile (Database V2 Generic GET Request in the sample process).
Creating the DB Profile
Create the Profile by performing the following:
- Get Operation Type: Standard Get (refer to Table Action/Operation Settings)
- Enable SQL Query: checked.
The following table provides a capability and requirement overview when using this feature. Ensure your Action and Operation are correct otherwise you will get an error.
Action/Operation Settings
| When Connector Action Is | Operation Needs to be | SQL Statements that can be used |
|---|---|---|
| GET | Standard Get | DML (select) |
| INSERT | Standard Insert | DML (insert, update, delete) DDL (create, drop, alter, truncate etc) |
| UPDATE | Standard Update | DML (insert, update, delete) DDL (create, drop, alter, truncate etc) |
| DELETE | Standard Delete | DML (insert, update, delete) DDL (create, drop, alter, truncate etc) |
The following screenshot shows a GET Import Operation (the schema name is set in the Connection).
Setting the SQL Query
The SQL is built up by using the ddpId to perform a Cross Reference lookup where the following parts are combined to populate ddpSQL:
- ddpCol: field column
- ddpTbl: table column
- ddpCriteria: criterion column
Testing the Process
Running the process should provide the results of the SQL that has been dynamically created.