Invoking stored procedure from DataPower ESB

On a recent project, a few stored procedures had to be exposed as web services.  Actually, the stored procedures already took an XML message as input and returned an XML message as output.

Initial plan was to write some .Net components to expose the stored procedures in the SQL Server 2008 database as web services.  The web services themselves would then be exposed via a DataPower ESB.

But with the DataPower ESB already in place and being capable of directly invoking the stored procedure (see also), the .Net components and underlying Windows servers could be avoided.

A WSDL was created based on the XML input and output messages.  That WSDL was the starting point to create a Web Service Proxy (WSP) on DataPower.  Within a rule of the WSP policy, a Transform step (XSLT) is executed that will invoke the stored procedure using a DataPower specific extension function.

A standard Web Service Proxy will invoke a back-end web service.  The rule of the Web Service Proxy immediately returns – wihout invoking any back-end web service – by setting the variable service/mpgw/skip-backside to 1.

Below the XSLT used to invoke the stored procedure using the dp:sql-execute function.  We obtain the XML part from the soap:Body and pass that on to the stored procedure.  The respone XML is wrapped in a SOAP Envelope.
<?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:xxx=”http://xxx.com/
xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/
extension-element-prefixes=”dp” >
<xsl:template match=”/”>
<xsl:variable name=”statement”>exec stored_proc ?</xsl:variable>
<xsl:variable name=”result”>
<dp:sql-execute source=”‘DS_DB’” statement=”$statement” >
<arguments>
<argument name=”request_id” type=”SQL_XML” mode=”INPUT”>
<xsl:copy-of
select=”./soap:Envelope/soap:Body/xxx:Operation” />
</argument>
</arguments>
</dp:sql-execute>
</xsl:variable>
<soap:Envelope>
<soap:Body>
<xsl:copy-of select=”dp:parse($result)” />
</soap:Body>
</soap:Envelope>
</xsl:template>
</xsl:stylesheet>
Note on the last few lines: the XML response from the stored procedure was contained in a CDATA segment.  With the utility function dp:parse() the XML content within the CDATA could be obtained.

Conclusion: one of the great features of Integration tools (ESB, EAI) is their built-in database connectivity. The database adapter of an ESB allows querying, updating and invoking of stored procedures. Database adapters typically also support polling (staging) tables for new or modified records.

Author: Guy

blogger

blogger

Curious to know more about this topic?

Working at i8c

i8c is a system integrator that strives for an informal atmosphere between its employees, who have an average age of approx 30 years old. We invest a lot of effort in the professional development of each individual, through a direct connection between the consultants and the management (no multiple layers of middle management). We are based in Kontich, near Antwerp, but our customers are mainly located in the triangle Ghent-Antwerp-Brussels and belong to the top 500 companies in Belgium (Securex, Electrabel, UCB, etc…).

Quality Assurance

i8c is committed to delivering quality services and providing customer satisfaction. That’s why we invested in the introduction of a Quality Management System, which resulted in our ISO9001:2000 certification. This guarantees that we will meet your expectations, as a reliable, efficient and mature partner for your SOA & integration projects.

i8c - ISO9001-2015

Also worth reading

AWS AppFlow: Streamlining SaaS Integrations with AWS Services

In today’s digital world, organizations are constantly looking for ways to streamline their workflows and improve their data management processes. One of the key challenges that organizations face is integrating their various software as a service (SaaS) applications with their data management systems. This is

Read More »

Apigee Scope Validation using OpenAPI Specification

In API security and management, we often use a lot of different security mechanisms to protect the requested resource behind the API Gateway. One of these mechanisms is the validation of scopes to authorize a client on a specific sub-resource of the API. Most of

Read More »