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

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 7 Next »

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.

The DBLookup mediator is a content-aware mediator.



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.

 

The parameters available to configure the DBLookup mediator are as follows:

Parameter NameDescription
Connection Information

This parameter is used to specify whether the connection should be taken from a connection pool or a datasource.

 

DriverThe class name of the database driver.
URLJDBC URL of the database where the data will be looked up.
UserUsername used to connect to the database.
PasswordPassword used to connect to the database.

The UI configuration of the DBLookup mediator further differs based on whether the connection to the database is made using an external datasource or a Carbon datasource. Click on the relevant tab to view the required UI configuration.

The parameters available to configure the DBLookup mediator are as follows.

Parameter NameDescription
 Connection InformationThis parameter is used to specify whether the connection should be taken from a connection pool or a datasource.
 Datasource TypeThis parameter is used to specify whether the connection to the databased should be made using an external datasource or a Carbon datasource.
 Initial ContextThe initial context factory class. The corresponding Java environment property is java.naming.factory.initial.
 Datasource NameThe naming service provider URL . The corresponding Java environment property is java.naming.provider.url.
 URLJDBC URL of the database that data will be looked up from.
UserThe user name used to connect to the database.
PasswordThe password used to connect to the database.


The parameters available to configure the DBLookup mediator are as follows.

Parameter NameDescription
 Connection InformationThis parameter is used to specify whether the connection should be taken from a connection pool or a datasource.
 Datasource TypeThis parameter is used to specify whether the connection to the databased should be made using an external datasource or a Carbon datasource.
JNDI NameThe JNDI used to look up data.


Adding Properties to DBLookup Mediator 

Property field descriptions:

Parameter NameDescription
NameThe name of the property.
ValueThe value of the property.
ActionThis parameter enables a property to be deleted.


The available properties are as follows.

Name

Value

Description

autocommit

true / false

The auto-commit state of the connections created by the pool.

isolation

Connection.TRANSACTION_NONE / Connection.TRANSACTION_READ_COMMITTED / Connection.TRANSACTION_READ_UNCOMMITTED / Connection.TRANSACTION_REPEATABLE_READ / Connection.TRANSACTION_SERIALIZABLE

The isolation state of the connections created by the pool.

initialsize

int

The initial number of connections created when the pool is started.

maxactive

int

The maximum number of active connections that can be allocated from this pool at a given time. Specify a negative value if you do not want to set a limit.

maxidle

int

The maximum number of idle connections to be allowed in the connection pool at a given time. Specify a negative value if you do not want to set a limit.

maxopenstatements

int

The maximum number of open statements that can be allocated from the statement pool at a given time. Specify a negative value if you do not want to set a limit.

maxwait

long

The maximum number of milliseconds that the connection pool will wait for a connection to return before throwing an exception when there are no connections available in the pool. Specify 0 or a negative value if you want the pool to wait indefinitely.

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