This discussion is archived
8 Replies Latest reply: Dec 11, 2012 7:59 AM by Venkatesh Ramasamy RSS

How to Implement B2B Callout with out XSLT - Java code using Apache POI

Venkatesh Ramasamy Newbie
Currently Being Moderated
Hello,

I have a requirement where the B2B picks up an Excel file from SFTP server and process in BEPL to Invoke a web service.

I am planning to convert the Excel file to XML using a Java callout at the B2B layer. I have written a Java code using Apache POI classes. The java code does the transform and there is no need of an XSLT. The default Callout "XSLTCalloitImpl.java", uses " String xsltFile = context.getStringProperty("xsltFile");" where the xsltFile is specified in the B2B Callout configuration on B2B console. My code below does not have that. I am not sure how to implement the callout with the below code, Is it possible with out XSLT? Or am I completely in a wrong direction?

I have tested this code outside SOA with an Excel File as a file input and got the expected XML output.

Any Ideas and suggestion are greatly appreciated.

Here is my Java code
package xmlexcel;

import java.io.IOException;
import java.io.InputStream;

import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import oracle.tip.b2b.callout.Callout;
import oracle.tip.b2b.callout.CalloutContext;
import oracle.tip.b2b.callout.CalloutMessage;
import oracle.tip.b2b.callout.exception.CalloutDomainException;
import oracle.tip.b2b.callout.exception.CalloutSystemException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;


public class excelxml implements Callout {


    // public void generateXML(File excelFile) throws InvalidFormatException {

    public void execute(CalloutContext context, List input,
                        List output) throws CalloutDomainException,
                                            CalloutSystemException
                                             {
        try {
            // Get the input callout message
            CalloutMessage cmIn = (CalloutMessage)input.get(0);
            //String inputPayload = cmIn.getBodyAsString();
            InputStream inputPayload = cmIn.getBodyAsInputStream();

            DocumentBuilderFactory factory =
                DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = factory.newDocumentBuilder();
            Document document = builder.newDocument();
            Element orderdetailElement = document.createElement("OrderDetail");
            document.appendChild(orderdetailElement);

            Workbook workbook = WorkbookFactory.create(inputPayload);
            // Workbook workbook = WorkbookFactory.create(excelFile);
            Sheet spreadsheet =
                workbook.getSheetAt(0); // sheet can be used as common for XSSF (xlsx) and HSSF (xls) WorkBook
            for (int i = 1; i <= spreadsheet.getLastRowNum();
                 i++) // Rows in the Sheet

            {
                Row row = spreadsheet.getRow(i);
                Element orderElement =
                    document.createElement("Order"); //Root Element
                orderdetailElement.appendChild(orderElement);
                //Start All columns
                Element fcorderdetailElement =
                    document.createElement("OrderDetail"); //B2B Identifier, can be removed if decided as not required
                orderElement.appendChild(fcorderdetailElement);
                fcorderdetailElement.appendChild(document.createTextNode(""));

                Element vendoridElement = document.createElement("VendorID");
                orderElement.appendChild(vendoridElement);
                vendoridElement.appendChild(document.createTextNode(row.getCell(1).getRichStringCellValue().getString()));

                Element ordernumberElement =
                    document.createElement("PurchaseOrderNumber");
                orderElement.appendChild(ordernumberElement);
                ordernumberElement.appendChild(document.createTextNode(row.getCell(2).getRichStringCellValue().getString()));

                Element invoicenumberElement =
                    document.createElement("InvoiceNumber");
                orderElement.appendChild(invoicenumberElement);
                invoicenumberElement.appendChild(document.createTextNode(row.getCell(5).getRichStringCellValue().getString()));

                Element invoiceamountElement =
                    document.createElement("InvoiceAmount");
                orderElement.appendChild(invoiceamountElement);
                invoiceamountElement.appendChild(document.createTextNode(row.getCell(6).getRichStringCellValue().getString()));
                //End All columns
            }


            //Transform the document
            TransformerFactory tFactory = TransformerFactory.newInstance();

            Transformer transformer = tFactory.newTransformer();

            DOMSource source = new DOMSource(document);
            //StreamResult result = new StreamResult(new File("orderdetail2.xml"));
            StreamResult result = new StreamResult();
            transformer.transform(source, result);
            //Create Callout output Message
            CalloutMessage cmOut = new CalloutMessage(result.toString());
            output.add(cmOut);

        } catch (IOException e) {
            System.err.println(e.getMessage());
        } catch (ParserConfigurationException e) {
            System.err.println(e.getMessage());

        } catch (TransformerConfigurationException e) {
            System.err.println(e.getMessage());

        } catch (TransformerException e) {
            System.err.println(e.getMessage());

        } catch (InvalidFormatException e) {
          System.err.println(e.getMessage());
        }
    }


    /* public static void main(String[] argv) throws InvalidFormatException {

        excelxml excel = new excelxml();
          File input = new File("c:/orderdetail.xls");
          excel.generateXML(input);
       }*/


}
Thanks,
Venkatesh
  • 1. Re: How to Implement B2B Callout with out XSLT - Java code using Apache POI
    Anuj Dwivedi Guru
    Currently Being Moderated
    Hi Venkatesh,

    It is definitely possible to transform messages without XSLT. You may use your code but make sure that all the classes which you are using in your callout are available in the server classpath. If possible pack all the classes in the same callout jar otherwise place all required jars/classes in directory $MW_Home\Oracle_SOA1\soa\modules\oracle.soa.ext_11.1.1

    The readme file in the above mentioned directory explains that how to add classes/jars in the classpath by using this directory.

    Regards,
    Anuj
  • 2. Re: How to Implement B2B Callout with out XSLT - Java code using Apache POI
    Venkatesh Ramasamy Newbie
    Currently Being Moderated
    Thanks,

    I was able to implement the callout. I have another question, when using CalloutMessage what would be the data type of the CalloutMessage. when I create a WorkBook using apache POI's workbookFactory class, all I am getting is a B2B Runtime error. I have tried using getBodyAsString, getBodyAsInputStream.

    Any Ideas or suggestions are greatly appreciated.

    Thanks,
    Venkatesh

    Edited by: Venkatesh Ramasamy on Dec 6, 2012 1:20 PM

    Edited by: Venkatesh Ramasamy on Dec 6, 2012 1:30 PM
  • 3. Re: How to Implement B2B Callout with out XSLT - Java code using Apache POI
    Anuj Dwivedi Guru
    Currently Being Moderated
    Venkatesh,

    Though I did not understand your question clearly, it seems you are asking about which method you should use to retrieve the payload from callout message. Given that WorkbookFactory can create a workbook using an input stream, you should use getBodyAsInputStream() method to get the body of payload from callout message.

    B2B java API is here -

    http://docs.oracle.com/cd/E23943_01/apirefs.1111/e18157/toc.htm

    and WorkbookFactory API is here -

    http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/WorkbookFactory.html

    Regards,
    Anuj
  • 4. Re: How to Implement B2B Callout with out XSLT - Java code using Apache POI
    Venkatesh Ramasamy Newbie
    Currently Being Moderated
    Thanks Anuj,

    Yes, I am using getBodyAsInputStream() to get the body of the payload, however, when passing the inputstream to the WorkbookFactory, the code errors out and does not throw any exception other than "B2B Run Time Error". I tried adding a try catch to catch the exception, still no exceptions were thrown. I am kind of confused as to what type of data that is coming in as payload and what is it getting in as Input Stream.

    The Input file to B2B is a .xls (Excel) file.

    Below is my code,
                CalloutMessage cmIn = (CalloutMessage)input.get(0);
    
                b2blog((new StringBuilder()).append("Callout execute() - All Parameter = ").append(cmIn.getParameters()).toString());
    
                b2blog((new StringBuilder()).append("Callout execute() - GetType = ").append(cmIn.getType()));
    
                InputStream inputPayload1 = cmIn.getBodyAsInputStream();
    
                b2blog((new StringBuilder()).append("Callout execute() - inputPayload = ").append(inputPayload1));
    
                Workbook workbook = null;
                try {
                    workbook = WorkbookFactory.create(inputPayload1);
                } catch (Exception ex) {
                    b2blog((new StringBuilder()).append("Callout execute() - Exception = ").append((ex.getMessage())));
                    System.err.println(ex.getMessage());
                }
    
                b2blog((new StringBuilder()).append("Callout execute() - Workbook Created"));
                Sheet spreadsheet =
                    workbook.getSheetAt(0); // sheet can be used as common for XSSF (xlsx) and HSSF (xls) WorkBook
    Here is the Log Message,

    ####<Dec 7, 2012 11:05:07 AM CST> <Error> <oracle.soa.b2b.engine> <OTC-IT5502458> <AdminServer> <[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <54ec404c68bbfb80:1cb5427c:13b75fef56d:-8000-000000000000010b> <1354899907783> <BEA-000000> <excelxml : Callout execute() called - Start>

    ####<Dec 7, 2012 11:05:07 AM CST> <Error> <oracle.soa.b2b.engine> <OTC-IT5502458> <AdminServer> <[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <54ec404c68bbfb80:1cb5427c:13b75fef56d:-8000-000000000000010b> <1354899907783> <BEA-000000> <excelxml :Callout execute() - All Parameter = {filename=JoyCarpets_JoyCarpetsDailySummary_v1.0_12333333175.xls, callout.class=com.oriental.callouts.excelxml, callout.param.fName=DailySummary, callout.timeout=30, MSG_RECEIVED_TIME=Fri Dec 07 11:05:07 CST 2012, callout.library=Callout.jar, file_ext=.xls, timestamp=Fri Dec 07 11:05:05 CST 2012, callout.name=TranslateExcel, fullpath=/Dev/Local/VEN-Dropship/JoyCarpets/JoyCarpets_JoyCarpetsDailySummary_v1.0_12333333175.xls, filename_format=%FROM_PARTY%_%DOCTYPE_NAME%_%DOCTYPE_REVISION%_%TIMESTAMP%.xls, preserve_filename=true, filesize=15360}>

    ####<Dec 7, 2012 11:05:07 AM CST> <Error> <oracle.soa.b2b.engine> <OTC-IT5502458> <AdminServer> <[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <54ec404c68bbfb80:1cb5427c:13b75fef56d:-8000-000000000000010b> <1354899907783> <BEA-000000> <excelxml :Callout execute() - GetType = 100>

    ####<Dec 7, 2012 11:05:07 AM CST> <Error> <oracle.soa.b2b.engine> <OTC-IT5502458> <AdminServer> <[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <54ec404c68bbfb80:1cb5427c:13b75fef56d:-8000-000000000000010b> <1354899907783> <BEA-000000> <excelxml :Callout execute() - inputPayload = java.io.ByteArrayInputStream@1a1edde>

    ####<Dec 7, 2012 11:05:08 AM CST> <Error> <oracle.soa.b2b.engine> <OTC-IT5502458> <AdminServer> <weblogic.work.j2ee.J2EEWorkManager$WorkWithListener@1607dc7> <<anonymous>> <>
    <54ec404c68bbfb80:1cb5427c:13b75fef56d:-8000-00000000000000eb> <1354899908252> <BEA-000000> <Error -: B2B-50029: B2B runtime error
    Error -: B2B-50029: B2B runtime error
         at oracle.tip.b2b.callout.B2BCalloutHandler.invokeTransportCallout(B2BCalloutHandler.java:726)
         at oracle.tip.b2b.callout.B2BCalloutHandler.handleTransportCallout(B2BCalloutHandler.java:510)
         at oracle.tip.b2b.transport.InterfaceListener.processCallout(InterfaceListener.java:910)
         at oracle.tip.b2b.transport.InterfaceListener.onMessage(InterfaceListener.java:239)
         at oracle.tip.b2b.transport.basic.sftp.SFTPProcessor.processMessage(SFTPProcessor.java:197)
         at oracle.tip.b2b.transport.basic.sftp.SFTPMonitor.processMessage(SFTPMonitor.java:428)
         at oracle.tip.b2b.transport.basic.sftp.SFTPMonitor.run(SFTPMonitor.java:348)
         at oracle.tip.b2b.transport.basic.SFTPFileReceiver.init(SFTPFileReceiver.java:156)
         at oracle.tip.b2b.transport.b2b.B2BTransport.init(B2BTransport.java:577)
         at oracle.tip.b2b.engine.ThreadWorkExecutor.run(ThreadWorkExecutor.java:238)
         at oracle.integration.platform.blocks.executor.WorkManagerExecutor$1.run(WorkManagerExecutor.java:120)
         at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:183)
         at weblogic.work.DaemonWorkThread.run(DaemonWorkThread.java:30)
    >
  • 5. Re: How to Implement B2B Callout with out XSLT - Java code using Apache POI
    Anuj Dwivedi Guru
    Currently Being Moderated
    oracle.tip.b2b.callout.B2BCalloutHandler.invokeTransportCallout(B2BCalloutHandler.java:726)
    Instead of configuring the callout in the listening channel, please configure the same in the inbound agreement and test again. Please refer section "13.3 Including a Callout in an Agreement" at -

    http://docs.oracle.com/cd/E23943_01/user.1111/e10229/callouts.htm#CHDEFBDG

    Regards,
    Anuj
  • 6. Re: How to Implement B2B Callout with out XSLT - Java code using Apache POI
    Venkatesh Ramasamy Newbie
    Currently Being Moderated
    Hi Anuj,

    After configuring the callout in the Agreement, I no longer received the B2B Run time error, however, the code did not go past getting the Body as InputStream, no errors or no exceptions, seems to still fail in the WorkbookFactory. Below are the code and the log messages,
            try {
                CalloutMessage cmIn = (CalloutMessage)input.get(0);
    
                InputStream inputPayload = cmIn.getBodyAsInputStream();
                b2blog((new StringBuilder()).append("Callout execute() - Input Payload = ").append((inputPayload)));
    
                Workbook workbook = null;
                try {
                    workbook = WorkbookFactory.create(inputPayload);
                } catch (Exception ex) {
                    b2blog((new StringBuilder()).append("Callout execute() - Exception = ").append((ex.getMessage())));
                    System.err.println(ex.getMessage());
                }
    
                b2blog((new StringBuilder()).append("Callout execute() - Workbook Created"));
                /*Sheet spreadsheet =
                    workbook.getSheetAt(0); // sheet can be used as common for XSSF (xlsx) and HSSF (xls) WorkBook*/
                //b2blog((new StringBuilder()).append("Callout execute() - Got handle to the Sheet"));
                CalloutMessage cmOut = new CalloutMessage(inputPayload.toString());
                output.add(cmOut);
                b2blog((new StringBuilder()).append("Callout execute() - CalloutMessage Out = ").append(cmOut));
                b2blog((new StringBuilder()).append("Callout execute() - CalloutMessage Out To String = ").append(cmOut).toString());
                b2blog((new StringBuilder()).append("Callout execute() - End Callout"));
    
            } catch (Exception e) {
                b2blog((new StringBuilder()).append("Callout execute() - CalloutMessage Out To String = ").append((e.getMessage())));
                System.err.println(e.getMessage());
            }
    Log:

    ####<Dec 7, 2012 1:54:32 PM CST> <Error> <oracle.soa.b2b.engine> <OTC-IT5502458> <AdminServer> <[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <54ec404c68bbfb80:-4cb53fd4:13b76ea5c93:-8000-0000000000000092> <1354910072330> <BEA-000000> <excelxml : Callout execute() called - Start>
    ####<Dec 7, 2012 1:54:32 PM CST> <Error> <oracle.soa.b2b.engine> <OTC-IT5502458> <AdminServer> <[ACTIVE] ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <54ec404c68bbfb80:-4cb53fd4:13b76ea5c93:-8000-0000000000000092> <1354910072330> <BEA-000000> <excelxml :Callout execute() - Input Payload = java.io.ByteArrayInputStream@13aec3e>

    Four minutes later,

    I found this in the log, not sure if this is related,

    ####<Dec 7, 2012 1:58:44 PM CST> <Warning> <oracle.adfinternal.view.faces.renderkit.rich.SimpleSelectOneRenderer> <OTC-IT5502458> <AdminServer> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <weblogic> <> <54ec404c68bbfb80:-4cb53fd4:13b76ea5c93:-8000-0000000000000220> <1354910324848> <BEA-000000> <Could not find selected item matching value "" in RichSelectOneChoice[UIXEditableFacesBeanImpl, id=value20]>
    ####<Dec 7, 2012 1:58:44 PM CST> <Warning> <oracle.adfinternal.view.faces.renderkit.rich.SimpleSelectOneRenderer> <OTC-IT5502458> <AdminServer> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <weblogic> <> <54ec404c68bbfb80:-4cb53fd4:13b76ea5c93:-8000-0000000000000220> <1354910324848> <BEA-000000> <Could not find selected item matching value "" in RichSelectOneChoice[UIXEditableFacesBeanImpl, id=value30]>

    Edited by: Venkatesh Ramasamy on Dec 7, 2012 2:04 PM

    Edited by: Venkatesh Ramasamy on Dec 7, 2012 2:35 PM
  • 7. Re: How to Implement B2B Callout with out XSLT - Java code using Apache POI
    Anuj Dwivedi Guru
    Currently Being Moderated
    Hi Venkatesh,
    After configuring the callout in the Agreement, I no longer received the B2B Run time error, however, the code did not go past getting the Body as InputStream
    Do you see application message getting created for your inbound message? If yes, what payload it has? I would suggest you to open a SR with Oracle Support. You may also consider forwarding your artifacts (callout jar and source code, B2B export and sample payload for test) to my id. In my spare time, I will look into this.
    Four minutes later,
    I found this in the log, not sure if this is related
    No. It is not related to B2B.

    Regards,
    Anuj
  • 8. Re: How to Implement B2B Callout with out XSLT - Java code using Apache POI
    Venkatesh Ramasamy Newbie
    Currently Being Moderated
    Hi Anuj,

    I had all the Apache POI jars and my callout Jar under "CalloutLib" directory. By moving the apache poi class to the $DOMAIN_DIR/lib directory, everything is working now. I appreciate your help and support.

    Thanks,
    Venkatesh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points