Setting up mysql database server
1. Install mysql server.
heshan@heshan-laptop:~$ apt-get install mysql
2. Connect to mysql server.
heshan@heshan-laptop:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 300
Server version: 5.0.75-0ubuntu10.3 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
3. Create a sample databasae
mysql>CREATE DATABASE comp;
4. Create a table using the following statement.
mysql>CREATE TABLE company(name varchar(10), id varchar(10), price double);
5. Inserts some data using following statements
mysql>INSERT INTO company VALUES ('IBM','c1',3.7563);
mysql>INSERT INTO company VALUES ('SUN','c2',3.2554);
mysql>INSERT INTO company VALUES ('MSFT','c3',0.0);6. Create a Stored Procedure.
mysql>CREATE PROCEDURE getCompany(compName VARCHAR(10)) SELECT name, id, price FROM company WHERE name = compName;
7.Add mysql-connector-java-5.1.12-bin.jar to the classpath. This can be done by putting the jar into the Synapse lib directory.
Running the Synapse sample
1. Save the following synapse-configuration as synapse_sample_364.xml to the samples folder.
SYNAPSE_HOME/repository/conf/sample/synapse_sample_364.xml
2. Synapse configuration.
<!-- SYNAPSE_HOME/repository/conf/sample/synapse_sample_364.xml -->
<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/comp</url>
<user>root</user>
<password>admin</password>
</pool>
</connection>
<statement>
<sql>call getCompany(?)</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>
3. Start the Synapse configuration numbered 364.
i.e. synapse -sample 364
4. Start the Axis2 server and deploy the SimpleStockQuoteService if not already done.
5. Run the client.
ant stockquote -Daddurl=http://localhost:9000/soap/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM
6. Voila, you have invoked a database stored procedure.
