Wednesday, 12 September 2012

Call Oracle Reports from the Middle Tier And Parse/XPath query the response

In my current project we're replacing Oracle Workflow with SOASuite/BPEL 11g. One of the problems turn out to be Oracle Reports in batch. There is a procedure in the database that queries for all requests for which an Oracle Report has to be run. For each of them it calls another procedure that constructs the URL to the Reports server and invokes it. The response is searched for a status, for an OK status.

Since potentially there can be hundreds or thousands of letters to be printed for requests, this procedure can run for a very long hours. Up to maybe three hours or so. From Oracle Workflow this is no problem at all, since it runs in the database in a background job itself. Normally you have one or more background engines running in dbms_jobs. The OWF processes can be rebuild in BPEL as easily. The batch procedure is then called through the database adapter. But since it can run for hours (potentially) the database-session needs to be open all the time. The inherent synchronous database adapter needs a time out of hours. And even then you might run into a time-out. So for BPEL this is not a good architecture.

There are several options to solve it. We could make the pl/sql service asynchronous, by creating a request and reply queue in Advanced Queueing. Then subscribe and register a pl/sql function that dequeues the message, calls the batch procedure and queues the reply message to the reply queue, using a correlation id. BPEL enqueues on the request queue a message to request for a batch run. And the wait for the correlated reply message. A good explanation on how to set that up is here on Ask Tom.

But I thought it would be nicer to call the Reports right from the middle tier. Then I would need to query the Requests for which to print in one database call, and for each one call the Oracle Report one by one. (Did you know that BPEL 2.0 had a very nice For-loop construction?)

So I needed to be able to call Reports from Java.

My Basic example I found here, is:
import java.net.*;
import java.io.*;

public class URLConnectionReader {
    public static void main(String[] args) throws Exception {
        URL yahoo = new URL("http://www.yahoo.com/");
        URLConnection yc = yahoo.openConnection();
        BufferedReader in = new BufferedReader(
                                new InputStreamReader(
                                yc.getInputStream()));
        String inputLine;

        while ((inputLine = in.readLine()) != null)
            System.out.println(inputLine);
        in.close();
    }
}

My first technical working example was:
   CallReportsService callReportsService = new CallReportsService();
        StringBuffer reportsUrl = new StringBuffer("http://reportsserver.darwin-it.local/reports/rwservlet?outputimageformat=gif&statusformat=xml&server=rep_dev_darwin&");
        reportsUrl.append("report=");
        String reportName = "DWN1234R";
        reportsUrl.append(reportName);
        reportsUrl.append("&P_PREVIEW=");
        String preview = "J";
        reportsUrl.append(preview);
        reportsUrl.append("&P_AVR_ID=");
        String aanvraagID = "7249834";
        reportsUrl.append(aanvraagID);
        reportsUrl.append("&JOBNAME=");
       
       
        String jobName = "DWN1234R_PREVIEW";
        reportsUrl.append(jobName);
                                         
                
        URL reportSvrURL = new URL(reportsUrl.toString());
       
        URLConnection reportSvrConnection = reportSvrURL.openConnection();
        reportSvrConnection.setConnectTimeout(60000);
        BufferedReader in = new BufferedReader(
                                new InputStreamReader(
                                reportSvrConnection.getInputStream()));
        String inputLine;

        while ((inputLine = in.readLine()) != null)
            System.out.println(inputLine);
        in.close();
    }"

Now eventually I want to have this running in a Spring context (had to figure that out too). So I want a Request and a Response bean. Since the response of the Reports server is in XML format, I want to have a ResponseParser. And then of course the service itsself.

The main runReport method of the CallReportsService is as follows:
    /**
     * Run a Report on the Report Server
     * @param repReq
     * @return ReportResponse
     */
    public ReportResponse runReport(ReportRequest repReq) {
        StringBuffer strBuf = new StringBuffer();
        ReportRequest repRequest = repReq;//(ReportRequest)iRepReq;
        
        ReportResponse repResp = null;
        URL reportSvrURL;
        try {
            reportSvrURL = new URL(repRequest.getReportsUrl());

            URLConnection reportSvrConnection = reportSvrURL.openConnection();
            reportSvrConnection.setConnectTimeout(repRequest.getHttpTimeOut());
            BufferedReader in =
                new BufferedReader(new InputStreamReader(reportSvrConnection.getInputStream()));
            String inputLine;
            do {
                inputLine = in.readLine();
                if (inputLine != null) {
                    strBuf.append(inputLine);
                    strBuf.append('\n');
                }
            } while (inputLine != null);
            in.close();
            ReportResponseParser repParser =
                new ReportResponseParser(strBuf.toString());
            repResp = repParser.getReportResponse();
        } catch (MalformedURLException e) {
            if (repResp == null)
                repResp = new ReportResponse();
            repResp.setErrorCode(EC_MALFORMEDURL);
            repResp.setErrorMessage("MalformedURLException: " +
                                    e.getLocalizedMessage());
        } catch (IOException e) {
            if (repResp == null)
                repResp = new ReportResponse();
            repResp.setErrorCode(EC_IO);
            repResp.setErrorMessage("IOException: " + e.getLocalizedMessage());
        }
        return repResp;
    }

It uses a request bean as an input. The request bean contains the method to build up a request url:
    /**
     * Build a URL-String from the paratamers in the request
     */
    public String getReportsUrl() {
        StringBuffer reportsUrl = new StringBuffer(getReportsBaseUrl());
        reportsUrl.append("report=");
        reportsUrl.append(getReportName());
        reportsUrl.append("&userid=");
        reportsUrl.append(getUserId());
        reportsUrl.append("&destination=");
        reportsUrl.append(getDestination());
        reportsUrl.append("&P_PREVIEW=");
        reportsUrl.append(getPreviewJN());
        reportsUrl.append("&P_AVR_ID=");
        reportsUrl.append(getAanvraagID());
        reportsUrl.append("&JOBNAME=");
        reportsUrl.append(getJobName());
        return reportsUrl.toString();
    }
The attribute "reportsBaseUrl" of the bean should contain the base URL to the reports server: "http://reportsserver.darwin-it.local/reports/rwservlet?outputimageformat=gif&statusformat=xml&server=rep_dev_darwin&"

To parse the XML content I created a few helper methods in the ReportResponseParser class. The first one is the actual parser:
        /**
     * Parse the Response XML String
     */
    private void parseRespXmlStr() {
        try {
            InputSource inputSource = getRespXMLAsInputSource();
            if (inputSource != null) {
                DocumentBuilder docBuilder;
                docBuilder = newDocBuilder();
                Document doc;
                doc = docBuilder.parse(inputSource);
                setDoc(doc);
            }
        } catch (ParserConfigurationException e) {
            setErrorMsg("ParserConfigurationException: " +
                        e.getLocalizedMessage());
            setErrorCode(EC_PARSING);
        } catch (SAXException e) {
            setErrorMsg("SAXException: " + e.getLocalizedMessage());
            setErrorCode(EC_PARSING);
        } catch (IOException e) {
            setErrorMsg("IOException: " + e.getLocalizedMessage());
            setErrorCode(EC_PARSING);
        }
    }
It needs an InputStream. So the String object that is the result from the ReportsServer is to be read into an InputSource:
   /**
     * Create an InputSource/InputStream for the response XML String
     * @return
     */
    private InputSource getRespXMLAsInputSource() {
        String respXMLStr = getResponseXMLStr();
        InputSource inputSource = null;
        if (respXMLStr != null) {
            inputSource = new InputSource();
            inputSource.setCharacterStream(new StringReader(respXMLStr));
        }
        return inputSource;
    }

Possibly, since I catch all the lines in the response into a StringBuffer, I could do it from there as well. But these methods I had "on the shelf". Then I need a DocumentBuilder that does the parsing.
    /**
     * Create a new DocumentBuilder
     * @return
     * @throws ParserConfigurationException
     */
    private DocumentBuilder newDocBuilder() throws ParserConfigurationException {
        DocumentBuilderFactory domFactory =
            DocumentBuilderFactory.newInstance();
        domFactory.setNamespaceAware(true);
        DocumentBuilder docBuilder = domFactory.newDocumentBuilder();
        return docBuilder;
    }

To evaluate an xpath expression on the XML Document I need the next method:
    /**
     * Evaluate xpath expression
     *
     * @param xpathExpr the xpath expression
     * @param returnType the return type that is expected.
     * http://www.ibm.com/developerworks/library/x-javaxpathapi.html:
     * XPathConstants.NODESET => node-set maps to an org.w3c.dom.NodeList
     * XPathConstants.BOOLEAN => boolean maps to a java.lang.Boolean
     * XPathConstants.NUMBER => number maps to a java.lang.Double
     * XPathConstants.STRING => string maps to a java.lang.String
     * XPathConstants.NODE
     *
     * @throws XPathExpressionException
     */
    public Object evaluate(String xpathExpr,
                           QName returnType) throws XPathExpressionException {
        XPathFactory factory = XPathFactory.newInstance();
        XPath xpath = factory.newXPath();
        /*XMLNSResolver nsRes = getNsRes();
           if (nsRes != null) {
               xpath.setNamespaceContext(nsRes);
           }*/
        XPathExpression expr = xpath.compile(xpathExpr);
        Document doc = getDoc();
        Object resultObj = expr.evaluate(doc, returnType);
        return resultObj;
    }

Now, the result is an Object. And we expect a NodeList:
    /**
     * Select Nodes using Xpath
     *
     * @param xpath
     * @return NodeList
     * @throws XPathExpressionException
     */
    public NodeList selectNodes(String xpath) throws XPathExpressionException {
        NodeList nl = (NodeList)evaluate(xpath, XPathConstants.NODESET);
        return nl;
    }

The queries I want to perform on the response are basically single strings. So the next method just gets the simple text content of the queried node:
    /**
     * Get the text content of a node.
     * @param xpath
     * @return
     */
    public String getXpathTextContent(String xpath) {
        String result = null;
        try {
            NodeList nl = selectNodes(xpath);
            if (nl.getLength() > 0) {
                result = nl.item(0).getNodeValue();
            }
        } catch (XPathExpressionException e) {
            setErrorMsg("XPathExpressionException: " +
                        e.getLocalizedMessage());
            errorCode = EC_XPATH;
            setErrorCode(errorCode);
        }
        return result;
    }

Then you can querie all the relevant elements as follows:
    /**
     * Get the Error Code from the Reponse
     * @return
     */
    public String getRespErrorCode() {
        String errorCode = getXpathTextContent("//serverQueues/error/@code");
        return errorCode;
    }

    /**
     * Get the Error Message from the Reponse
     * @return
     */
    public String getRespErrorMsg() {
        String errorMsg = getXpathTextContent("//serverQueues/error/@message");
        return errorMsg;
    }
    /**
     * Get the job Id from the Reponse
     * @return
     */
    public String getRespJobId() {
        String errorMsg = getXpathTextContent("//serverQueues/job/@id");
        return errorMsg;
    }
    /**
     * Get the job status from the Reponse
     * @return
     */
    public String getRespJobStatus() {
        String errorMsg = getXpathTextContent("//serverQueues/job/status");
        return errorMsg;
    }  
    /**
     * Get the job status Codefrom the Reponse
     * @return
     */
    public String getRespJobStatusCode() {
        String errorMsg = getXpathTextContent("//serverQueues/job/status/@code");
        return errorMsg;
    }
    /**
     * Parse the responseXML into  a ReportResponse bean
     * @return
     */
    ReportResponse getReportResponse() {
        ReportResponse repResp = new ReportResponse();
        repResp.setErrorCode(this.getRespErrorCode());
        repResp.setErrorMessage(this.getRespErrorMsg());
        repResp.setResponseXml(this.getResponseXMLStr());
        repResp.setJobId(this.getRespJobId());
        repResp.setJobStatus(this.getRespJobStatus());
        repResp.setJobStatusCode(this.getRespJobStatusCode());
        return repResp;
    }

In the end it is pretty simple. Most of the code is to be able to call the service with a nice Request bean and  get a response bean back. You could of course get the fields you want using string-manipulation methods. But I found the xml-parsing a more neat method. If you need more info from the response it is easy to query them. Tip: use the xpath-search (from the Search menu) tool from JDeveloper to test your xpath queries.
A tipical Reports response xml is:

<?xml version = '1.0' encoding = 'UTF-8' standalone = 'yes'?>
<serverQueues>
   <job id="5159" queueType="past">
      <name>DWN1234R_PREVIEW</name>
      <type>report</type>
      <status code="4">Rapport  is voltooid.</status>
      <owner>RWUser</owner>
      <server>rep_dev_darwin</server>
      <destination>
         <desType>unknown</desType>
         <desFormat>dflt</desFormat>
      </destination>
      <timingInfo>
         <queued>11-sep-2012 12:45:14</queued>
         <started>11-sep-2012 12:45:14</started>
         <finished>11-sep-2012 12:45:14</finished>
      </timingInfo>
   </job>
</serverQueues>

I put my classes in a SOA project. Because the next step is to create a spring context for them. You can download the project here.

No comments: