Page Comparison

The The DBLookup Mediator can can execute an arbitrary SQL select statement and then set a resulting values as a local message property on in 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.

Excerpt

hidden	true

NOTE TO WRITERS: The following tip is based on the FAQ http://docs.wso2.org/wiki/display/FAQ/Mediators#Mediators-DBLookup. If you make changes to the tip, be sure to make changes in the original FAQ as well.

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

Info
The DBLookup mediator is a content-aware mediator.

...

Table of Contents

maxLevel	3
minLevel	3
style	border:1
location	top
type	flat
separator	pipe

...

Syntax

Code Block

	XML
	XML

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

...

Image Removed

Field descriptions of the DBLookup Mediator in the screenshot above:

...

UI configuration of the DBLookup mediator changes depending on whether you establish the connection to the database using a connection pool, or using a data source. Click on the relevant tab to view the required UI configuration.

Localtabgroup

Localtab

active	true
title	Connection Pool

Image Added

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

Parameter Name	Description
Connection Information	This parameter is used to specify whether the connection should be taken from a connection pool or

...

a

...

datasource.

Driver

...

The class name of the 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.

...

Datasource Type - External

Image Removed

Field descriptions of the DBLookup Mediator in the screenshot above:

...

Adding properties to the DBLookup mediator

If you click Add Property, the page will expand to display the following parameters.

Image Added

The parameters available to manage properties are as follows.

Parameter Name	Description
Name	The name of the property.
Value	The value of the property.
Action	This 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 0 or a negative value if you want the pool to wait indefinitely.
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	The minimum number of idle connections to be allowed in the connection pool at a given time. Specify 0 or a negative value if you want the pool to wait indefinitely.
poolstatements	true/ false	If the value is `true`, statement pooling is enabled for the pool.
testonborrow	true/ false	If the value is `true`, objects are validated before they are borrowed from the pool. An object which fails the validation test will be dropped from the pool and another object in the pool will be picked instead.
testwhileidle	true/ false	If the value is `true`, the objects in the pool will be validated using an idle object evictor (if any exists). Any object which fails this validation test would be dropped from the pool.
validationquery	String	The SQL query that will be used to validate connections from this pool before returning them to the caller.

Localtab

title	Data source

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.

Localtabgroup

Localtab

title	External Datasource

Image Added

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

Parameter Name	Description
Connection Information	This parameter is used to specify whether the connection should be taken from a connection pool or

...

a

...

datasource.

...

Datasource Type	This parameter is used to specify whether the connection to the databased should be made using an external datasource or a Carbon datasource.
Initial Context	The initial context factory class. The

...

corresponding Java

...

environment property

...

is java.naming.factory.initial.

...

Datasource 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

...

	The user name used to connect to the database.
Password

...

Datasource Type - Carbon Data Source

Image Removed

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

...

Image Removed

Property field descriptions:

...

The password used to connect to the database.

...

Adding properties to the DBLookup mediator

If you click Add Property, the page will expand to display the following parameters.

Image Added

The parameters available to manage properties are as follows.

Parameter Name	Description
Name	The name of the property.
Value

...

	The value of the property.
Action

...

This 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 0 or a negative value if you want the pool to wait indefinitely.
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	The minimum number of idle connections to be allowed in the connection pool at a given time. Specify 0 or a negative value if you want the pool to wait indefinitely.
poolstatements	true/ false	If the value is `true`, statement pooling is enabled for the pool.
testonborrow	true/ false	If the value is `true`, objects are validated before they are borrowed from the pool. An object which fails the validation test will be dropped from the pool and another object in the pool will be picked instead.
testwhileidle	true/ false	If the value is `true`, the objects in the pool will be validated using an idle object evictor (if any exists). Any object which fails this validation test would be dropped from the pool.
validationquery	String	The SQL query that will be used to validate connections from this pool before returning them to the caller.

Localtab

title	Carbon Datasource

Image Added

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

Parameter Name	Description
Connection Information	This parameter is used to specify whether the connection should be taken from a connection pool or a datasource.
Datasource Type	This parameter is used to specify whether the connection to the databased should be made using an external datasource or a Carbon datasource.
JNDI Name	The JNDI used to look up data.

Adding SQL Statements statements to the DBLookup Mediator

Anchor

	SQL Statements
	SQL Statements

Image Removed

...

If you click Add Statement, the page will be expanded to display the following parameters.

Image Added

Parameter Name	Description
SQL	This parameter is used to enter one or more SQL statements.
Parameters	This section is used to specify how the values of

...

parameters in the SQL will be

...

determined. A parameter value can be

...

static

...

or calculated at runtime based on

...

a given expression.
Parameter Type	The data type of the parameter. Possible values are as follows. CHAR VARCHAR NUMERIC DECIMAL BIT TINYINT SAMLLINT INTEGER BIGINT REAL DOUBLE DATE TIME TIMESTAMP
Property Type	This determines whether the parameter value should be a static value or calculated at run time via an expression. Value: If this is selected, a static value would be considered as the property value and this value should be entered in the Value/Expression parameter. Expression: If this is selected, the property value will be determined during mediation by evaluating an expression. This expression should be entered in the Value/Expression parameter.
Value/Expression

...

This parameter is used to enter the static value or the XPath expression used to determine the property value based on the option you selected for the Property Type parameter.

Info

title	Tip

You can click

...

NameSpaces to

...

add namespaces if you

...

are providing an expression.

...

Then the Namespace Editor panel would appear where you can provide any number of namespace prefixes and URLs

...

used in the XPath expression.

Action

...

	This allows you to delete a parameter.
Results

...

This section is used to specify how to deal with the rerun result from a Database query execution.

Result Name
Column
Action - Deletes the result.

Info

title	Note

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

Image RemovedImage Added

...

Examples

Code Block

	XML
	XML

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

In this example, when a message arrives at the DBLookup Mediatoris received by a proxy service with a DBLookup mediator configuration, it opens a connection to the database and executes the SQL query. The SQL query uses "uses ?" character 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 the id of of the company from the company database using the symbol which is evaluated using an XPath against the SOAP envelope.

...

Versions Compared

Old Version 2

New Version 3

Key

Syntax

UI Configuration

Adding properties to the DBLookup mediator

Adding properties to the DBLookup mediator

Examples