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.

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.

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.

• Action - Deletes the parameter.
• Results- Specify how to deal with the rerun result from a Database query execution.
  • Result Name
  • Column
  • Action - Deletes the result.

Example

<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>select * from company where name =?</sql>
        <parameter expression="//m0:getQuote/m0:request/m0:symbol"
                   xmlns:m0="http://services.samples/xsd" type="VARCHAR"/>
        <result name="company_id" column="id"/>
    </statement>
</dblookup>

In this example, when a message arrives at the DBLookup Mediator, 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.