Click here to close now.

Welcome!

AJAX & REA Authors: Kevin Jackson, Carmen Gonzalez, Elizabeth White, Hovhannes Avoyan, XebiaLabs Blog

Related Topics: Java, XML, Websphere, AJAX & REA

Java: Article

WebSphere Datapower Configuration for Database

Construct Database Query from Requests URI

The purpose of this article is to describe the steps involve to configure Datapower service for the Database communication. The service will parse the incoming parameters from the request, construct a SQL query string and returns the data from the Database, based on the constructed query.

Database
For the demonstration, the following table "DataPowerTest" is created in MS SQL Server. The following columns and data were added as well.

Datapower Configurations

1. In the Datapower, create SQL Data Source with the following values

Input

Values

Name

GallagherDirectoryDB

Database Type

Microsoft SQL Server

Connection User Name

<username>

Connection Password

<password>

Data Source ID

GallagherDirectory <database name>

Data Source Host

<database hostname>

Data Source Port

1433

2.    Create the GallagherDirectoryQuery.xslt file and type the following. Upload the file on the Datapower

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:dp="http://www.datapower.com/extensions"

xmlns:dpconfig="http://www.datapower.com/param/config"

extension-element-prefixes="dp"

exclude-result-prefixes="dp dpconfig">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/">

<xsl:variable name="incomingURI">

<xsl:value-of select="dp:variable('var://service/URI')"/>

</xsl:variable>

<xsl:variable name="squote">'</xsl:variable>

<xsl:variable name="tempVar">

<xsl:value-of select="substring-after($incomingURI,'?')"/>

</xsl:variable>

<xsl:call-template name="parseAtt">

<xsl:with-param name="tempVar" select="$tempVar" />

<xsl:with-param name="andVar" select="''" />

</xsl:call-template>

</xsl:template>

<xsl:template name="parseAtt">

<xsl:param name="tempVar" />

<xsl:param name="andVar" />

<xsl:variable name="squote">'</xsl:variable>

<xsl:variable name="att1Var">

<xsl:if test="contains($tempVar,'&amp;')">

<xsl:value-of select="substring-before($tempVar,'&amp;')" />

</xsl:if>

<xsl:if test="not(contains($tempVar,'&amp;'))">

<xsl:value-of select="$tempVar"/>

</xsl:if>

</xsl:variable>

<xsl:variable name="att2Var">

<xsl:value-of select="substring-after($tempVar,'&amp;')" />

</xsl:variable>

<xsl:variable name="parameter1Var">

<xsl:if test="contains($att1Var,'=')">

<xsl:value-of select="substring-before($att1Var,'=')"/>

</xsl:if>

</xsl:variable>

<xsl:variable name="value1Var">

<xsl:if test="contains($att1Var,'=')">

<xsl:value-of select="substring-after($att1Var,'=')"/>

</xsl:if>

</xsl:variable>

<xsl:variable name="dbVar" select="concat($andVar, $parameter1Var,' like ',$squote, '%',$value1Var,'%',$squote)"/>

<xsl:if test="not($att2Var='')">

<xsl:call-template name="parseAtt">

<xsl:with-param name="tempVar" select="substring-after($tempVar,'&amp;')" />

<xsl:with-param name="andVar" select="concat($dbVar, ' and ')" />

</xsl:call-template>

</xsl:if>

<xsl:if test="$att2Var=''">

<xsl:variable name="query">

select * from DataPowerTest Where

<xsl:value-of select="$dbVar"/>

</xsl:variable>

<xsl:variable name="result" select="dp:sql-execute('GallagherDirectoryDB',$query)" />

<xsl:copy-of select="$result" />

</xsl:if>

</xsl:template>

</xsl:stylesheet>

3. Create a new Multi-Protocol Gateway with the following values

Input

Values

Name

RESTDemoMPGW

Type

dynamic-backends

Response Type

Non-XML

Request Type

SOAP-XML

4. Create a HTTP Front side handler with the following values

Input

Values

Name

RESTDemoFSH

Local IP Address

<setup on one of the Ethernet Interface>

Port

80

Allow Methods and Version

GET (and all the other default values)

5. At the Multi-Protocol Gateway screen, click on the "Advance" tab menu and click on "Process Messages Whose Body Is Empty"

6. Click on Apply.

7. On RESTDemoMPGW, create a new Policy and named it "RESTDEmoPolicy"

8. Create a new "Client to Server" rule and configure Match action icon for "* " for URL (match all)

9. Assign a "Transform" action to the rule and assign the "GallagherDirectoryQuery.xslt" file.

10. Drag the "Advanced" action to the rule and select "SET Variable". Set the following variable

Variable

Values

/service/mpgw/skip-backside

1

11. Click "Apply Policy" and then "Close Window". Click on "Apply" at the Multi-Protocol Gateway screen.

12. Save configurations.

Testing

1. From the SOAPUI, send the following request to the MPGW

http://<datapowerhostname>:<port>/people/?Id=2&Name=T

2. The successful query with the condition "Id=2" and "Name=T*" will send the data back from database.

3. From the SOAPUI, send another following request to the MPGW

http://<datapowerhostname>:<port>/people/?Id=1&Name=K&Division=C

4. The successful query with the condition "Id=1", "Name=K*" and "Division=C*" will send the data back from database.

More Stories By Asim Saddal

Asim Saddal works in the Middleware (WebSphere Application Server, WebSphere Datapower, WebSphere Process Server, WebSphere VE) practice of IBM Software Services for WebSphere.

@CloudExpo Stories
of cloud, colocation, managed services and disaster recovery solutions, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. TierPoint, LLC, is a leading national provider of information technology and data center services, including cloud, colocation, disaster recovery and managed IT services, with corporate headquarters in St. Louis, MO. TierPoint was formed through the strategic combination of some of t...
SYS-CON Events announced today that Column Technologies, a global technology solutions company, will exhibit at SYS-CON's DevOps Summit 2015 New York, which will take place June 9-11, 2015, at the Javits Center in New York City, NY. Established in 1998, Column Technologies is a leader in application performance and infrastructure management for commercial and federal markets. The company is headquartered in the United States, with a diverse and talented team of more than 350 employees around th...
SYS-CON Events announced today that Ciqada will exhibit at SYS-CON's @ThingsExpo, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Ciqada™ makes it easy to connect your products to the Internet. By integrating key components - hardware, servers, dashboards, and mobile apps - into an easy-to-use, configurable system, your products can quickly and securely join the internet of things. With remote monitoring, control, and alert messaging capability, you will mee...
SYS-CON Media announced today that @WebRTCSummit Blog, the largest WebRTC resource in the world, has been launched. @WebRTCSummit Blog offers top articles, news stories, and blog posts from the world's well-known experts and guarantees better exposure for its authors than any other publication. @WebRTCSummit Blog can be bookmarked ▸ Here @WebRTCSummit conference site can be bookmarked ▸ Here
Health care systems across the globe are under enormous strain, as facilities reach capacity and costs continue to rise. M2M and the Internet of Things have the potential to transform the industry through connected health solutions that can make care more efficient while reducing costs. In fact, Vodafone's annual M2M Barometer Report forecasts M2M applications rising to 57 percent in health care and life sciences by 2016. Lively is one of Vodafone's health care partners, whose solutions enable o...
The best mobile applications are augmented by dedicated servers, the Internet and Cloud services. Mobile developers should focus on one thing: writing the next socially disruptive viral app. Thanks to the cloud, they can focus on the overall solution, not the underlying plumbing. From iOS to Android and Windows, developers can leverage cloud services to create a common cross-platform backend to persist user settings, app data, broadcast notifications, run jobs, etc. This session provide...
SYS-CON Events announced today that GENBAND, a leading developer of real time communications software solutions, has been named “Silver Sponsor” of SYS-CON's WebRTC Summit, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. The GENBAND team will be on hand to demonstrate their newest product, Kandy. Kandy is a communications Platform-as-a-Service (PaaS) that enables companies to seamlessly integrate more human communications into their Web and mobile applicatio...
Public Cloud IaaS started it's life in the developer and startup communities and has grown rapidly to a $20B+ industry, but it still pales in comparison to how much is spent worldwide on IT: $3.6 trillion. In fact, there are 8.6 million data centers worldwide, the reality is many small and medium sized business have server closets and colocation footprints filled with servers and storage gear. While on-premise environment virtualization may have peaked at 75%, the Public Cloud has lagged in ado...
Dave will share his insights on how Internet of Things for Enterprises are transforming and making more productive and efficient operations and maintenance (O&M) procedures in the cleantech industry and beyond. Speaker Bio: Dave Landa is chief operating officer of Cybozu Corp (kintone US). Based in the San Francisco Bay Area, Dave has been on the forefront of the Cloud revolution driving strategic business development on the executive teams of multiple leading Software as a Services (SaaS) ap...
SYS-CON Events announced today that BroadSoft, the leading global provider of Unified Communications and Collaboration (UCC) services to operators worldwide, has been named “Gold Sponsor” of SYS-CON's WebRTC Summit, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. BroadSoft is the leading provider of software and services that enable mobile, fixed-line and cable service providers to offer Unified Communications over their Internet Protocol networks. The Compa...
The 5th International DevOps Summit, co-located with 17th International Cloud Expo – being held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA – announces that its Call for Papers is open. Born out of proven success in agile development, cloud computing, and process automation, DevOps is a macro trend you cannot afford to miss. From showcase success stories from early adopters and web-scale businesses, DevOps is expanding to organizations of all sizes, including the...
DevOps tasked with driving success in the cloud need a solution to efficiently leverage multiple clouds while avoiding cloud lock-in. Flexiant today announces the commercial availability of Flexiant Concerto. With Flexiant Concerto, DevOps have cloud freedom to automate the build, deployment and operations of applications consistently across multiple clouds. Concerto is available through four disruptive pricing models aimed to deliver multi-cloud at a price point everyone can afford.
ProfitBricks has launched its new DevOps Central and REST API, along with support for three multi-cloud libraries and a Python SDK. This, combined with its already existing SOAP API and its new RESTful API, moves ProfitBricks into a position to better serve the DevOps community and provide the ability to automate cloud infrastructure in a multi-cloud world. Following this momentum, ProfitBricks has also introduced several libraries that enable developers to use their favorite language to code ...
What exactly is a cognitive application? In her session at 16th Cloud Expo, Ashley Hathaway, Product Manager at IBM Watson, will look at the services being offered by the IBM Watson Developer Cloud and what that means for developers and Big Data. She'll explore how IBM Watson and its partnerships will continue to grow and help define what it means to be a cognitive service, as well as take a look at the offerings on Bluemix. She will also check out how Watson and the Alchemy API team up to off...
The IoT Bootcamp is coming to Cloud Expo | @ThingsExpo on June 9-10 at the Javits Center in New York. Instructor. Registration is now available at http://iotbootcamp.sys-con.com/ Instructor Janakiram MSV previously taught the famously successful Multi-Cloud Bootcamp at Cloud Expo | @ThingsExpo in November in Santa Clara. Now he is expanding the focus to Janakiram is the founder and CTO of Get Cloud Ready Consulting, a niche Cloud Migration and Cloud Operations firm that recently got acquir...
The 17th International Cloud Expo has announced that its Call for Papers is open. 17th International Cloud Expo, to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, APM, APIs, Microservices, Security, Big Data, Internet of Things, DevOps and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding bu...
In 2015, 4.9 billion connected "things" will be in use. By 2020, Gartner forecasts this amount to be 25 billion, a 410 percent increase in just five years. How will businesses handle this rapid growth of data? Hadoop will continue to improve its technology to meet business demands, by enabling businesses to access/analyze data in real time, when and where they need it. Cloudera's Chief Technologist, Eli Collins, will discuss how Big Data is keeping up with today's data demands and how in t...
SYS-CON Media announced today that @ThingsExpo Blog launched with 7,788 original stories. @ThingsExpo Blog offers top articles, news stories, and blog posts from the world's well-known experts and guarantees better exposure for its authors than any other publication. @ThingsExpo Blog can be bookmarked. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago.
As Marc Andreessen says software is eating the world. Everything is rapidly moving toward being software-defined – from our phones and cars through our washing machines to the datacenter. However, there are larger challenges when implementing software defined on a larger scale - when building software defined infrastructure. In his session at 16th Cloud Expo, Boyan Ivanov, CEO of StorPool, will provide some practical insights on what, how and why when implementing "software-defined" in the dat...
SYS-CON Events announced today that robomq.io will exhibit at SYS-CON's @ThingsExpo, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. robomq.io is an interoperable and composable platform that connects any device to any application. It helps systems integrators and the solution providers build new and innovative products and service for industries requiring monitoring or intelligence from devices and sensors.