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" expression="//m0:return/m0:last/child::text()" type="DOUBLE"/> <parameter xmlns:m0="http://services.samples" expression="//m0:return/m0: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" expression="//m0:return/m0: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

Pre-requisites

  • Setup a MySQL database as described in the database setup guide
  • Deploy the SimpleStockQuoteService in the sample Axis2 server and start Axis2
  • Open the repository/conf/sample/synapse_sample_364.xml file and change the database username, password credentials accordingly
  • Start Synapse using the configuration numbered 364 (repository/conf/sample/synapse_sample_364.xml)
    Unix/Linux: sh synapse.sh -sample 364
    Windows: synapse.bat -sample 364

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