DB Egress Connector
Version: 17.01
Supported Since: 17.01
What is DB Egress Connector?
The database egress connector can be used to insert, update and delete data to a SQL database. The SQL query is pre configured and the parameters for the SQL query can be passed through a configuration file. Data to insert to the database can be extracted from 4 resources. They are,
-
Message payload
-
Message header
-
Scope variables
-
Values hard coded to the mapping file
DB egress connector can be used with any relational SQL database system such as Oracle, SQL Server and MySQL databases.
Sample Use Case
Let’s configure a db egress connector to insert a data to a database table with four columns. For better understandability, let’s extract data from all 4 possible resources.
Suppose there is a database table named as table_1 with 4 columns named as column_1, column_2, column_3, column_4. The the sample integration flow will be as follows.
Sample configuration of the DB egress connector for the above flow will be as follows.
Prerequisite
In order to use the DB Ingress Connector you must first select the “DB” dependency from the connector list when you are creating an empty Ultra project. If you have already created a project, you can add the “DB” dependency via Component Registry. From Tools menu, select Ultra Studio → Component Registry and from the connectors list, select the “DB” dependency.
Then you have to configure the resources needed for the database connection in the project.xpml. You can add the data source resource using the "Custom Template" button of the project.xpml designer. Select "Datasource" option and fill the required fields.
Setting up the connector
Let’s create our integration flow named “SampleFlow” and add a DB EgressConnector to that. Then click on the gear icon appeared on the connector icon to open settings. Set the datasource of the database to read the data from. This datasource was added to the project.xpml in the above prerequisite section.
Here the SQL query can be an INSERT, UPDATE or a DELETE query. SELECT queries are not supported by the egress connector.
Query can be a simple SQL query or an advance one with nested queries. Parameters to the query can be passed using the
@{parameterName} format.
Now we have configured a datasource for the database to be used and a SQL query with parameters. Then we have to setup the configuration to extract values to replace the parameters. This is done using a mapping file. In this mapping file we have to declare where to extract parameter values from. Following is a sample mapping file for the above use case.
{
"messageType": "XML",
"parameters": [
{
"name": "parameter_1",
"type": "PATH",
"path": "/item/element_name"
},
{
"name": "parameter_2",
"type": "HEADER",
"headerName": "headerName",
"className": "java.lang.String"
}
},
{
"name": "parameter_3",
"type": "VARIABLE",
"variableName": "variableName",
"className": "java.lang.String",
"propagateToParent": true
},
{
"name": "parameter_4",
"type": "VALUE",
"value": "value_1"
},
{
"name" : "parameter_5"
"type" : "BLOB",
},
{
"name" : "parameter_6"
"type" : "CLOB",
}
]
}
"BLOB" and "CLOB" parameter types can be used to save the full payload to the database as a blob or clob.
This mapping file can be generated using the DB configuration mapping builder.
Out Ports
On Exception |
The message will be sent to this outport, if an error occurred while performing the connector operation. |
Parameters
Data Source |
Basic |
Resource bean for the database connection to be used by the connector. |
Mapping file |
Basic |
File path to the JSON configuration mapping file. |
SQL query |
Basic |
SQL query with parameters. Ex: insert into table_1 (column_1, column_2, column_3, column_4) values (@{parameter_1}, @{parameter_2}, @{parameter_3}, @{parameter_4}) |
Egress Timeout |
Advanced |
Timeout in milliseconds after which the egress message timeouts |
Weight |
Fail-Over |
Weight of the Egress endpoint when used with a Load Balancer |
In this topic
In this topic