DBLookup Mediator

The DBLookup Mediator can execute an arbitrary SQL select statement and then set a resulting values as a local message property on the message context. The DB connection used may be looked up from an external data source or specified inline. In this case, an Apache DBCP connection pool is established and used.

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 WSO2 Data Services Server to create a data service and invoke that service from the ESB using the Callout mediator instead.

1 Syntax | 2 UI Configuration | 3 Example

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

The DBLook Mediator Connection Information can be selected as:

Pool
Data Source:
- External
- Carbon Data Source

It is possible to add Properties and SQL Statements to the DBLookup Mediator.

Connection Information - Pool

Field descriptions of the DBLookup Mediator in the screenshot above:

Connection Information - Specifies whether the connection is taken from a connection pool or from a data source.
Driver - Database driver.
Url - JDBC URL of the database where the data will be looked up.
User - Username used to connect to the database.
Password - Password used to connect to the database.

Connection Information - Data Source

Datasource Type - External

Field descriptions of the DBLookup Mediator in the screenshot above:

Connection Information - Specifies whether the connection is taken from a connection pool or from a data source.
Datasource Type - The name of the Datasource to be looked up.
Initial Context - The initial context factory class. The corresponding Java environment property is java.naming.factory.initial.
Data Source Name - The naming service provider URL . The corresponding Java environment property is java.naming.provider.url.
Url - JDBC URL of the database that data will be looked up from.
User - Username used to connect to the database.
Password - Password used to connect to the database.

Connection Information - Data Source

Datasource Type - Carbon Data Source

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

Adding Properties to DBLookup Mediator

Property field descriptions:

Name - The name of the property.
Value - The value of the property.
Action - Deletes the property.

Name	Value

Name	Value
autocommit	true / false
isolation	Connection.TRANSACTION_NONE / Connection.TRANSACTION_READ_COMMITTED / Connection.TRANSACTION_READ_UNCOMMITTED / Connection.TRANSACTION_REPEATABLE_READ / Connection.TRANSACTION_SERIALIZABLE
initialsize	int
maxactive	int
maxidle	int
maxopenstatements	int
maxwait	long
minidle	int
poolstatements	true/ false
testonborrow	true/ false
testwhileidle	true/ false
validationquery	String

Adding SQL Statements to DBLookup Mediator

SQL- One or more SQL Statements
Parameters- Specify how the values of parameter in the SQL will be calculated. A value can be a static literal text and can be calculated at runtime based on the given expression.
- Parameter Type
  - CHAR
  - VARCHAR
  - NUMERIC
  - DECIMAL
  - BIT
  - TINYINT
  - SAMLLINT
  - INTEGER
  - BIGINT
  - REAL
  - DOUBLE
  - DATE
  - TIME
  - TIMESTAMP
- Property Type
  - Value
  - Expression
- Value/Expression
- Namespace - Gives possibility to choose an Expression from the list.