<?xml version="1.0" encoding="ISO-8859-1" ?>
<!--
  ~  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.
  -->

<document>
    <properties>
        <title>Apache Synapse - Sample 364</title>
    </properties>
    <body>
        <section name="Sample 364: Executing Database Stored Procedures">
            <div class="xmlConf"><![CDATA[<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>]]></div>
            <subsection name="Objective">
                <p>
                    Demonstrate how to invoke a database stored procedure from Synapse
                </p>
            </subsection>
            <subsection name="Pre-requisites">
                <p>
                    <ul>
                        <li>
                            Setup a MySQL database as described in the <a href="setup/db.html#mysql">database setup guide</a>
                        </li>
                        <li>
                            Deploy the SimpleStockQuoteService in the sample Axis2 server and start Axis2
                        </li>
                        <li>
                            Open the repository/conf/sample/synapse_sample_364.xml file and change the
                            database username, password credentials accordingly
                        </li>
                        <li>
                            Start Synapse using the configuration numbered 364 (repository/conf/sample/synapse_sample_364.xml)
                            <div class="command">
                                Unix/Linux: sh synapse.sh -sample 364<br/>
                                Windows: synapse.bat -sample 364
                            </div>
                        </li>
                    </ul>
                </p>
            </subsection>
            <subsection name="Executing the Client">
                <p>
                    This scenario is very similar to <a href="sample363.html">sample 363</a>, 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).
                </p>
                <p>
                    To try this sample out, invoke the sample client as follows.
                </p>
                <div class="command">ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM</div>
                <p>
                    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.
                </p>
                <div class="consoleOutput">INFO LogMediator text = ** Looking up from the Database ** ...
INFO LogMediator text = Company ID - c1 ...
INFO LogMediator text = Stock price - 183.3635460215262</div>
            </subsection>
        </section>
        <p><a href="../samples.html">Back to Catalog</a></p>        
    </body>
</document>