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 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

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

DBLookup Mediator options field descriptions:

• 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 that 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

DBLookup Mediator options field descriptions:

• Connection Information - Specifies whether the connection is taken from a connection pool or from a data source.
• Data Sourse 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.
• 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 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 would 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.

    Tip

    You can click this link to add namespaces if you provide an expression. You will be provided another panel named "Namespace Editor," where you can provide any number of namespace prefixes and URL that you have used in the XPath expression.

  • Action - Deletes the parameter.
• Results - Specify how to deal with rerun result from Database query execution.
  • Result Name
  • Column
  • Action - Delete 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 use "?" 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 id of the company from the company database using the symbol which is evaluated using an XPath against the SOAP envelope.