DBLookup Mediator
The DBLookup Mediator can execute an arbitrary SQL select statement and then set a resulting values as a local message property in the message context. The DB connection used may be looked up from an external data source or specified inline.
The DBLookup mediator can set a property from one row in a result set. It cannot return multiple rows. If you need to get multiple records, or if you have a table with multiple parameters (such as URLs), you can use the WSO2 Data Services Server to create a data service and invoke that service from the ESB using the Callout mediator instead.
The DBLookup mediator is a content-aware mediator.
Syntax
The syntax of the DBLookup mediator changes depending on whether you connect to the database using a connection pool, or using a data source. Click on the relevant tab to view the required syntax.
Configuration
The configuration of the DBLookup mediator changes depending on whether you connect to the database using a connection pool, or using a data source. Click on the relevant tab to view the required UI configuration.
Adding SQL statements to the DBLookup Mediator
If you click Add Statement, the page will be expanded to display the following parameters.
| Parameter Name | Description |
|---|---|
| SQL | This parameter is used to enter one or more SQL statements. |
| Parameters | This section is used to specify how the values of parameters in the SQL will be determined. A parameter value can be static or calculated at runtime based on a given expression. |
| Parameter Type | The data type of the parameter. Possible values are as follows.
|
| Property Type | This determines whether the parameter value should be a static value or calculated at run time via an expression.
|
| Value/Expression | This parameter is used to enter the static value or the XPath expression used to determine the property value based on the option you selected for the Property Type parameter. You can click NameSpaces to add namespaces if you are providing an expression. Then the Namespace Editor panel would appear where you can provide any number of namespace prefixes and URLs used in the XPath expression. |
| Action | This allows you to delete a parameter. |
| Results | This section is used to specify how to deal with the rerun result from a Database query execution.
|
Examples
<dblookup xmlns="http://ws.apache.org/ns/synapse">
<connection>
<pool>
<driver>org.apache.derby.jdbc.ClientDriver</driver>
<url>jdbc:derby://localhost:1527/esbdb;create=false</url>
<user>esb</user>
<password>esb</password>
</pool>
</connection>
<statement>
<sql><![CDATA[select * from company where name =?]]></sql>
<parameter expression="//m0:getQuote/m0:request/m0:symbol" type="VARCHAR" xmlns:m0="http://services.samples/xsd"/>
<result column="id" name="company_id"/>
</statement>
</dblookup>
In this example, when a message is received by a proxy service with a DBLookup mediator configuration, it opens a connection to the database and executes the SQL query. The SQL query uses ? character for attributes that will be filled at runtime. The parameters define how to calculate the value of those attributes at runtime. In this sample, the DBLookup Mediator has been used to extract the id of the company from the company database using the symbol which is evaluated using an XPath against the SOAP envelope.