Mediator to support Database stored procedure

Following blogpost shows how to use Apache Synapse's dblookup mediator to support a mysql database stored procedure.

Setting up mysql database server
1. Install mysql server.

heshan@heshan-laptop:~$ apt-get install mysql

2. Connect to mysql server.

heshan@heshan-laptop:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 300
Server version: 5.0.75-0ubuntu10.3 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

3. Create a sample databasae

mysql>CREATE DATABASE comp;

4. Create a table using the following statement.

mysql>CREATE TABLE company(name varchar(10), id varchar(10), price double);

5. Inserts some data using following statements

mysql>INSERT INTO company VALUES ('IBM','c1',3.7563);
mysql>INSERT INTO company VALUES ('SUN','c2',3.2554);
mysql>INSERT INTO company VALUES ('MSFT','c3',0.0);

6. Create a Stored Procedure.

mysql>CREATE PROCEDURE getCompany(compName VARCHAR(10)) SELECT name, id, price FROM company WHERE name = compName;

7.Add mysql-connector-java-5.1.12-bin.jar to the classpath. This can be done by putting the jar into the Synapse lib directory.

Running the Synapse sample
1. Save the following synapse-configuration as synapse_sample_364.xml to the samples folder.

SYNAPSE_HOME/repository/conf/sample/synapse_sample_364.xml

2. Synapse configuration.

<!-- SYNAPSE_HOME/repository/conf/sample/synapse_sample_364.xml -->
<definitions xmlns="http://synapse.apache.org/ns/2010/04/configuration">

<sequence name="myFaultHandler">
   <makefault response="true">
       <code value="tns:Receiver" xmlns:tns="http://www.w3.org/2003/05/soap-envelope"/>
       <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>com.mysql.jdbc.Driver</driver>
                   <url>jdbc:mysql://localhost/comp</url>
                   <user>root</user>
                   <password>admin</password>
               </pool>
           </connection>
           <statement>
               <sql>call getCompany(?)</sql>
               <parameter expression="//m0:getQuote/m0:request/m0:symbol"
                          xmlns:m0="http://services.samples" 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 value="tns:Receiver"
                         xmlns:tns="http://www.w3.org/2003/05/soap-envelope"/>
                   <reason value="** Unrecognized Company ID **"/>
               </makefault>
               <send/>
               <drop/>
           </default>
       </switch>
       <drop/>
   </in>

   <out>
       <send/>
   </out>

</sequence>

</definitions>

3. Start the Synapse configuration numbered 364.

i.e. synapse -sample 364

4. Start the Axis2 server and deploy the SimpleStockQuoteService if not already done.

5. Run the client.

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

6. Voila, you have invoked a database stored procedure.