Database Profile

Database Profiles represent the data set to be retrieved from or written to a relational database. They contain the actual SQL used to select or insert/update/delete records. A given Profile is paired with a specific Database Connector Operation.

There are two Types of Profiles:

• Read - Read Profiles are used to extract records from a database. They contain a single SELECT statement (or Stored Procedure call) and the fields or elements returned in the result set. You will use them In a Data Map, the Read Profile would be referenced as the Source Profile.
• Write - Write Profiles are used to write records to a database. They contain one or more INSERT/UPDATE/DELETE statements (or Stored Procedure calls) and the input fields for those statements. In a Data Map, the Write Profile would be referenced as the Destination Profile. You can defined multiple statements to insert or update parent-child relationships in one Map.

When extracting data from a database, you can only use a single query. However, that query may join multiple tables and perform other filtering logic.

SQL Syntax Differences in SQL syntax exist between the different database vendors. Your SQL statements will be parsed by the database you are connecting to: Oracle's SQL syntax is slightly different than Microsoft SQL Server's syntax. Refer to the documentation provided by your database vendor for syntax details.

Read Profiles

For Read Profiles, the Data Elements Tab is where you define the SQL statement to extract data and the columns returned by that query.

To define the SQL statement, click the Statement node in the middle. Then on the right, click Browse... to launch the Database Table Browser to auto-generate the statement and output fields, or click Edit to enter or cut-and-paste your SQL Statement. Note: You can modify the auto-generated query created by the Table Browser to include additional logic such as JOINs and WHERE clauses.

If you choose not to use the Table Browser and enter your own query, you will need to manually create Outputs to match the number of columns returned by the query. You can execute any valid SQL statement supported by the database application you're connecting to. The query may be as simple or as complex (with JOINs, subqueries, WHERE clauses, GROUP BY, ORDER BY, UNIONs, etc.) as required.

Leverage the Power of SQL SQL allows you to perform some pretty complex logic when extracting data. As a best practice, use the SQL query to perform the necessary joining, filtering, sorting, grouping, even minor formatting before the data is sent into the Boomi Process. This can greatly simplify the logic required in the Boomi Process and can execute more efficiently because it is handled by the driver on the database server.

Stored Procedures

If you need to call a stored procedure to extract data instead of a SQL statement, simply choose Stored Procedure Read as the Type and enter the Stored Procedure Name below. You will need to create the Outputs manually to match the number of columns returned by the stored procedure.

Adding Outputs

Outputs represent the individual columns or fields returned by the query or stored procedure. These are the elements you can map from in a Data Map. If you used the Table Browser the fields are created automatically; however if you entered a SQL statement manually or wish to modify the auto-generated query, you will need to add fields manually.

To add a field, click the dropdown arrow () next to Outputs and choose Add Field. Click on the newly created field to edit its properties to the right. Repeat these steps for each field returned by the query.

The columns returned are matched to the Output fields by position, not by name. You can change the order of the Outputs by dragging and dropping one field onto another.

To remove a field, simply click the dropdown arrow and choose Delete Field.

Be aware that the number of Outputs defined must match the number of columns returned. If not, the Process will fail during execution. Also the order of the Outputs must match the columns returned as well.

Using Parameters

Parameters allow you to insert dynamic values into your query, typically in the WHERE clause(s) to filter results. For example, if you wanted to only extract records that have been created or modified since the last time the Boomi Process ran, you could design the query to accept a date value passed in from the Connection (for more information, see Setting Parameters).

To use parameters, you will need to modify the SQL statement itself. Click the Statement node and then click Edit to open the SQL Editor. Modify the SQL statement and put a question mark to serve as a placeholder for each dynamic parameter should be inserted. Click OK when finished. For example:

SELECT ORDER_ID, CUSTOMER_NAME, ORDER_DATE FROM SALES_ORDER_HDR WHERE DIVISION = ? AND ORDER_DATE > ?

This query contains two placeholders: one for DIVISION and one for ORDER_DATE. In the Profile, you need to define two parameters that will be used to pass values into the query's placeholders (The question marks).

To add a parameter, click the dropdown arrow () next to Parameters and choose Add Parameter. Click on the newly created parameter to edit its properties to the right (be careful to make sure the Parameter data type matches the column it is evaluated against). Repeat these steps for each parameter expected by the query. Similar to Output fields, there must be one Parameter defined for each placeholder (question mark) in the query, and they are matched by position.

When the Process runs, the values defined in the Connection Step for the Parameters will be passed into the query and will replace the question marks before executing the SQL statement against the database.

To remove a parameter, simply click the dropdown arrow and choose Delete Field.

Technical Note: For users familiar with database programming, Parameters are "Prepared Statements".

Field Properties

To edit a field's properties, simply click on its name. The Table Browser automatically configures these settings by inspecting the database column metadata.

Name	Description
Data Name	User defined name for the field. Technically this does not have to match the database column name or alias (because columns are matched to Outputs by position) but should as a best practice.
Mandatory	Check if the given field should contain a value. For use with the Cleanse Step; leave unchecked otherwise. Note: This is not related to the database table schema definition.
Enforce Unique	Not applicable for Read Profiles.
Data Type Options	Choose the element data type and specify a format for the value if required. (More...) There are no default formats configurable for each data type. Upon execution, values will be formatted according to the associated Database Connection component and corresponding JDBC driver. Character: Default type for character-based values. All values will be surrounded with single quotes in the SQL statement. Number: Type for numeric values. You should choose this type over character if it is a database field-level requirement. Date/Time: Type for date/time values. You should choose this type over character if it is a database field-level requirement. Blob: Type for binary large object fields. Clob: Type for character large object fields.

Write Profiles

For Write Profiles, the Data Elements Tab is where you define the SQL statement(s) to insert, update, or delete records.

Use the Data Elements tab to select the data for the Database Write for Mapping Data. Select Fields (Input parameters) based on requirements. Database Write profiles use Fields (Input Parameters) as the destination profile in a map.

Each question mark defined in the SQL statement serves as a placeholder and must bind positionally to the field listed in the profile.

Database Statement View

Database Fields View

Section	Field / Group/ Button	Type/Value	Description
Statement	Type	Drop down	Standard Insert/Update/Delete
			Dynamic Insert
			Stored Procedure Write
			Dynamic Update
			Dynamic Delete
	Sql Script Text Area	Text	Enter any valid SQL statement.
Fields	Add	Click Blue Arrow	Click to Add Field to the Statement

Dynamic Statements

Dynamic Inserts

A Dynamic Insert Type allows you to simply define the table and fields that you would like to insert into; therefore, the field names must match directly to the database table field names, but they can be listed in any order. Boomi will dynamically generate the insert statement based on these settings and the data that is mapped into each of the fields. If there is no source data to send into a particular field, the field definition and value will not be included in the actual insert statement. You should consider setting a Default Value for the element in the map in the event that a required field is blank.

Dynamic Updates

A Dynamic Update Type works similarly to the Dynamic Insert. You must specify the table name and fields that should be included in the update statement. If a particular field is un-mapped or blank, it will not be added to the final update SQL. The Update type is different because it uses Conditions. These are the Where clause parameters you will need to set in order to make the update unique.

For example, if you need to dynamically update a Contact record, you would want to set a primary field such as Email or Last Name as the condition, so the data set targets that Contact record specifically. Mapping the Email from the source data will ensure this unique update statement will be generated.

Note: If you use the Table Browser to generate the Dynamic Update, Boomi will add every field as a unique condition. You must manually delete all condition instances that should not be a part of the Where clause or Boomi will expect data to populate each condition.

Dynamic Deletes

A Dynamic Delete Type allows you to delete entire record instances based on 1 to many Conditions. These are the Where clause parameters you will need to set in order to make the delete unique.

For example, if you need to dynamically delete a Company record, you would want to set a primary field such as CompanyID or CompanyName as the condition, so the data set targets that Company record specifically. Mapping the ID from the source data will ensure this unique delete statement will be generated.

Note: If you use the Table Browser to generate the Dynamic Update, Boomi will add every field as a unique condition. You must manually delete all condition instances that should not be a part of the Where clause or Boomi will expect data to populate each condition.

Dynamic Condition Configuration

Name	Description
Field Name	Specific name of 'Where' condition field in database table
Operator	Dropdown that identifies the nature of the condition. 'Greater Than' Example: DELETE ORDERS WHERE QUANTITY > 100 Condition Field Name: QUANTITY Condition Operator: Greater Than Data Type: Number Mapped Value: 100
Data Type	Character, Number or Date/Time format requirements

Multiple Statements

You may specify multiple statements in the same database profile. This is especially useful when inserting into tables that have parent-child relationships. For example, one statement could insert into the OrderHeader table, and another statement could insert into the OrderDetail tables.

Refer to the Database Operation component or Database Best Practices to learn more about the Commit Options for this case.

Table Browser

The Table Browser helps you quickly configure the Database Profile by auto-generating SQL statements and creating the necessary Output and Parameter fields. The Table Browser can be used for Select, Dynamic Insert, Dynamic Update, and Dynamic Delete statements. It connects to a locally-deployed Atom to browse the database schema metadata.

To launch the Table Browser, select a Statement node and click on the Import button.

Step 1 - Atom and Connection

Choose the Connection and Atom to connect to the database and click Next.

Note: An Atom must already be deployed to a machine with access to the database server.

Step 2 - Choose Table(s)

Choose a database table to use for this statement and click Next or Finish. If configuring a Select Statement, you can choose multiple tables by holding down the Shift or Ctrl key on your keyboard while selecting tables or click Select All....

Step 3 - Choose Columns

Choose one or more columns and click Finish. You can choose multiple columns by holding down the Shift or Ctrl key on your keyboard while selecting columns or use the Select All and Deselect All buttons.

The Profile is now configured with the SQL statement (Read only), Output fields with data types, and Parameter fields (Dynamic Update only).

Select Statements After Boomi generates the SQL statement, you can and in most scenarios should edit the SQL statement. The auto-generated statement is a simple "select all" query and does not include any table JOINs or WHERE clauses. Most integration scenarios will require you modify the query to include this additional logic. Don't forget to add Parameter fields if using any dynamic values!