Database Egress Connector
Version: 17.07
Supported Since: 17.01
What is Database 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 which needs to be inserted to the
database can be extracted from for resources and they are;
-
Message Payload (XML Payloads only)
-
Transport Header
-
Scope Variable
-
Values hard coded to the mapping file
Database Egress connector can be used with relational SQL database system such as Oracle, MS SQL Server and MySQL.
| In order to use the Database Egress Connector, you must first select the Database Connector dependency from the connector list when you are creating an empty Ultra project. If you have already created a project, you can add the Database Connector dependency via Component Registry. From Tools menu, select Ultra Studio → Component Registry and from the Connectors list, select the Database Connector dependency. |
Out Ports
On Exception |
The message will be emitted from this out port, if an error occurs while performing Database operation. |
Side Ports
Connector Operation |
This port is used to connect operational elements to the Egress Connector. By-default, user does not have to connect any operational element and the default connector operation will be used. |
Parameters
* marked fields are mandatory
Data Source * |
Basic |
Resource bean for the database connection to be used by the connector. |
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}) |
Mapping file |
Basic |
File path to the DSON configuration mapping file. |
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 |
Sample Use Case
Prerequisite
First open the project.xpml file of the project and right click on it (on the XML content of the file). From the context menu, select Resource Template as shown in below figure.
After that from the shown dialog box, select Database template.
Next you need to specify the required parameters as shown below. It is mandatory to specify a bean prefix and for that you can specify any value.
Setting up the connector
Now let’s configure a Database Egress Connector to insert 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 Database egress connector for the above flow will be as follows.
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 Database configuration mapping UI as well.
In this topic
In this topic