Sample 364: Executing Database Stored Procedures
<definitions xmlns="http://ws.apache.org/ns/synapse">
<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>
Objective
Demonstrate how to invoke a database stored procedure from Synapse
Executing the Client
This scenario is very similar to sample 363, but makes
use of stored procedures to lookup and update the database instead of simple
SQL queries. Note that we are still using the dblookup and dbreport mediators
to access the database but the statements are simply calling a stored procedure in
MySQL (the syntax to call a stored procedue is database engine specific).
To try this sample out, invoke the sample client as follows.
ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM
Synapse will invoke the two stored procedures as the response is mediated back
to the client. You will see the following output on the Synapse console.
INFO LogMediator text = ** Looking up from the Database ** ...
INFO LogMediator text = Company ID - c1 ...
INFO LogMediator text = Stock price - 183.3635460215262
Back to Catalog
