Issue with DBAdapter Update due to char datatype & shouldTrimStrings property

Recently I looked into a technical issue with DB adapter where the update was not working even though it was not throwing any error. Everything seems to be working fine but the field is never updated.

All the settings looked fine but the update was not happening. So we checked the table which revealed that the all the string fields were of char type. It didn’t seem to value much as my understanding was that char will work similar to varchar. I was puzzled to see the Note from Oracle @Oracle DBAdapter documentation ,but it was true.

It reads as :: Oracle recommends that you use varchar instead of char for primary key columns, otherwise you must set the weblogic-ra.xml property shouldTrimStrings to false. The truncation of trailing spaces could cause the primary key to be read incorrectly, making it impossible to update read rows as processed.

Issue :: The Primary Key fields in Database were of char type and data contains trailing spaces. Since by default shouldTrimStrings property is set to true, weblogic will trim these spaces, there by it will not be able to fetch the correct rows from DB. So update fails as it will not be able to fetch the row properly.

Solution::

Set the shouldTrimStrings property to false in weblogic DBAdapter Outbound Connection Properties

By default the property shouldTrimStrings is not visible in Outbound Connection Properties, so we will not able to change it.

So to modify the value a new entry needs to added to the DBAdapter config  properties. This is one time configuration task that needs to done by updating the ra.xml from filesystem.

Steps

1.    Take a backup of DBAdapter.rar

2.     Extract the entire content.(use unzip tools)

3.     Edit the META-INF/ra.xml.

Add a property in ra.xml file

           <config-property>

               <config-property-name>shouldTrimStrings</config-property-name>

               <config-property-type>java.lang.Boolean</config-property-type>

               <config-property-value>false</config-property-value>

            </config-property>

   

4.    Re-Create the DbAdapter  rar file.

>>>>jar.exe -cvf DbAdapter.rar -C DbAdapter/ .

added manifest

adding: DBAdapter.jar(in = 551405) (out= 519781)(deflated 5%)

ignoring entry META-INF/

ignoring entry META-INF/MANIFEST.MF

adding: META-INF/ra.xml(in = 13473) (out= 2866)(deflated 78%)

adding: META-INF/weblogic-ra.xml(in = 6713) (out= 1775)(deflated 73%)

adding: toplink-grid.jar(in = 102529) (out= 91349)(deflated 10%)

5.    Update the DBAdapter from Deployments

From Console the property should be available for all the database adapter connection under Outbound Connection Properties.

Thanks to Thani, Lee, Sampath for helping with this issue.

Java utility to create/populate a DVM from XL sheet -SOA 11g

Today I am posting the java code which reads in a input XL with the dvm data and creates a dvm file. I had created a utility in 10g , now just modified it for 11g.This is very useful in scenarios where there is huge amount of data to be inserted into DVM before the interface can start working. Even though we have soa composer and JDev to enter the recods into DVM. manual entry of large number of DVM data is not a feasible solution For running the java program pass the 2 arguments
for eg: -inputXL=D:\Projects\DVMTest\Sites.xls
-outputDVM=D:\Projects\DVMTest\SiteTest.dvm
This will work for any number of columns.The first row in the XL sheet represents the column name that needs to appear in the DVM. If decimal points are not required modify the code to replace the decimal value. Hope this helps.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


/**
 * This class has methods which will read the excel file and convert it into a
 * a DVM file.
 *
 *@author George Thomas
 *@version 1.0
 *
 */
public class DVMCreator {
    private final static Logger logger =
        Logger.getLogger(DVMCreator.class.getName());

    public DVMCreator() {
        super();
    }


    private String inputFile;

    /**
     * @param inputFile
     */
    public void setInputFile(String inputFile) {
        this.inputFile = inputFile;
    }


    /**
     * This method listens for incoming records and handles them as required.
     *
     * @return sheetData
     * @throws Exception
     */
    public List getSheetData() throws Exception {
        //
        // Create an ArrayList to store the data read from excel sheet.
        //
        List sheetData = new ArrayList();

        FileInputStream fis = null;
        try {
            //
            // Create a FileInputStream that will be use to read the
            // excel file.
            //
            fis = new FileInputStream(inputFile);

            //
            // Create an excel workbook from the file system.
            //
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            //
            // Get the first sheet on the workbook.
            //
            HSSFSheet sheet = workbook.getSheetAt(0);

            //
            // When we have a sheet object in hand we can iterator on
            // each sheet's rows and on each row's cells. We store the
            // data read on an ArrayList so that we can printed the
            // content of the excel to the console.
            //
            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow)rows.next();
                Iterator cells = row.cellIterator();
                List data = new ArrayList();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell)cells.next();
                    data.add(cell);
                }

                sheetData.add(data);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                fis.close();
            }
            showExcelData(sheetData);
            // processExelData(sheetData);
            return sheetData;
        }
    }


     private static String convertExcelToDVMData(List sheetData,String dvmName ) {
        //
        // Iterates the data and print it out to the console.
        //
        StringBuffer xmlData = new StringBuffer();
      
      dvmName = dvmName.replaceAll(".dvm", "");

        
        Map map = new HashMap();
        xmlData.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
        xmlData.append("<dvm name=\"");
        xmlData.append(dvmName);
        xmlData.append("\"");
        xmlData.append(" xmlns=\"http://xmlns.oracle.com/dvm\">");
        
        xmlData.append("<description></description>");
        xmlData.append("<columns>");


        List columnNamelist = (List)sheetData.get(0);
        for (int j = 0; j < columnNamelist.size(); j++) {
            xmlData.append("<column name=\"");
            HSSFCell cell = (HSSFCell)columnNamelist.get(j);
            if (cell.getCellType() == 0) {
                logger.log(Level.INFO, " " + cell.getNumericCellValue());
                xmlData.append(cell.getNumericCellValue());

            } else if (cell.getCellType() == 1) {
                logger.log(Level.INFO,
                           "S " + cell.getRichStringCellValue().getString());
                xmlData.append(cell.getRichStringCellValue().getString());
            }
            xmlData.append("\"/>");

        }
        logger.log(Level.INFO, "");
        xmlData.append("</columns>");


        xmlData.append("<rows>");

        for (int i = 1; i < sheetData.size(); i++) {
            xmlData.append("<row>");
            List list = (List)sheetData.get(i);
            for (int j = 0; j < list.size(); j++) {
                xmlData.append("<cell>");
                HSSFCell cell = (HSSFCell)list.get(j);
                if (cell.getCellType() == 0) {
                    logger.log(Level.INFO, " " + cell.getNumericCellValue());
                    xmlData.append(cell.getNumericCellValue());

                } else if (cell.getCellType() == 1) {
                    logger.log(Level.INFO,
                               "S " + cell.getRichStringCellValue().getString());
                    xmlData.append(cell.getRichStringCellValue().getString());
                }
                xmlData.append("</cell>");
                if (j < list.size() - 1) {
                    logger.log(Level.INFO, ", ");
                }
            }
            logger.log(Level.INFO, "");
            xmlData.append("</row>");
        }
        xmlData.append("</rows>");
        xmlData.append("</dvm>");

        return xmlData.toString();
    }

  



    

  public static boolean writeDVM(String xmlData, File file) throws Exception {

       
      try {
          FileOutputStream fOut = new FileOutputStream(file);
          fOut.write(xmlData.getBytes());
          fOut.flush();
          fOut.close();
          logger.log(Level.INFO, "File Created .::" + file.getName());

      } catch (Exception e) {
          logger.log(Level.INFO,
                     "!!BOOOM!! xlCreate() failed : " + e.getMessage());
          throw e;
      }
      return true;
  }
  
  
    /**
     * main method
     *
     * @param args      Expect one argument that is the file to read.
     * @throws IOException  When there is an error processing the file.
     */
    public static void main(String[] args) throws IOException {
        DVMCreator reader = new DVMCreator ();
        String spliter[] = null;
        String inputXL = null;
        String outputDVM = null;
        if (args.length == 0 || args.length == 1) {
            logger.log(Level.INFO, " USAGE:- Enter the input excel file \n" +
                    "     -inputXL  and enter the output DVM file location and name -outputDVM \n");
        } else if (args[0].equals("-help")) {
            logger.log(Level.INFO, "USAGE:- Enter the input excel file \n" +
                    "     -inputXL  and enter the output DVM file location and name -outputDVM \n");
        } else {
            if (args[0].contains("-inputXL") &&
                args[1].contains("-outputDVM")) {
                spliter = args[0].split("=");
                inputXL = spliter[1];
                reader.setInputFile(inputXL);
                spliter = args[1].split("=");
                outputDVM = spliter[1];
                File file = null;
              try {
                   file = new File(outputDVM);
                  logger.log(Level.INFO, "File Created .::" + outputDVM);

              } catch (Exception e) {
                  logger.log(Level.INFO,
                             "!!BOOOM!! xlCreate() failed : " + e.getMessage());
                
              }

                try {
                    List data = reader.getSheetData();
                    
                    String xmlData = convertExcelToDVMData(data,file.getName());
                    boolean flag = writeDVM(xmlData, file);
                    logger.log(Level.INFO, "XML data ::" + xmlData);


                } catch (Exception e) {
                    logger.log(Level.INFO, "Error occured" + e.getMessage());
                    e.printStackTrace();
                }
                logger.log(Level.INFO, "DVM  write done.");

            }


        }
    }
}

How to avoid Selection Failure in Assign activity

One of the queries I got recently was on how to handle selection failures in assign activity if the tags are not present as part of the payload.

To show case how it works I will just do a small sample program which assigns input variable to output variable. I am using PS5(11.1.1.6) environment and BPEL2.0 as specification.

I am using BPEL 2.0 spec, but the solution works in BPEL 1.1 also.

Example:-  I use a simple assign activity to assign 2 elements of input to output.

<assign name="Assign1" xml:id="id_11">             <copy xml:id="id_16">                 <from xml:id="id_17">$inputVariable.payload/client:input1</from>                 <to xml:id="id_18">$outputVariable.payload/client:result1</to>             </copy>             <copy xml:id="id_19" ignoreMissingFromData="no">                 <from xml:id="id_20">$inputVariable.payload/client:input2</from>                 <to xml:id="id_21">$outputVariable.payload/client:result2</to>             </copy>         </assign>

For testing this I pass only one element in payload. The element2 tag is not present in the payload.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bpel="http://xmlns.oracle.com/BPMLearning/Project1/BPELProcess1">    <soapenv:Header/>    <soapenv:Body>       <bpel:process>          <bpel:input1>hello</bpel:input1>         </bpel:process>    </soapenv:Body> </soapenv:Envelope>

In runtime it throws following selection failure error

Exception stack trace:

Exception is thrown because the from-spec at line 61 is evaluated to be empty -<details> -<from-spec> <from xml:id="id_20" xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable"> $inputVariable.payload/client:input2 </from> </from-spec> -<variable> <message> inputVariable </message> </variable> -<fault> -<bpelFault> <faultType> 0 </faultType> <selectionFailure xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable"/> </bpelFault> </fault> </details>

Solution

 To fix this there is an attribute ignoreMissingFromData

The ignoreMissingFromData attribute suppresses any bpel:selectionFailure standard faults.

BPEL 1.1  syntax	BPEL 2.0 syntax
<copy bpelx:ignoreMissingFromData="yes|no"/>	<copy ignoreMissingFromData="yes|no"/>

 

<assign name="Assign1" xml:id="id_11">             <copy xml:id="id_16">                 <from xml:id="id_17">$inputVariable.payload/client:input1</from>                 <to xml:id="id_18">$outputVariable.payload/client:result1</to>             </copy>             <copy xml:id="id_19" ignoreMissingFromData="yes">                 <from xml:id="id_20">$inputVariable.payload/client:input2</from>                 <to xml:id="id_21">$outputVariable.payload/client:result2</to>             </copy>         </assign>

The error gets suppressed since it ignores the element as the ignoreMissingFromData flag is set to yes.

BPEL instance execution stacktrace

Copy rule at line 64 is ignored because the evaluated from value is empty <payload>

<details> <from-spec> <from  xml:id="id_23">$inputVariable.payload/client:input2</from> </from-spec> <variable> <message>inputVariable</message> </variable> </details> That’s it .It’s an easy solution to selectionFailure instead of  using the count nodes function and taking headaches in  doing the mapping. References: SOA developers guide

BPM11g-Getting started with BPMHelloWorld process-issues/workarounds

It has been some time that I got queries on how to get started on BPM .The best way to start on any technology or product stack is to do it the HelloWorld way. Always start with a hello world example.

I am using 11.1.1.1.6 environment for the examples.

There is a good step by step guide of getting started in BPM  @ link

To start up with you can just do the small part of getting the hello message /greeting and writing it to a file . In Jdev it will look like this.

 

The Request Hello in turn is implemented using a Human work flow activity, which receives in a HelloMessage and makes a service call which in turn calls a file adapter to write to a file.

Two activities between Start and End in BPM layer. The implementations of these 2 activities are done in SOA layer using Human work Flow and call to a file adapter.

I will list down some points/issues you may face doing this example

•       The JDev Role matters, In case you select the BPM Role for Jdeveloper development, the implementation tab will be missing from your activities when you drill down. So if you want to the implementation in SOA then the Role must be default which gives access to both BPM and SOA together.

•            During your development the main tab that yo will be using is the BPMProjectNavigator which will list the different components that will be part of your process.

•        There are some chances the auto-create Task form may run into issues. So create the Task form again and delete previous UI project associated with the BPM process. 
•            Once you compile/deploy BPM process, you will need to login to BPM workspace to view and initiate the process. The common issue is that The BPM process is not visible in th BPM workspace.  The User roles become pretty important in BPM. By default the Role of the Hello world activities will be tied to Role. You can change it to Requester or other role based on your process design.

These roles will now need to be mapped to users.

In case the process doesn’t show up you will have to check or create /modify users for roles.

The below screenshots show the configurations that need to be done on User roles in jdeveloper, EM and BPM workspace.

In jdeveloper

a)    Add the users to the roles used in the process/project.

In Oracle Enterprise Manager

a)    Expand the domain farm  and select  Application Deployments

b)    Select the deployed Application .Go to Security >Application Roles

c)    Add the <project name>.<rolename> and Users to the roles who can access the role.

In oracle BPM workspace

a)    Go to Administration tab >Roles

b)    Add the user to  <composite name>.<role name> members.

•   In BPM workspace when you click on process to initiate the process for entering the Hello Message. The UI page throws an error .Page not found exception.

Error:  OracleJSP error: java.io.FileNotFoundException: Set the init-param debug_mode to "true" to see the complete exception message.

         The error is because adfc-config.xml refers to Empty1.jspx file.

    Create a empty jspx file and name it as Empty1.jspx , add below content in the file.

<?xml version='1.0' encoding='windows-1252'?>

<jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1"

xmlns:f="http://java.sun.com/jsf/core"

xmlns:h="http://java.sun.com/jsf/html"

xmlns:af="http://xmlns.oracle.com/adf/faces/rich">

<jsp:directive.page contentType="text/html;charset=windows-1252"/>

<f:view>

<af:document id="d1">

<af:form id="f1"></af:form>

</af:document>

</f:view>

</jsp:root>

Once you add the file and redeploy the process the error will not recur.

Then you can enter all the hello message and submit the values, which in turn will make a service call to write a message to the file as specified in file adapter.

This is all some points I wanted to share with the newbies starting on BPM 11g

Hav a gr8 day ahead J