Friday, May 28, 2010

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

Wednesday, May 19, 2010

SOA Summer School - 2010

Summer School will focus on Service Oriented Architecture (SOA) concepts, technologies, and best practices. In addition to that, this year's Summer School sessions will be including insights into proven techniques for cloud deployments, business process management (BPM), complex event processing (CEP) , business rules processing and more.

Summer School 2010 runs for two months starting 10th June and ending on 29th July. Do you know what's the best news about Summer School is ? IT'S ABSOLUTELY FREE ! :) yes, free as in open source ;)

If you are interested, you can register here.

Tuesday, May 11, 2010

Call database stored procedures from WSO2 ESB

Stored procedure is a powerful mechanism to interact with a relational database. It allows business logic to be embedded inside database as an API. Stored procedure is stored in a precompiled format within the database itself resulting in faster execution speed.

WSO2 Enterprise Service Bus is capable of calling database Stored Procedures via the db mediators. If your application uses stored procedures and you want to expose them to outside business systems, WSO2 ESB is the answer that you were looking for.

Read the full article on OT.

Sunday, May 2, 2010

The WSO2 Enterprise Service Bus (ESB) 3.0.0 Released

The WSO2 ESB team is pleased to announce the release of version 3.0.0 of the Open Source Enterprise Service Bus (ESB).

WSO2 ESB is a fast, lightweight and user friendly open source Enterprise Service Bus (ESB) distributed under the Apache Software License v2.0. WSO2 ESB allows system administrators and developers to easily configure message routing, intermediation, transformation, logging, task scheduling, fail over routing and load balancing. It also supports transport switching, eventing, rule based mediation and priority based mediation for advanced integration requirements. The ESB runtime is designed to be completely asynchronous, non-blocking and streaming based on the Apache Synapse mediation engine.

WSO2 ESB 3.0.0 is developed on top of the revolutionary WSO2 Carbon platform (Middleware a' la carte), an OSGi based framework that provides seamless modularity to your SOA via componentization. This release also contains many new features and a range of optional components (add-ons) that can be installed to customize the behavior of the ESB. Further, any existing features of the ESB which are not required to your environment can be easily removed using the underlying provisioning framework of Carbon. In brief, WSO2 ESB can be fully customized and tailored to meet your exact SOA needs.

You can download this distribution from http://wso2.org/downloads/esb and give it a try.

How to Run

  1. Extract the downloaded zip
  2. Go to the bin directory in the extracted folder
  3. Run the wso2server.sh or wso2server.bat as appropriate
  4. Point your browser to the URL https://localhost:9443/carbon
  5. Use "admin", "admin" as the username and password to login as an admin and create a user account
  6. Assign the required permissions to the user through a role
  7. If you need to start the OSGi console with the server use the property -DosgiConsole when starting the server. The INSTALL.txt file found on the installation directory will give you a comprehensive set of options and properties that can be passed into the startup script
  8. Sample configurations can be started by the wso2esb-samples script passing the sample number with the -sn option (Please have a look at the samples guide for more information, on running samples)

New Features of WSO2 ESB 3.0.0

  • Priority based mediation through priority executors
  • WS-Discovery support and dynamic endpoint discovery
  • Message Relay for efficient pass through of messages
  • Component manager to install and uninstall features (provisioning support)
  • Common Internet File System (CIFS) support through the VFS transport
  • File locking functionality in the VFS transport to support concurrent polling
  • Smooks mediator for efficient message transformation
  • Enrich mediator for smart message manipulation
  • OAuth mediator for 2-legged OAuth support
  • Default endpoint UI
  • Hot deploy and hot update configuration elements (sequences, endpoints, proxy services etc)
  • Transport level statistics collection and monitoring
  • POX security support
  • Dependency detection and alerting for mediation configuration elements
  • Mediation statistics API and custom mediation statistics consumers
  • Multiple certificate/identity support in the NHTTP transport sender
  • Improved logging capabilities for the NHTTP transport
  • Templates based proxy service development in the UI
  • Dashboard to monitor server environment and runtime
  • Easy creation and management capabilities for dynamic sequences and endpoints
  • Pagination to service management, endpoint management and sequence management UIs
  • Obtaining resources like WSDL's through web proxy servers

Key Features of WSO2 ESB

  • Proxy services - facilitating synchronous/asynchronous transport, interface (WSDL/Schema/Policy), message format (SOAP 1.1/1.2, POX/REST, Text, Binary), QoS (WS-Addressing/WS-Security/WS-RM) and optimization switching (MTOM/SwA).
  • Non-blocking HTTP/S transports based on Apache HttpCore-NIO for ultrafast execution and support for thousands of connections at high concurreny with constant memory usage.
  • Built in Registry/Repository, facilitating dynamic updating and reloading of the configuration and associated resources (e.g. XSLTs, XSD, WSDL, Policies, JS configurations ..)
  • Easily extendable via custom Java classes (mediator and command)/Spring configurations, or BSF Scripting languages (Javascript, Ruby, Groovy, etc.)
  • Built in support for scheduling tasks using the Quartz scheduler.
  • Load-balancing (with or without sticky sessions)/Fail-over, and clustered Throttling and Caching support
  • WS-Security, WS-Reliable Messaging, Caching & Throttling configurable via (message/operation/service level) WS-Policies
  • Lightweight, XML and Web services centric messaging model
  • Support for industrial standards (Hessian binary web service protocol/ Financial Information eXchange protocol and optional Health Level-7 protocol)
  • Enhanced support for the VFS (File/FTP/SFTP), JMS, Mail transports with optional TCP/UDP transports and transport switching among any of the above transports
  • Support for message splitting & aggregation using the EIP and service callouts
  • Database lookup & store support with DBMediators with reusable database connection pools
  • WS-Eventing support with event sources and event brokering
  • Rule based mediation of the messages using the Drools rule engine
  • Transactions support via the JMS transport and Transaction mediator for database mediators
  • Internationalized GUI management console with user management for configuration development
  • Integrated monitoring support with statistics, configurable logging and tracing
  • JMX monitoring support and JMX management capabilities like, Graceful/Forceful shutdown/restart

Bugs Fixed in This Release

This release of WSO2 ESB comes with a number of bug fixes, both in the base framework and the ESB specific componenents. All the issues which have been fixed in ESB 3.0.0 are recorded at following locations:

Known Issues

  • Endpoint UI does not support selecting already existing endpoints as child endpoints when creating load balance/failover endpoints
  • HTTP GET requests performed on an endpoint that has a trailing '/' character, do not work properly
  • SOAP tracer does not work when the message relay is activated
  • The sequence editor and the built-in XML editors do not work properly on Google Chrome

All the open issues pertaining to WSO2 ESB 3.0 are reported at following locations:

How You Can Contribute

Mailing Lists

Join our mailing list and correspond with the developers directly.

Reporting Issues

WSO2 encourages you to report issues and your enhancement requests for the WSO2 ESB using the public JIRA.

You can also watch how they are resolved, and comment on the progress..

Discussion Forums

Alternatively, questions could be raised using the forums available.

WSO2 ESB Forum : Discussion forum for WSO2 ESB developers/users

Support

We are committed to ensuring that your enterprise middleware deployment is completely supported from evaluation to production. Our unique approach ensures that all support leverages our open development methodology and is provided by the very same engineers who build the technology.

For more details and to take advantage of this unique opportunity please visit http://wso2.com/support.

For more information about WSO2 ESB please see http://wso2.com/products/enterprise-service-bus.

-- The WSO2 ESB Team --