This blog entry was written in collaboration with my colleague Jørgen Michelsen.
Introduction
Most of us have tried using the JDBC adapter to retrieve data from a database using the nice XML SQL message format to hold our request and response. This works well as long as we are retrieving row sets where each field contains a simple value such as a string or a number. But sometimes the data we need to retrieve is more complex. For instance, some databases (like Oracle) are able to return XML from stored procedure calls, and this is a very convenient way of returning complex data structures.
There is, however, a catch when combining XML results from stored procedures with the JDBC adapter’s XML SQL format: the XML returned by the stored procedure will be wrapped in the XML SQL response structure. More specifically, the XML will be "escaped" and appear as a single character string in the element representing the output parameter of the stored procedure.
This blog demonstrates how to recover the original un-escaped XML using a simple Java mapping.
Example
We have a stored procedure named my_stored_procedure which takes a single input parameter and returns an XML document in the output parameter p_xml. Here is an example of the XML returned:
Message type definitions
In order to call this stored procedure using the JDBC adapter, we first have to create a message type for the XML SQL request. The element p_xml corresponds to our output parameter. Message type 1:
We must also create a message type to hold the XML SQL response from the JDBC adapter. Message type 2:
In the actual response message, the element p_xml will contain an “escaped” version of the XML document that we are actually interested in. (In other words, reserved XML characters appear as XML entities such as < and >):
Finally, we define a message type for the actual XML document which we will "unwrap". Its structure must of course be in agreement with the XML returned by the stored procedure. Message type 3:
Message Interface and Interface Mapping
Since we are getting data, a synchronous interface must be defined using message type 1 and 2 as the request and response, respectively:
In the interface mapping the target structure of the request mapping will be message type 1.
For the response mapping, we insert our Java mapping before the mapping program which will transform from message type 3 to whatever format the requester of data needs.
The Java mapping takes care of the transformation from message type 2 to message type 3, i.e. it extracts and un-escapes the original XML produced by the stored procedure.
Java mapping program
It is sufficient to use a very simple SAX parser in the Java mapping, letting it wait for the appropriate element and then return all the characters in that element. The SAX parser will automatically un-escape XML entities. Here is the source code:
package dk.applicon.xi.mapping;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.HashMap;
import java.util.Map;
import javax.xml.parsers.FactoryConfigurationError;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;
import com.sap.aii.mapping.api.StreamTransformation;
import com.sap.aii.mapping.api.StreamTransformationException;
public class GetResponseFromXMLSQLFormat implements StreamTransformation {
private static final String CHARACTER_ENCODING = "UTF-8";
private class SAXEventHandler extends DefaultHandler {
private static final String RESPONSE_XML_ELEMENT_NAME = "p_xml";
private static final int NOT_READY = 0;
private static final int READY_FOR_RESPONSE_XML = 1;
int payloadStatus = NOT_READY;
Writer outputWriter;
private SAXEventHandler(Writer outputWriter) {
this.outputWriter = outputWriter;
}
public void startElement(String namespaceURI, String localName, String qName, Attributes atts) throws SAXException {
if (RESPONSE_XML_ELEMENT_NAME.equals(localName)) {
payloadStatus = READY_FOR_RESPONSE_XML;
}
}
public void endElement(String namespaceURI, String localName, String qName) throws SAXException {
if (RESPONSE_XML_ELEMENT_NAME.equals(localName)) {
payloadStatus = NOT_READY;
}
}
public void characters(char[] ch, int start, int length) throws SAXException {
switch (payloadStatus) {
case READY_FOR_RESPONSE_XML:
try {
outputWriter.write(ch, start, length);
} catch (IOException e) {
throw new RuntimeException(e);
};
case NOT_READY :
// Do nothing with any characters outside p_xml element
}
}
}
private Map param = null;
private SAXParserFactory parserFactory = null;
public void execute(InputStream inputStream, OutputStream outputStream) throws StreamTransformationException {
setSAXParserFactory();
final String ERR = "Fatal parsing error.";
try {
// Prepare input, output and parser
InputSource inputSrc = new InputSource(new InputStreamReader(inputStream, CHARACTER_ENCODING));
Writer outputWriter = new BufferedWriter(new OutputStreamWriter(outputStream, CHARACTER_ENCODING));
SAXEventHandler saxEventHandler = new SAXEventHandler(outputWriter);
SAXParser parser = parserFactory.newSAXParser();
// Parse XML
parser.parse(inputSrc, saxEventHandler);
outputWriter.close();
} catch (Exception e) {
e.printStackTrace();
throw new StreamTransformationException(ERR, e);
}
}
public void setParameter(Map param) {
this.param = param;
if (param == null) {
this.param = new HashMap();
}
}
private void setSAXParserFactory() throws StreamTransformationException {
final String ERR = "Fatal: Could not create SAXParser factory.";
if (parserFactory == null) {
try {
parserFactory = SAXParserFactory.newInstance();
parserFactory.setNamespaceAware(true);
} catch (FactoryConfigurationError e) {
throw new StreamTransformationException(ERR, e);
}
}
}
}
Of course, a reverse logic can be applied if you need to post a complex structure to your stored procedure. Then the request mapping would have to “escape and wrap” your XML data into an input element in the XML SQL request.
5 Comments
