Welcome!

Industrial IoT Authors: Jyoti Bansal, Yeshim Deniz, Elizabeth White, SmartBear Blog, Liz McMillan

Related Topics: Java IoT, Industrial IoT, IBM Cloud, Machine Learning

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.

@ThingsExpo Stories
SYS-CON Events announced today that delaPlex will exhibit at SYS-CON's @ThingsExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. delaPlex pioneered Software Development as a Service (SDaaS), which provides scalable resources to build, test, and deploy software. It’s a fast and more reliable way to develop a new product or expand your in-house team.
In his keynote at @ThingsExpo, Chris Matthieu, Director of IoT Engineering at Citrix and co-founder and CTO of Octoblu, focused on building an IoT platform and company. He provided a behind-the-scenes look at Octoblu’s platform, business, and pivots along the way (including the Citrix acquisition of Octoblu).
SYS-CON Events announced today that Outscale, a global pure play Infrastructure as a Service provider and strategic partner of Dassault Systèmes, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Founded in 2010, Outscale simplifies infrastructure complexities and boosts the business agility of its customers. Outscale delivers a secure, reliable and industrial strength solution for its customers, which in...
SYS-CON Events announced today that Outscale will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Outscale's technology makes an automated and adaptable Cloud available to businesses, supporting them in the most complex IT projects while controlling their operational aspects. You boost your IT infrastructure's reactivity, with request responses that only take a few seconds.
SYS-CON Events announced today that Systena America will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Systena Group has been in business for various software development and verification in Japan, US, ASEAN, and China by utilizing the knowledge we gained from all types of device development for various industries including smartphones (Android/iOS), wireless communication, security technology and IoT serv...
DevOps at Cloud Expo – being held October 31 - November 2, 2017, 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 world's largest enterprises – and delivering real r...
Five years ago development was seen as a dead-end career, now it’s anything but – with an explosion in mobile and IoT initiatives increasing the demand for skilled engineers. But apart from having a ready supply of great coders, what constitutes true ‘DevOps Royalty’? It’ll be the ability to craft resilient architectures, supportability, security everywhere across the software lifecycle. In his keynote at @DevOpsSummit at 20th Cloud Expo, Jeffrey Scheaffer, GM and SVP, Continuous Delivery Busine...
In order to meet the rapidly changing demands of today’s customers, companies are continually forced to redefine their business strategies in order to meet these needs, stay relevant and continue to see profitable growth. IoT deployment and development is integral in this transformation, and today businesses are increasingly seeing the value of investing their resources into IoT deployments. These technologies are able increase ROI through projects such as connecting supply chains or enabling sm...
SYS-CON Events announced today that CollabNet, a global leader in enterprise software development, release automation and DevOps solutions, will be a Bronze Sponsor of SYS-CON's 20th International Cloud Expo®, taking place from June 6-8, 2017, at the Javits Center in New York City, NY. CollabNet offers a broad range of solutions with the mission of helping modern organizations deliver quality software at speed. The company’s latest innovation, the DevOps Lifecycle Manager (DLM), supports Value S...
SYS-CON Events announced today that Peak 10, Inc., a national IT infrastructure and cloud services provider, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Peak 10 provides reliable, tailored data center and network services, cloud and managed services. Its solutions are designed to scale and adapt to customers’ changing business needs, enabling them to lower costs, improve performance and focus intern...
Everywhere we turn in our industry we can find strong opinions about the direction, type and nature of cloud’s impact on computing and business. Another word that is used in every context in our industry is “hybrid.” In his session at 20th Cloud Expo, Alvaro Gonzalez, Director of Technical, Partner and Field Marketing at Peak 10, will use a combination of a few conceptual props and some research recently commissioned by Peak 10 to offer a real-world consideration of how the various categories of...
A strange thing is happening along the way to the Internet of Things, namely far too many devices to work with and manage. It has become clear that we'll need much higher efficiency user experiences that can allow us to more easily and scalably work with the thousands of devices that will soon be in each of our lives. Enter the conversational interface revolution, combining bots we can literally talk with, gesture to, and even direct with our thoughts, with embedded artificial intelligence, whic...
SYS-CON Events announced today that Super Micro Computer, Inc., a global leader in compute, storage and networking technologies, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Supermicro (NASDAQ: SMCI), the leading innovator in high-performance, high-efficiency server technology, is a premier provider of advanced server Building Block Solutions® for Data Center, Cloud Computing, Enterprise IT, Hadoop/...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend @CloudExpo | @ThingsExpo, June 6-8, 2017, at the Javits Center in New York City, NY and October 31 - November 2, 2017, Santa Clara Convention Center, CA. Learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
Multiple data types are pouring into IoT deployments. Data is coming in small packages as well as enormous files and data streams of many sizes. Widespread use of mobile devices adds to the total. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists will look at the tools and environments that are being put to use in IoT deployments, as well as the team skills a modern enterprise IT shop needs to keep things running, get a handle on all this data, and deli...
SYS-CON Events announced today that SoftLayer, an IBM Company, has been named “Gold Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. SoftLayer, an IBM Company, provides cloud infrastructure as a service from a growing number of data centers and network points of presence around the world. SoftLayer’s customers range from Web startups to global enterprises.
In his opening keynote at 20th Cloud Expo, Michael Maximilien, Research Scientist, Architect, and Engineer at IBM, will motivate why realizing the full potential of the cloud and social data requires artificial intelligence. By mixing Cloud Foundry and the rich set of Watson services, IBM's Bluemix is the best cloud operating system for enterprises today, providing rapid development and deployment of applications that can take advantage of the rich catalog of Watson services to help drive insigh...
Existing Big Data solutions are mainly focused on the discovery and analysis of data. The solutions are scalable and highly available but tedious when swapping in and swapping out occurs in disarray and thrashing takes place. The resolution for thrashing through machine learning algorithms and support nomenclature is through simple techniques. Organizations that have been collecting large customer data are increasingly seeing the need to use the data for swapping in and out and thrashing occurs ...
SYS-CON Events announced today that delaPlex will exhibit at SYS-CON's @CloudExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. delaPlex pioneered Software Development as a Service (SDaaS), which provides scalable resources to build, test, and deploy software. It’s a fast and more reliable way to develop a new product or expand your in-house team.
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend @CloudExpo | @ThingsExpo, June 6-8, 2017, at the Javits Center in New York City, NY and October 31 - November 2, 2017, Santa Clara Convention Center, CA. Learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.