Friday, April 30, 2010

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.

4 comments:

ParisH said...

great msg for me, thanks a lot dude˙﹏˙

Heshan Suriyaarachchi said...

You're welcome ParisH :). I have submitted a new sample to Synapse as a patch. It will be available in next release of Synapse i.e. Synapse 2.0.

Madhuri Wakchaure said...

Hi,
thanks for above sample. i tried this & it's working too.but i wish to send result of first proxy to second proxy.And this new proxy should save the result of first proxy to another table of same database.suggest something...
Thanks in advance..

Heshan Suriyaarachchi said...

Hi Madhuri,

Sorry for the late reply. It's been a long time since I last wrote an ESB configuration. I can't answer you without refreshing my knowledge and I haven't kept in touch with WSO2 ESB's newer releases. Therefore, the best places to ask this question would be WSO2's user mailing list or the developer mailing list. You can find the contact information on their website.

Thanks,
Heshan.