Welcome!

Related Topics: Java IoT, Industrial IoT, IBM Cloud, IoT User Interface

Java IoT: 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
If you’re responsible for an application that depends on the data or functionality of various IoT endpoints – either sensors or devices – your brand reputation depends on the security, reliability, and compliance of its many integrated parts. If your application fails to deliver the expected business results, your customers and partners won't care if that failure stems from the code you developed or from a component that you integrated. What can you do to ensure that the endpoints work as expect...
Just over a week ago I received a long and loud sustained applause for a presentation I delivered at this year’s Cloud Expo in Santa Clara. I was extremely pleased with the turnout and had some very good conversations with many of the attendees. Over the next few days I had many more meaningful conversations and was not only happy with the results but also learned a few new things. Here is everything I learned in those three days distilled into three short points.
SYS-CON Events announced today that ReadyTalk, a leading provider of online conferencing and webinar services, has been named Vendor Presentation Sponsor at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. ReadyTalk delivers audio and web conferencing services that inspire collaboration and enable the Future of Work for today’s increasingly digital and mobile workforce. By combining intuitive, innovative tec...
WebRTC adoption has generated a wave of creative uses of communications and collaboration through websites, sales apps, customer care and business applications. As WebRTC has become more mainstream it has evolved to use cases beyond the original peer-to-peer case, which has led to a repeating requirement for interoperability with existing infrastructures. In his session at @ThingsExpo, Graham Holt, Executive Vice President of Daitan Group, will cover implementation examples that have enabled ea...
Apache Hadoop is a key technology for gaining business insights from your Big Data, but the penetration into enterprises is shockingly low. In fact, Apache Hadoop and Big Data proponents recognize that this technology has not yet achieved its game-changing business potential. In his session at 19th Cloud Expo, John Mertic, director of program management for ODPi at The Linux Foundation, will explain why this is, how we can work together as an open data community to increase adoption, and the i...
Fifty billion connected devices and still no winning protocols standards. HTTP, WebSockets, MQTT, and CoAP seem to be leading in the IoT protocol race at the moment but many more protocols are getting introduced on a regular basis. Each protocol has its pros and cons depending on the nature of the communications. Does there really need to be only one protocol to rule them all? Of course not. In his session at @ThingsExpo, Chris Matthieu, co-founder and CTO of Octoblu, walk you through how Oct...
There is growing need for data-driven applications and the need for digital platforms to build these apps. In his session at 19th Cloud Expo, Muddu Sudhakar, VP and GM of Security & IoT at Splunk, will cover different PaaS solutions and Big Data platforms that are available to build applications. In addition, AI and machine learning are creating new requirements that developers need in the building of next-gen apps. The next-generation digital platforms have some of the past platform needs a...
Smart Cities are here to stay, but for their promise to be delivered, the data they produce must not be put in new siloes. In his session at @ThingsExpo, Mathias Herberts, Co-founder and CTO of Cityzen Data, will deep dive into best practices that will ensure a successful smart city journey.
Businesses are struggling to manage the information flow and interactions between all of these new devices and things jumping on their network, and the apps and IT systems they control. The data businesses gather is only helpful if they can do something with it. In his session at @ThingsExpo, Chris Witeck, Principal Technology Strategist at Citrix, will discuss how different the impact of IoT will be for large businesses, expanding how IoT will allow large organizations to make their legacy ap...
Adobe is changing the world though digital experiences. Adobe helps customers develop and deliver high-impact experiences that differentiate brands, build loyalty, and drive revenue across every screen, including smartphones, computers, tablets and TVs. Adobe content solutions are used daily by millions of companies worldwide-from publishers and broadcasters, to enterprises, marketing agencies and household-name brands. Building on its established design leadership, Adobe enables customers not o...
SYS-CON Events announced today that Tintri Inc., a leading producer of VM-aware storage (VAS) for virtualization and cloud environments, will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Tintri VM-aware storage is the simplest for virtualized applications and cloud. Organizations including GE, Toyota, United Healthcare, NASA and 6 of the Fortune 15 have said “No to LUNs.” With Tintri they mana...
SYS-CON Events announced today that Numerex Corp, a leading provider of managed enterprise solutions enabling the Internet of Things (IoT), will exhibit at the 19th International Cloud Expo | @ThingsExpo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Numerex Corp. (NASDAQ:NMRX) is a leading provider of managed enterprise solutions enabling the Internet of Things (IoT). The Company's solutions produce new revenue streams or create operating...
24Notion is full-service global creative digital marketing, technology and lifestyle agency that combines strategic ideas with customized tactical execution. With a broad understand of the art of traditional marketing, new media, communications and social influence, 24Notion uniquely understands how to connect your brand strategy with the right consumer. 24Notion ranked #12 on Corporate Social Responsibility - Book of List.
Major trends and emerging technologies – from virtual reality and IoT, to Big Data and algorithms – are helping organizations innovate in the digital era. However, to create real business value, IT must think beyond the ‘what’ of digital transformation to the ‘how’ to harness emerging trends, innovation and disruption. Architecture is the key that underpins and ties all these efforts together. In the digital age, it’s important to invest in architecture, extend the enterprise footprint to the cl...
Why do your mobile transformations need to happen today? Mobile is the strategy that enterprise transformation centers on to drive customer engagement. In his general session at @ThingsExpo, Roger Woods, Director, Mobile Product & Strategy – Adobe Marketing Cloud, covered key IoT and mobile trends that are forcing mobile transformation, key components of a solid mobile strategy and explored how brands are effectively driving mobile change throughout the enterprise.