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 which case an Apache DBCP connection pool is established and used.
In-lined Data Source
This new Datasource is based on Apache DBCP connection pools. This connection pool support the following configuration properties:
External Data Source
SQL Statements
More than one statement may be specified, and the SQL statement may specify parameters which could be specified as values or XPath expressions. The types of parameters could be any valid SQL types. Only the first row of a result set will be considered and any others are ignored. The single <result> element contains the 'name' and the 'column' attributes. The 'name' attribute defines the name under which the result is stored in the Synapse message context, and the column attribute specifies a column number or name .
<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>
Figure 1: DBLookup mediator options
DBLookup mediator options field descriptions,
If existing data source is choosen, clicking "Load Data Sources" will give the available datasource. Then a data source can be selected.
Figure 2: Carbon Datasources
Like in Pool connection data source configuration properties should be specified.
Figure 3: External Datasources
Figure 4: Adding a property
Property field descriptions,
Figure 5: Adding SQL statements
<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 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 a 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.