> ## Content Index
> Fetch the complete content index at: https://www.fromthegarage.com.au/llms.txt
> Use this file to discover other available public pages before exploring further.

# Being more Direct: Creating SQLs Dynamically Using the DBv2 Connector
- URL: https://www.fromthegarage.com.au/technology/being-more-direct-using-the-boomi-database-v2-sql-query/
- Published: 2025-01-26T06:09:29.000Z
- Updated: 2026-07-09T07:36:26.000Z
- Author: Peter Antony
- Tags: technology

## **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](https://help.boomi.com/docs/atomsphere/integration/connectors/int-database%5Fv2%5Foperation%5F36b0cb58-24e1-42b7-8e2d-7343edcc73ac?ref=fromthegarage.com.au) "...*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.

0:00 

/0:39 

1× 

### **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](https://help.boomi.com/docs/Atomsphere/Integration/Connectors/int-Database%5FV2%5Foperation%5F36b0cb58-24e1-42b7-8e2d-7343edcc73ac?ref=fromthegarage.com.au) 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](https://platform.boomi.com/BoomiLabs.html?ref=fromthegarage.com.au#pub%5Fbundles;/tab=my%5Fbundles/bundle=0c3461e8-5ff0-4ac5-9cca-8382dc55efb3/bundleOwner=true). Refer to [https://help.boomi.com/docs/Atomsphere/Bundles/bundles\_Installing\_a\_Bundle](https://help.boomi.com/docs/Atomsphere/Bundles/bundles%5FInstalling%5Fa%5FBundle?ref=fromthegarage.com.au) 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 is  
`select * 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).

![](https://www.fromthegarage.com.au/content/images/2025/02/Untitled.png)

### **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**).

![](https://www.fromthegarage.com.au/content/images/2025/02/Import.png)

### **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*:

1. **ddpCol:** *field* column
2. **ddpTbl:** *table* column
3. **ddpCriteria:** *criterion* column

### Testing the Process

Running the process should provide the results of the SQL that has been dynamically created.

#### References

[https://help.boomi.com/docs/Atomsphere/Integration/Connectors/int-Database\_V2\_operation\_36b0cb58-24e1-42b7-8e2d-7343edcc73ac](https://help.boomi.com/docs/Atomsphere/Integration/Connectors/int-Database%5FV2%5Foperation%5F36b0cb58-24e1-42b7-8e2d-7343edcc73ac?ref=fromthegarage.com.au)