WSO2 ESB - DBLookup Mediator

DBLookup Mediator

The dblookup mediator is capable of executing an arbitrary SQL select statement, and then set some resulting values as local message properties on the message context. The DB connection used maybe looked up from an external DataSource or specified in-line, in which case an Apache DBCP connection pool is established and used.

In-lined Data Source

This new Datasource is based on Apache DBCP connection pools. This connection pool support the following configuration properties:

External Data Source

SQL Statements

More than one statement may be specified, and the SQL statement may specify parameters which could be specified as values or XPath expressions. The types of parameters could be any valid SQL types. Only the first row of a result set will be considered and any others are ignored. The single <result> element contains the 'name' and the 'column' attributes. The 'name' attribute defines the name under which the result is stored in the Synapse message context, and the column attribute specifies a column number or name .

Syntax

<dblookup>
   <connection>
     <pool>
      (
       <driver/>
       <url/>
       <user/>
       <password/>
     |
       <dsName/>
       <icClass/>
       <url/>
       <user/>
       <password/>
     )
       <property name="name" value="value"/>*
     </pool>
   </connection>
   <statement>
     <sql>select something from table where something_else = ?</sql>
     <parameter [value="" | expression=""] type="CHAR|VARCHAR|LONGVARCHAR|NUMERIC|DECIMAL|BIT|TINYINT|SMALLINT|INTEGER|BIGINT|REAL|FLOAT|DOUBLE|DATE|TIME|TIMESTAMP"/>*
     <result name="string" column="int|string"/>*
   </statement>+
</dblookup>

UI Configuration

Figure 1: DBLookup mediator options

DBLookup mediator options field descriptions,

Data sources

Data Source

Existing

If existing data source is choosen, clicking "Load Data Sources" will give the available datasource. Then a data source can be selected.

Inline

Like in Pool connection data source configuration properties should be specified.

Adding properties

Figure 2 shows the screen after clicking Add Property,

Figure 2: Adding a property

Property field descriptions,

Adding SQL statements

  1. Click Add Statement link shown in figure 2 and it'll open up the screen where you could enter SQL statement option. This is shown in figure 3.
  2. More than one statement may be specified.

Figure 3: Adding SQL statements