## Apache Synapse

## Sample 360: Introduction to DBLookup Mediator

<definitions xmlns="http://ws.apache.org/ns/synapse"> <sequence name="myFaultHandler"> <makefault response="true"> <code xmlns:tns="http://www.w3.org/2003/05/soap-envelope" value="tns:Receiver"/> <reason expression="get-property('ERROR_MESSAGE')"/> </makefault> <send/> <drop/> </sequence> <sequence name="main" onError="myFaultHandler"> <in> <log level="custom"> <property name="text" value="** Looking up from the Database **"/> </log> <dblookup> <connection> <pool> <driver>org.apache.derby.jdbc.ClientDriver</driver> <url>jdbc:derby://localhost:1527/synapsedb;create=false</url> <user>synapse</user> <password>synapse</password> </pool> </connection> <statement> <sql>select * from company where name =?</sql> <parameter xmlns:m0="http://services.samples" expression="//m0:getQuote/m0:request/m0:symbol" type="VARCHAR"/> <result name="company_id" column="id"/> </statement> </dblookup> <switch source="get-property('company_id')"> <case regex="c1"> <log level="custom"> <property name="text" expression="fn:concat('Company ID - ',get-property('company_id'))"/> </log> <send> <endpoint> <address uri="http://localhost:9000/services/SimpleStockQuoteService"/> </endpoint> </send> </case> <case regex="c2"> <log level="custom"> <property name="text" expression="fn:concat('Company ID - ',get-property('company_id'))"/> </log> <send> <endpoint> <address uri="http://localhost:9000/services/SimpleStockQuoteService"/> </endpoint> </send> </case> <case regex="c3"> <log level="custom"> <property name="text" expression="fn:concat('Company ID - ',get-property('company_id'))"/> </log> <send> <endpoint> <address uri="http://localhost:9000/services/SimpleStockQuoteService"/> </endpoint> </send> </case> <default> <log level="custom"> <property name="text" value="** Unrecognized Company ID **"/> </log> <makefault response="true"> <code xmlns:tns="http://www.w3.org/2003/05/soap-envelope" value="tns:Receiver"/> <reason value="** Unrecognized Company ID **"/> </makefault> <send/> <drop/> </default> </switch> <drop/> </in> <out> <send/> </out> </sequence> </definitions>

### Objective

Demonstrating how to perform database lookups during mediation using the dblookup mediator

### Pre-requisites

- Setup a Derby database as described in the database setup guide
- Deploy the SimpleStockQuoteService in the sample Axis2 server and start Axis2
- Start Synapse using the configuration numbered 360 (repository/conf/sample/synapse_sample_360.xml)
  Unix/Linux: sh synapse.sh -sample 360
  Windows: synapse.bat -sample 360

### Executing the Client

This sample demonstrates simple database read operations through Synapse. When a message arrives at dblookup mediator, it opens a connection to the database and executes the given 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 a dblookup mediator has been used to extract 'id' of the company from the company database using the symbol which is extracted from the SOAP envelope by evaluating an XPath. Then 'id' bases switching will be done by a switch mediator.

To try this out, first request a stock quote for the symbol 'IBM' as follows.

ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM

Synapse console will display the following message.

INFO LogMediator text = ** Looking up from the Database ** INFO LogMediator text = Company ID – c1

Now request a quote for the symbol 'SUN'.

ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=SUN

Synapse will display the following output.

INFO LogMediator text = ** Looking up from the Database ** INFO LogMediator text = Company ID – c2

Finally send a stock quote request for the symbol 'MSFT'.

ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=MSFT

In this case Synapse will display the following output.

INFO LogMediator text = ** Looking up from the Database ** INFO LogMediator text = Company ID – c2

If you send any requests with different symbols, dblookup mediator will return an empty result set, since those symbols are not stored in the Derby database. So as a result Synapse will not be able to determine the company ID, which will result in the following log entry (from the default case in the switch mediator).

INFO LogMediator text = ** Unrecognized Company ID **

Back to Catalog