Page Comparison

Objective: Demonstrate the use of reusable database connection pools

...

language	html/xml

...

Table of Contents

Introduction

This sample demonstrates how you can setup reusable connection pools for the DBLookup and DB Report mediators.

Prerequisites

Setup a Derby database and the Synapse datasources.
- For instructions on setting up a Derby database, see Setting up Remote Derby.
- For instructions on setting up a Synapse datasources, see Setting up Synapse datasources.
For a list of general prerequisites, see Prerequisites to Start the ESB Samples.

Building the sample

The XML configuration for this sample is as follows:

Code Block

language	html/xml

<!-- Reusable database connection pool -->
<definitions xmlns="http://ws.apache.org/ns/synapse">
    <sequence name="myFaultHandler">
         <makefault><makefault response="true">
            <code value="tns:Receiver" xmlns:tns="http://www.w3.org/2003/05/soap-envelope" value="tns:Receiver"/>
            <reason expression="get-property('ERROR_MESSAGE')"/>
        </makefault>
        <send/>
      <property name="RESPONSE" value="true" <drop/>
    </sequence>
   <header name="To" expression="get-property('ReplyTo')"/>
        <send/>
        <drop/>     </sequence>

    <sequence name="main" onError="myFaultHandler">
        <in>
            <log level="custom">
                <property name="text"                           value=value="** Looking up from the Database **"/>
            </log>
            <dblookup>
                <connection>
                    <pool>
                        <dsName>lookupdb</dsName>
                    </pool>
   <icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass>             </connection>
           <url>rmi://localhost:2199</url>     <statement>
                   <user>esb</user> <sql>select * from company where name =?</sql>
                 <password>esb</password>   <parameter xmlns:m0="http://services.samples" expression="//m0:getQuote/m0:request/m0:symbol"
               </pool>                 </connection>type="VARCHAR"/>
                <statement>         <result name="company_id" column="id"/>
           <sql>select * from company where name =?</sql>statement>
             </dblookup>
      <parameter expression="//m0:getQuote/m0:request/m0:symbol"     <switch source="get-property('company_id')">
                         xmlns:m0="http://services.samples/xsd" type="VARCHAR"/<case regex="c1">
                    <result<log namelevel="company_id" column="id"/custom">
                </statement>        <property name="text" expression="fn:concat('Company ID  </dblookup>

            <switch source="- ',get-property('company_id'))"/>
   
            <case regex="c1">       </log>
             <log level="custom">      <send>
                  <property name="text"     <endpoint>
                            <address expressionuri="fn:concat('Company ID - ',get-property('company_id'))"/>http://localhost:9000/services/SimpleStockQuoteService"/>
                        </log>endpoint>
                    <send></send>
                </case>
       <endpoint>         <case regex="c2">
                    <address<log urilevel="http://localhost:9000/services/SimpleStockQuoteService"/>"custom">
                        <property name="text" expression="fn:concat('Company ID  </endpoint>- ',get-property('company_id'))"/>
                    </send>log>
                  </case>  <send>
              <case regex="c2">          <endpoint>
                            <log<address leveluri="custom"http://localhost:9000/services/SimpleStockQuoteService"/>
                        <property name="text"</endpoint>
                    </send>
              expression="fn:concat('Company ID - ',get-property('company_id'))"/> </case>
                <case regex="c3">
  </log>                  <log level="custom">
 <send>                       <property  <endpoint>
      name="text" expression="fn:concat('Company ID - ',get-property('company_id'))"/>
                    </log>
<address uri="http://localhost:9000/services/SimpleStockQuoteService"/>                   <send>
     </endpoint>                   <endpoint>
 </send>                 </case>          <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
     <case regex="c3">                  </endpoint>
  <log level="custom">                 </send>
       <property name="text"        </case>
                 <default>
        expression="fn:concat('Company ID - ',get-property('company_id'))"/>           <log level="custom">
         </log>               <property name="text" value="** Unrecognized Company ID <send>**"/>
                     </log>
  <endpoint>                             <address uri="http://localhost:9000/services/SimpleStockQuoteService"/<makefault response="true">
                        </endpoint><code xmlns:tns="http://www.w3.org/2003/05/soap-envelope" value="tns:Receiver"/>
                    </send>    <reason value="** Unrecognized Company ID **"/>
       </case>             </makefault>
   <default>                 <send/>
   <log level="custom">                <drop/>
        <property name="text" value="** Unrecognized Company ID **"/>        </default>
            </log>switch>
            <drop/>
       <makefault> </in>
        <out>
            <log  <code valuelevel="tns:Receivercustom">
                <property        name="text" value="** Reporting to the Database **"/>
      xmlns:tns="http://www.w3.org/2003/05/soap-envelope"/>      </log>
            <dbreport>
     <reason value="** Unrecognized Company ID **"/>      <connection>
              </makefault>      <pool>
                <property name="RESPONSE" value="true"/>      <dsName>reportdb</dsName>
              <header name="To" action="remove"/>    </pool>
                <send</>connection>
                <statement>
   <drop/>                 </default><sql>update company set price=? where name =?</sql>
      </switch>               <drop/>
<parameter xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd"
       </in>          <out>             <log levelexpression="custom">
 //m0:return/m1:last/child::text()" type="DOUBLE"/>
              <property name="text"     <parameter xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd"
                   value="** Reporting to the Database **"/>             </log>expression="//m0:return/m1:symbol/child::text()" type="VARCHAR"/>
            <dbreport>    </statement>
            <connection></dbreport>
            <log level="custom">
      <pool>          <property name="text" value="** Looking up from the         <dsName>reportdb</dsName>Database **"/>
            </log>
           <icClass>com.sun.jndi.rmi.registry.RegistryContextFactory</icClass>
 <dblookup>
                       <url>rmi://localhost:2199</url><connection>
                    <pool>
    <user>esb</user>                         <password>esb</password><dsName>reportdb</dsName>
                    </pool>
                </connection>
                <statement>
                    <sql>update<sql>select * from company set price=? where name =?</sql>
                    <parameter expressionxmlns:m0="http://m0:return/m1:last/child::text()services.samples" xmlns:m1="http://services.samples/xsd"
                               xmlns:m0expression="http//m0:return//services.samples" xmlns:m1="http://services.samples/xsd" type="DOUBLEm1:symbol/child::text()" type="VARCHAR"/>
                    <parameter expression<result name="stock_price" column="//m0:return/m1:symbol/child::text()"price"/>
                </statement>
            </dblookup>
     xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="VARCHAR"/>     <log level="custom">
          </statement>      <property name="text" expression="fn:concat('Stock price    </dbreport>
            <log level="custom"- ',get-property('stock_price'))"/>
            </log>
   <property name="text"        <send/>
        </out>
         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>esb</user>
                        <password>esb</password>
                    </pool>
                </connection>
                <statement>
                    <sql>select * from company where name =?</sql>
                    <parameter expression="//m0:return/m1:symbol/child::text()"
                               xmlns:m0="http://services.samples" xmlns:m1="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>

Prerequisites: Setting up DataBase and DataSources according to the sample setup guide.

Start the Synapse configuration numbered 363: i.e. wso2esb-samples -sn 363
Start the Axis2 server and deploy the SimpleStockQuoteService if not already done

Runs the client as follows

code

</sequence>
</definitions>

This configuration file synapse_sample_363.xml is available in the <ESB_HOME>/repository/samples directory.

When you go through this configuration, you will see that there are two instances of the dblookup mediator and a single instance of the dbreport mediator.

To build the sample

Start the ESB with the sample 363 configuration. For instructions on starting a sample ESB configuration, see Starting the ESB with a sample configuration.

The operation log keeps running until the server starts, which usually takes several seconds. Wait until the server has fully booted up and displays a message similar to "WSO2 Carbon started in n seconds."
Start the Axis2 server. For instructions on starting the Axis2 server, see Starting the Axis2 server.
Deploy the back-end service SimpleStockQuoteService. For instructions on deploying sample back-end services, see Deploying sample back-end services.

Executing the sample

The sample client used here is the Stock Quote Client, which can operate in several modes. For further details on this sample client and its operation modes, see Stock Quote Client.

To execute the sample client

Run the following command from the <ESB_HOME>/samples/axis2Client directory.

Code Block

language	bash

ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/

...

Analyzing the output

If you look at debug log output on the ESB console, you will see the following:

Code Block

 
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

When you analyze the log, you will understand that the ESB the logs the above output as it reads from and writes to the database.

Versions Compared

Old Version 1

New Version Current

Key

Introduction

Prerequisites

Building the sample

Executing the sample

Analyzing the output