You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@synapse.apache.org by hi...@apache.org on 2010/05/04 06:55:14 UTC
svn commit: r940726 - in /synapse/trunk/java:
repository/conf/sample/synapse_sample_364.xml
src/site/xdoc/Synapse_Samples.xml src/site/xdoc/Synapse_Samples_Setup.xml
Author: hiranya
Date: Tue May 4 04:55:14 2010
New Revision: 940726
URL: http://svn.apache.org/viewvc?rev=940726&view=rev
Log:
Adding a new sample (sample 364) to demonstrate the ability to invoke stored procedures. (SYNAPSE-571)
Patch provided by Heshan.
Added:
synapse/trunk/java/repository/conf/sample/synapse_sample_364.xml
Modified:
synapse/trunk/java/src/site/xdoc/Synapse_Samples.xml
synapse/trunk/java/src/site/xdoc/Synapse_Samples_Setup.xml
Added: synapse/trunk/java/repository/conf/sample/synapse_sample_364.xml
URL: http://svn.apache.org/viewvc/synapse/trunk/java/repository/conf/sample/synapse_sample_364.xml?rev=940726&view=auto
==============================================================================
--- synapse/trunk/java/repository/conf/sample/synapse_sample_364.xml (added)
+++ synapse/trunk/java/repository/conf/sample/synapse_sample_364.xml Tue May 4 04:55:14 2010
@@ -0,0 +1,111 @@
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ -->
+
+<!-- Executing database stored procedures -->
+<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/synapsedb</url>
+ <user>root</user>
+ <password>admin</password>
+ </pool>
+ </connection>
+ <statement>
+ <sql>call getCompanyDetail(?)</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>
Modified: synapse/trunk/java/src/site/xdoc/Synapse_Samples.xml
URL: http://svn.apache.org/viewvc/synapse/trunk/java/src/site/xdoc/Synapse_Samples.xml?rev=940726&r1=940725&r2=940726&view=diff
==============================================================================
--- synapse/trunk/java/src/site/xdoc/Synapse_Samples.xml (original)
+++ synapse/trunk/java/src/site/xdoc/Synapse_Samples.xml Tue May 4 04:55:14 2010
@@ -255,7 +255,9 @@
<li>
<a href="#Sample362">Sample 362: Action of dbreport and dblookup mediators together</a></li>
<li>
-<a href="#Sample363">Sample 363: Reusable database connection pools</a></li></ul></li>
+<a href="#Sample363">Sample 363: Reusable database connection pools</a></li>
+<li>
+<a href="#Sample364">Sample 364: Executing database Stored Procedures</a></li></ul></li>
<li>
<a href="#Throttle">Throtteling messages (Throttle Mediator)</a>
<ul>
@@ -3437,7 +3439,128 @@ INFO LogMediator text = Stock price - 15
<div>
<p>Prior to run this sample please follow the 'Setting up Synapse DataSources' section in the
<a href="Synapse_Samples_Setup.html">sample setup guide</a>.</p>
-<pre><!-- Reusable database connection pool --> <definitions xmlns="http://synapse.apache.org/ns/2010/04/configuraiton"> <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> <dsName>lookupdb</dsName> <icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass> <url>rmi://localhost:2199</url> <user>synapse</user> <password>synapse</password>
</pool> </connection> <statement> <sql>select * from company where name =?</sql> <parameter expression="//m0:getQuote/m0:request/m0:symbol" xmlns:m0="http://services.samples/xsd" 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&
gt; <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> <dro
p/> </in> <out> <log level="custom"> <property name="text" value="** Reporting to the Database **"/> </log> <dbreport> <connection> <pool> <dsName>reportdb</dsName> <icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass> <url>rmi://localhost:2199</url> <user>synapse</user> <password>synapse</password> </pool> </connection> <statement> <sql>update company set price=? where name =?</sql> <parameter expression="//m0:return/m0:last/child::text()" xmlns:m0="http://services.samples/xsd" type="DOUBLE"/> <parameter expression="//m0:return/m0:symbol/child::text()" xmlns:m0="http://services.samples/xsd" type="VARCHAR"/> </statement> </dbreport> <log level="custom"> <property name="text" value=&
quot;** Looking up from the Database **"/> </log> <dblookup> <connection> <pool> <dsName>reportdb</dsName> <icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass> <url>rmi://localhost:2199</url> <user>synapse</user> <password>synapse</password> </pool> </connection> <statement> <sql>select * from company where name =?</sql> <parameter expression="//m0:return/m0:symbol/child::text()" xmlns:m0="http://services.samples/xsd" type="VARCHAR"/> <result name="stock_price" column="price"/> </statement> </dblookup> <log level="custom"> <property name="text" expression="fn:concat('Stock price - ',get-property('stock_price'))"/> </log> <send/> </out> </sequence> </definitions></pre></div>
+<pre><definitions xmlns="http://synapse.apache.org/ns/2010/04/configuraiton">
+ <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>
+ <dsName>lookupdb</dsName>
+ <icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass>
+ <url>rmi://localhost:2199</url>
+ <user>synapse</user>
+ <password>synapse</password>
+ </pool>
+ </connection>
+ <statement>
+ <sql>select * from company where name =?</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>
+ <log level="custom">
+ <property name="text" value="** Reporting to the Database **"/>
+ </log>
+ <dbreport>
+ <connection>
+ <pool>
+ <dsName>reportdb</dsName>
+ <icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass>
+ <url>rmi://localhost:2199</url>
+ <user>synapse</user>
+ <password>synapse</password>
+ </pool>
+ </connection>
+ <statement>
+ <sql>update company set price=? where name =?</sql>
+ <parameter expression="//m0:return/m0:last/child::text()" xmlns:m0="http://services.samples/xsd" type="DOUBLE"/>
+ <parameter expression="//m0:return/m0:symbol/child::text()" xmlns:m0="http://services.samples/xsd" type="VARCHAR"/>
+ </statement>
+ </dbreport>
+ <log level="custom">
+ <property name="text" value="** Looking up from the Database **"/>
+ </log>
+ <dblookup>
+ <connection>
+ <pool>
+ <dsName>reportdb</dsName>
+ <icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass>
+ <url>rmi://localhost:2199</url>
+ <user>synapse</user>
+ <password>synapse</password>
+ </pool>
+ </connection>
+ <statement>
+ <sql>select * from company where name =?</sql>
+ <parameter expression="//m0:return/m0:symbol/child::text()" xmlns:m0="http://services.samples/xsd" type="VARCHAR"/>
+ <result name="stock_price" column="price"/>
+ </statement>
+ </dblookup>
+ <log level="custom">
+ <property name="text" expression="fn:concat('Stock price - ',get-property('stock_price'))"/>
+ </log>
+ <send/>
+ </out>
+ </sequence>
+</definitions></pre></div>
<div>
<p>
<strong>Objective: Demonstrate the use of reusable database connection pools</strong></p>
@@ -3449,7 +3572,125 @@ INFO LogMediator text = Stock price - 15
<p>Runs the client as follows</p>
<pre>ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM</pre>
<p>Then the console output </p>
-<pre>INFO LogMediator text = ** Looking up from the Database ** ... INFO LogMediator text = Company ID - c1 ... INFO LogMediator text = ** Reporting to the Database ** ... INFO LogMediator text = ** Looking up from the Database ** ... INFO LogMediator text = Stock price - 183.3635460215262</pre></div>
+<pre>INFO LogMediator text = ** Looking up from the Database ** ...
+ INFO LogMediator text = Company ID - c1 ...
+ INFO LogMediator text = ** Reporting to the Database ** ...
+ INFO LogMediator text = ** Looking up from the Database ** ...
+ INFO LogMediator text = Stock price - 183.3635460215262</pre></div>
+<h2>
+<a name="Sample364" id="Sample364">Sample 364: Executing database Stored Procedures</a></h2>
+<div>
+<p>This sample if functionally similar to sample 363 but uses a stored procedure to query
+the database instead of direct SQL statements. Prior to running this sample please follow the
+instructions given in the 'Setting up MySQL database server' section in the
+<a href="Synapse_Samples_Setup.html#mysql">sample setup guide</a>.</p>
+
+<pre xml:space="preserve"><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/synapsedb</url>
+ <user>root</user>
+ <password>admin</password>
+ </pool>
+ </connection>
+ <statement>
+ <sql>call getCompanyDetail(?)</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></pre></div>
+<div>
+<p>
+<strong>Objective: Demonstrate the use of dblookup mediator to execute a database Stored Procedure (SP)</strong></p>
+<p>
+<strong>Prerequisites:</strong>Set up MySQL database server according to the
+<a href="Synapse_Samples_Setup.html#mysql">sample setup guide</a>. Edit the synapse_sample_364.xml
+configuration file and update the configuration with the appropriate database connection details.</p>
+<p>Start the Synapse configuration numbered 364: i.e. synapse -sample 364</p>
+<p>Start the Axis2 server and deploy the SimpleStockQuoteService if not already done </p>
+<p>Run the client as follows</p>
+<pre>ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM</pre>
+<p>Then you will get the following console output </p>
+<pre>INFO LogMediator text = ** Looking up from the Database ** ...
+INFO LogMediator text = Company ID - c1 ...
+INFO LogMediator text = Stock price - 183.3635460215262</pre></div>
<h2>
<a name="Throttle" id="Throttle">Throttling messages (Throttle Mediator)</a></h2>
<h2>
Modified: synapse/trunk/java/src/site/xdoc/Synapse_Samples_Setup.xml
URL: http://svn.apache.org/viewvc/synapse/trunk/java/src/site/xdoc/Synapse_Samples_Setup.xml?rev=940726&r1=940725&r2=940726&view=diff
==============================================================================
--- synapse/trunk/java/src/site/xdoc/Synapse_Samples_Setup.xml (original)
+++ synapse/trunk/java/src/site/xdoc/Synapse_Samples_Setup.xml Tue May 4 04:55:14 2010
@@ -784,6 +784,48 @@ synapse.sh -sample <n></pre>
the required database driver jars to the Synapse classpath.
</p>
<p/>
+ <h2 id="mysql">
+ Setting Up MySQL database server
+ </h2>
+ <p>
+ You can use a MySQL installation to try out certain database mediator samples.
+ You can download MySQL distribution from <a
+ href="http://dev.mysql.com/downloads/">http://dev.mysql.com/downloads/</a>
+ </p>
+ <ol>
+ <li>
+ Set up and start the MySQL server
+ </li>
+ <li>
+ Create a sample databasae<br/>
+ <pre>CREATE DATABASE synapsedb;</pre>
+ </li>
+ <li>
+ Create a table in the sample database
+ <pre> USE synapsedb;
+ CREATE table company(name varchar(10), id varchar(10), price double);</pre>
+ </li>
+ <li>
+ Inserts some data using following statements
+ <pre> INSERT into company values ('IBM','c1',3.7563);
+ INSERT into company values ('SUN','c2',3.8349);
+ INSERT into company values ('MSFT','c3',3.2938);</pre>
+ </li>
+ <li>Create a Stored Procedure<br/>
+ <pre>CREATE PROCEDURE getCompanyDetail(compName VARCHAR(10)) SELECT name, id, price FROM company WHERE name = compName;</pre>
+ </li>
+ </ol>
+ <p>
+ When using MySQL, you need to add mysql-connector-java.jar to the Synapse classpath. This can be done
+ by putting the above mentioned jar into the Synapse lib directory. For testing the samples
+ MySQL-5.0.75 distribution and mysql-connector-java-5.1.12-bin.jar was used.
+ </p>
+ <p>
+ You can use any other database product instead of MySQL. Then you have to
+ change the database connection details accordingly. Also you have to copy
+ the required database driver jars to the Synapse classpath.
+ </p>
+ <p/>
<h2>Key Stores Configurations</h2>
<div>