Click here to close now.

SYS-CON MEDIA Authors: Pat Romanski, SmartBear Blog, Bob Gourley, Mike Kavis, Elizabeth White

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.

Latest Stories
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...
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...
How do you securely enable access to your applications in AWS without exposing any attack surfaces? The answer is usually very complicated because application environments morph over time in response to growing requirements from your employee base, your partners and your customers. In his session at 16th Cloud Expo, Haseeb Budhani, CEO and Co-founder of Soha, will share five common approaches that DevOps teams follow to secure access to applications deployed in AWS, Azure, etc., and the frict...
With IoT exploding, massive data will transform businesses with opportunities to monetize almost anything that can be measured. In this C-Level Roundtable Discussion at @ThingsExpo, Brendan O’Brien, Aria Systems Co-founder and Chief Evangelist, will lead an expert panel of consultants, thought leaders and practitioners who will look at these new monetization trends, discuss the implications, and detail lessons learned from their collective experience. Finally, the panel will point the way forw...
SOASTA, the leader in performance analytics, today reported record growth of the CloudTest community, exceeding 30,000 registered users of the CloudTest platform in Q1 2015. SOASTA also announced widespread adoption of its Web and mobile testing solutions, with more than 1,600 customers completing more than 285,000 tests using CloudTest during the quarter. This rapid growth shows that DevOps-driven digital businesses are embracing a more continuous approach to testing, and CloudTest is meeting t...
While DevOps most critically and famously fosters collaboration, communication, and integration through cultural change, culture is more of an output than an input. In order to actively drive cultural evolution, organizations must make substantial organizational and process changes, and adopt new technologies, to encourage a DevOps culture. Moderated by Andi Mann, panelists will discuss how to balance these three pillars of DevOps, where to focus attention (and resources), where organizations m...
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...
How is unified communications transforming the way businesses operate? In his session at WebRTC Summit, Arvind Rangarajan, Director of Product Marketing at BroadSoft, will discuss how to extend unified communications experience outside the enterprise through WebRTC. He will also review use cases across different industry verticals. Arvind Rangarajan is Director, Product Marketing at BroadSoft. He has over 19 years of experience in the telecommunications industry in various roles such as Softw...
SYS-CON Events announced today that Vicom Computer Services, Inc., a provider of technology and service 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. They are located at booth #427. Vicom Computer Services, Inc. is a progressive leader in the technology industry for over 30 years. Headquartered in the NY Metropolitan area. Vicom provides products and services based on today’s requirements...
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...
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...
SYS-CON Media announced today that John Treadway’s blog has exceeded 475,000 page views. John Treadway, Vice President at Cloud Technology Partners, has surpassed 475,000 page views on the SYS-CON family of online magazines, which includes Cloud Computing Journal, Internet of Things Journal, Big Data Journal, Microservices Journal, and several others. His blog home page at SYS-CON can be found at JohnTreadway.SYS-CON.com.
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...