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