Call MS SQL Server SPs from WSO2 ESB

This post will be focusing on MS SQL Server Stored Procedures(SP) and follow up my article titled "How to call database stored procedures from WSO2 ESB" on WSO2 OxygenTank.

You may think "why is he writing a separate post addressing MS SQL Server?". The reason is that; when calling MS SQL Server SPs, you need to slightly modify the mediator configuration in order to successfully call a SP.

Now let's dig in to "how to do this".

Setting up MS SQL database server
1. Install and setup MS SQL Server.

2. Create a sample database in MS SQL Server.
Following sql statments cab be used to create the same enviornment discussed in the above article.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[company]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[company](
[compName] [varchar](10) NULL,
[id] [varchar](10) NULL,
[price] [decimal](18, 0) NULL,
[location] [varchar](10) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getCompany]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[getCompany] @compname varchar(10) as
select * from company where compName = @compname
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[updateCompany]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[updateCompany] @compPrice decimal, @compName varchar(10) as
update company set price = @compPrice where compName = @compName
'
END

Setting up ESB server
1. Download latest version of WSO2 ESB from http://wso2.org/downloads/esb. This tutorial will be demonstrated upon the latest release of the WSO2 ESB. ie. WSO2 ESB-3.0.0.

2. Install it as a standalone server. Install location will be referred to as ESB_HOME from here onwards.

3. When using MS SQL Server, ms-sql-jdbc.jar should be added to the ESB classpath(i.e. ESB_HOME/repository/components/lib/).

4. Start WSO2ESB using the startup scripts.
eg. wso2server.bat for windows environments
wso2server.sh for linux environments

5. Open a web browser and navigate to https://localhost:9443/

6. Login to ESB. The default username/password will be admin/admin. Go through the User Interface and get yourself familiarized with WSO2 ESB environment.

ESB configuration
Following is the Synapse configuration used to call the MS SQL Stored Procedure. Pay close attention on 'how the SP query is written inside the mediator' :).

<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>
<url>jdbc:sqlserver://10.100.1.200:1433;databaseName=ESB_SP_SAMPLE</url>
<user>sa</user>
<password>test</password>
<driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
            </pool>
        </connection>
        <statement>
            <sql>{ call updateCompany(?,?) }</sql>
            <parameter expression="//m0:return/m1:last/child::text()"
                       xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="DOUBLE"/>
            <parameter expression="//m0:return/m1:symbol/child::text()"
                       xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="VARCHAR"/>
        </statement>
    </dbreport>
    <log level="custom">
        <property name="text"
                  value="** Looking up from the Database **"/>
    </log>
    <dblookup>
        <connection>
            <pool>
<url>jdbc:sqlserver://10.100.1.200:1433;databaseName=ESB_SP_SAMPLE</url>
<user>sa</user>
<password>test</password>
<driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
            </pool>
        </connection>
        <statement>
            <sql>{ call getCompany(?) }</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_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>

Running the sample.
1. Start the WSO2 ESB server with the attached synapse configuration named dbreport_synapse_config.xml.

2. Start the Axis2 server and deploy the SimpleStockQuoteService if not already done.

3. Run the client.

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