This documentation is for WSO2 Enterprise Service Bus version 4.8.0 . View documentation for the latest release.

All docs This doc
Skip to end of metadata
Go to start of metadata

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.



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:

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

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.

Tip

You can click the Namespaces 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 URLs that you have used in the XPath expression.

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

 

Note

You can configure the Mediator using XML. Click on "switch to source view" in the "Mediator" window.


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.

  • No labels