This site contains the documentation that is relevant to older WSO2 product versions and offerings.
For the latest WSO2 documentation, visit https://wso2.com/documentation/.
Sample 364: Executing Database Stored Procedures
Objective: Demonstrate the use of dblookup and dbreport mediators to execute a database Stored Procedures
<definitions xmlns="http://ws.apache.org/ns/synapse" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://ws.apache.org/ns/synapse http://synapse.apache.org/ns/2010/04/configuration/synapse_config.xsd"> <sequence name="main"> <in> <send> <endpoint> <address uri="http://localhost:9000/services/SimpleStockQuoteService"/> </endpoint> </send> </in> <out> <log level="custom"> <property name="text" value="** Reporting to the Database **"/> </log> <dbreport> <connection> <pool> <driver>com.mysql.jdbc.Driver</driver> <url>jdbc:mysql://localhost:3306/synapsedb</url> <user>user</user> <password>password</password> </pool> </connection> <statement> <sql>call updateCompany(?,?)</sql> <parameter xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" expression="//m0:return/m1:last/child::text()" type="DOUBLE"/> <parameter xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" expression="//m0:return/m1:symbol/child::text()" type="VARCHAR"/> </statement> </dbreport> <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:3306/synapsedb</url> <user>user</user> <password>password</password> </pool> </connection> <statement> <sql>call getCompany(?)</sql> <parameter xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" expression="//m0:return/m1:symbol/child::text()" type="VARCHAR"/> <result name="stock_prize" column="price"/> </statement> </dblookup> <log level="custom"> <property name="text" expression="fn:concat('Stock Prize - ',get-property('stock_prize'))"/> </log> <send/> </out> </sequence> </definitions>
Prerequisites: Set up MySQL database server according to the sample setup guide.
- Start the Synapse configuration numbered 364: i.e. wso2esb-samples -sn 364
- Start the Axis2 server and deploy the SimpleStockQuoteService if not already done
Runs the client as follows
ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM
Then you will get the following console output
INFO LogMediator text = ** Looking up from the Database ** ... INFO LogMediator text = Company ID - c1 ... INFO LogMediator text = Stock price - 183.3635460215262