Monday, June 30, 2008

Database interactions in mediation using DBLookup and DBReport mediators with MySQL

MySQL is known to be the world's most popular open source database due to its fast performance. It is a known fact that a many individuals as well as a lot of world's largest organizations such as Yahoo, Google, Nokia and YouTube use MySQL as their database to save time and money. WS02 ESB has a wide variety of mediators that enables us to connect, manage and transform service interactions between Web services. The DBLookup and DBReport mediators allows you to read data from a database and also to write to a database. Generally we use Apache Derby as the database in our samples but we can use MySQL as well. Let us see how we can use these mediators to look up and report to a MySQL database.

Step 1 - Setting up the MySQL database
You will need to install MySQL server on your machine. Once you have installed MySQL, log into the database using the command

mysql -u <username> -p
E.g.:- mysql -u root -p

Create a database using the following command.
create database esb;

Select the newly created database as follows
CREATE table company(name varchar(10), id varchar(10), price double);

Now create a table using the following command on the newly created database
INSERT into company values ('IBM','c1',0.0);
INSERT into company values ('SUN','c2',0.0);
INSERT into company values ('MSFT','c3',0.0);

Step 2 - Installing WSO2 ESB

a) Downloading the binary distribution

You will need to install WSO2 ESB on your machine. You can download the latest ESB binary distribution from here.

b) Deploying the service

Extract the distribution and navigate to the ESB_HOME/samples/axis2Server folder and start the Axis2 Server. To deploy the SimpleStockQuoteService go to ESB_HOME/samples/axis2Server/src/SimpleStockQuoteService and type 'ant' and it will build the particular service. You can verify whether the service has been deployed successfully by accessing the URL http://localhost:9000/soap/

c) Starting the Simple Axis2 Server

To start the simple axis server navigate to ESB_HOME/samples/axis2Server and run ./axis2Server.sh. Then it would start the axis2 server on port 9000

d) Starting the WSO2 ESB server

To start the WSO2 ESB server go to ESB_HOME/bin and run ./wso2-esb.sh. Once the WSO2 ESB server is started you will be able to access the WSO2 ESB admin console using https://localhost:9444/esb

Step 3 - Create configuration

The following configuration sends a request to a service and receives a response and then the database is updated from those retrieved response values. Create the following configuration using the WS02 ESB admin console.
<syn:definitions xmlns:syn="http://ws.apache.org/ns/synapse">
<syn:sequence name="sample_sequence2">
<syn:in>
<syn:send>
<syn:endpoint>
<syn:address uri="http://localhost:9000/soap/SimpleStockQuoteService"/>
</syn:endpoint>
</syn:send>
</syn:in>
<syn:out>
<syn:log level="custom">
<syn:property name="text" value="** Reporting to the Database **"/>
</syn:log>
<syn:dbreport>
<syn:connection>
<syn:pool>
<syn:driver>com.mysql.jdbc.Driver</syn:driver>
<syn:property name="autocommit" value="false"/>
<syn:password>admin</syn:password>
<syn:user>root</syn:user>
<syn:url>jdbc:mysql://127.0.0.1:3306/esb</syn:url>
</syn:pool>
</syn:connection>
<syn:statement>
<syn:sql><update company set price=? where name =?></syn:sql>
<syn:parameter xmlns:m0="http://services.samples/xsd" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" expression="//m0:return/m0:last/child::text()" type="DOUBLE"/>
<syn:parameter xmlns:m0="http://services.samples/xsd" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" expression="//m0:return/m0:symbol/child::text()" type="VARCHAR"/>
</syn:statement>
</syn:dbreport>
<syn:log level="custom">
<syn:property name="text" value="** Looking up from the Database **"/>
</syn:log>
<syn:dblookup>
<syn:connection>
<syn:pool>
<syn:driver>com.mysql.jdbc.Driver</syn:driver>
<syn:password>admin</syn:password>
<syn:user>root</syn:user>
<syn:url>jdbc:mysql://127.0.0.1:3306/esb</syn:url>
</syn:pool>
</syn:connection>
<syn:statement>
<syn:sql><select * from company where name =?></syn:sql>
<syn:parameter xmlns:m0="http://services.samples/xsd" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" expression="//m0:return/m0:symbol/child::text()" type="VARCHAR"/>
<syn:result name="stock_price" column="price"/>
</syn:statement>
</syn:dblookup>
<syn:log level="custom">
<syn:property xmlns:ns1="http://org.apache.synapse/xsd" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" name="text" expression="fn:concat('Stock price - ',get-property('stock_price'))"/>
</syn:log>
<syn:send/>
</syn:out>
</syn:sequence>
</syn:definitions>
NOTE: When using MySQL as the database make sure that you set the following property in the configuration
<syn:property name="autocommit" value="false"/>

Step 4 - Invoking the client

Once the configuration is created you should be able to invoke the stockquote client using the following command.
ant stockquote -Daddurl=http://localhost:9000/soap/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM

If the client executes successfully you should see the following on the WSO2 ESB console

DEBUG LogMediator Start : Log mediator
INFO LogMediator text = ** Reporting to the Database **
DEBUG LogMediator End : Log mediator
....
DEBUG DBReportMediator Successfully prepared statement : update company set price=? where name =? against DataSource : jdbc:mysql://127.0.0.1:3306/esb
DEBUG DBReportMediator Inserted 1 row/s using statement : update company set price=? where name =?
....
DEBUG LogMediator Start : Log mediator
INFO LogMediator text = ** Looking up from the Database **
DEBUG LogMediator End : Log mediator
....
DEBUG DBLookupMediator Successfully prepared statement : select * from company where name =? against DataSource : jdbc:mysql://127.0.0.1:3306/esb
DEBUG DBLookupMediator End : DBLookup mediator
DEBUG LogMediator Start : Log mediator
INFO LogMediator text = Stock price - 147.286209770313
DEBUG LogMediator End : Log mediator

Finally you can check if the company table has been updated with the new stock value by a simple query.

11 comments:

Raghava said...

Could you please explain how can I use datasource instead of pool?

Thanks in advance.
Raghava
raghava_gp@yahoo.co.in

Evan said...

Hi raghava,

Please refer this sample. It clearly explains how you can use datasources.

https://wso2.org/project/esb/java/1.7.1/docs/ESB_Samples.html#Sample363

Hope this helps.
Thanks,
Evanthika

Raghava said...

Dear Evanthika,

Thank you for the such a quick response. Now I have one more question for you. Could you give me some information on how can i call JMS queue created on JBoss via send mediator of wso2 esb.

I am using following endpoint URL

jms:/queue/WFQueue?transport.jms.ConnectionFactoryJNDIName=QueueConnectionFactory&java.naming.provider.url=jnp://localhost:2001&transport.jms.DestinationType=queue

but i am getting bellow error:
org.apache.axis2.AxisFault: Unable to access the endpoint factory instance :: Invalid endpoint confi
guration.

More over I have copied jboss-j2ee.jar and jbossall-client.jar to the wso2 lib folder.

Regards,
Raghava
-raghava_gp@yahoo.co.in

Evan said...

Yes raghava,

I would love to answer all your questions but our development team will be much glad to answer them. You will get very descriptive answers from them.

Therefore I would like you to publish a post to our WSO2 ESB forum and I also invite you to subscribe to our mailing list

I assure you that you will get prompt responses from our development team.

Thanks,
Evanthika

Evan said...

Meanwhile will it be possible for you to send your WSO2 ESB configuration. I assume you have not specified the 'Send' mediator properly. Will it be possible for you to show me how you have defined your endpoint in the 'Send' mediator?

Ravi M said...

Hi Evanthika,
Nice post. I have followed the same steps as mentioned above. When I try making a call from the client program, I notice that the my sql driver(com.mysql.jdbc.Driver) is not being loaded by ESB. I have placed mysql connector jar in ESB_HOME\lib folder where ESB-HOME is the home directory where I installed WSO2 ESB.

Can you please advise what could be going wrong.

Thanks
Ravi.

Evan said...

Hi Ravi,

You need to place the mysql driver inside ESB_HOME\lib\extensions folder. Once the jar is placed in the above folder you will have to restart the server. Can you please try doing that and see whether it resolves your issue?

Thanks,
Evanthika

DG said...

Hi Evanthika,
Thanks for the nice post.
I was wondering whether you could help me on sending transaction log into a database. I am quite new to ESB. But my requirement is to have all transactions recorded in a database so that I can run end of day report.

Thanks in advance
Dhammika

Evan said...

Hi Dhammika,

Please post your questions to dev@wso2.com and you will get more comprehensive answers for all your queries.

Regards,
Evanthika

sandeep kotha said...

Hi Evanthika

Rather than using




How can I use DBLookup Mediator with datasource ?

regards
Sandeep

Evan said...

Hi Sandeep,

The document at [1] clearly describes how you can use data sources with the DBLookup mediator.

[1] - https://docs.wso2.com/display/ESB481/DBLookup+Mediator

Regards,
Evanthika