What do I need to close to free memory ?

[YannickL]

Hi.

I use poi to read Excel files in a task in Bonita BPM.
When I run this task more than 3 times, it won't work again and I see this error in the log :
org.bonitasoft.engine.connector.exception.SConnect orException: java.util.concurrent.ExecutionException: java.lang.ExceptionInInitializerError

I think it happens because I try to read multiple time in the same file.
I probably need to free memory by closing something in my program but I don't know what.

Here is my code :

public static Workbook openWorkbook(String nomFichier, String password) {
        try {
            FileInputStream file = new FileInputStream(new File(nomFichier));
            BufferedInputStream bufferInput = new BufferedInputStream(file);
            file.close();
            if (nomFichier.endsWith("xls")) {
                POIFSFileSystem pfs = new POIFSFileSystem(bufferInput);
                Biff8EncryptionKey.setCurrentUserPassword(password);
                bufferInput.close();
                return new HSSFWorkbook(pfs);
            }
            if (nomFichier.endsWith("xlsx")) {
                POIFSFileSystem pfs = new POIFSFileSystem(bufferInput);
                EncryptionInfo encInfo = new EncryptionInfo(pfs);
                Decryptor decryptor = Decryptor.getInstance(encInfo);
                decryptor.verifyPassword(password);
                bufferInput.close();
                return new XSSFWorkbook(decryptor.getDataStream(pfs));
            }
            if (nomFichier.endsWith("xlsm")) {
                OPCPackage pkg = OPCPackage.open(new File(nomFichier));
                bufferInput.close();
                XSSFWorkbook wb = new XSSFWorkbook(pkg);
                pkg.close();
                return wb;
            }
            bufferInput.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("Le fichier doit être au format .xls, .xlsm ou .xlsx !");
        return null;
    }

public static ArrayList<String> getListeIdRelease(String nomFichier, String password, String nomOnglet){
        Workbook workbook = openWorkbook(nomFichier, password);
        Sheet sheet = workbook.getSheet(nomOnglet);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        Iterator<Row> rowIterator = sheet.iterator();
        TreeSet<String> listeIdRelease = new TreeSet<String>(Collator.getInstance());
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Cell cellRelease = row.getCell(releaseIndex);
            Cell cellEtat = row.getCell(etatIndex);
            if (cellRelease != null && cellEtat.getStringCellValue().equals("")) {
                listeIdRelease.add(getCellValueToString(cellRelease, evaluator));
            }
        }
        return new ArrayList<String>(listeIdRelease);
    }

I already close the file, bufferInput and OPCPackage.
Did you have and idea of what can cause this problem ?

Thanks in advance.

[gimbal2]

Excuse me, but how do you reason that you have memory problems from only "org.bonitasoft.engine.connector.exception.SConnec torException: java.util.concurrent.ExecutionException: java.lang.ExceptionInInitializerError" ?

[Tolls]

How does that even work for non xlsm files?

FileInputStream file = new FileInputStream(new File(nomFichier));
BufferedInputStream bufferInput = new BufferedInputStream(file);
file.close();

Closing the FileInputStream while you still intend on reading from it (via the BufferedInputStream) is sure to cause problems...possibly even the ones you're seeing.

Also, if you do get an exception in that code, then those streams will not be closed as they are not handled in a finally block.

[YannickL]

Excuse me, but how do you reason that you have memory problems from only "org.bonitasoft.engine.connector.exception.SConnec torException: java.util.concurrent.ExecutionException: java.lang.ExceptionInInitializerError" ?

I don't think this is a memory error because of this line. I don't understand this error.
I think there is a memory problem because the program works fine 3 times and fail after this.
If I close and reopen the software Bonita BPM, it works fine again 3 times and fail after this.
I don't know if the problem is from my code or from Bonita BPM.

How does that even work for non xlsm files?

FileInputStream file = new FileInputStream(new File(nomFichier));
BufferedInputStream bufferInput = new BufferedInputStream(file);
file.close();

Closing the FileInputStream while you still intend on reading from it (via the BufferedInputStream) is sure to cause problems...possibly even the ones you're seeing.

Also, if you do get an exception in that code, then those streams will not be closed as they are not handled in a finally block.

Thanks, I will correct this.
I need to add :

finally {
bufferInput.close();
file.close();
}

?
If I do that, it says that "file might not have been initialized".

[gimbal2]

But that's an incredibly basic Java programming error so I'm sure you know how to fix that. Right?

[Tolls]

In addition, closing the buffered stream will close the file stream.
No need to close that separately.

[YannickL]

But that's an incredibly basic Java programming error so I'm sure you know how to fix that. Right?

If I know how to fix that, I won't ask for help.
Google says that it is a problem with a static block. I think I need to initialize something but the log doesn't say what.

[gimbal2]

If you don't know how to fix that you're trying to hack together complicated code with only a very minimal knowledge of the bare basics of Java programming. In that case there is only one way to go: get a book on basic Java programming and start learning stuff.

[YannickL]

I don't think it's so basic if no-one can give me an hint.


I try to create an other simplier example :

public class TestExcelCourt {

    public static void main (String[] args) {
        System.out.println(getData("D:\\Doc stage\\essai.xlsm", "Feuil1"));
    }

    public static String getData(String nomFichier, String nomOnglet) {
        Workbook workbook = openWorkbook(nomFichier);
        Sheet sheet = workbook.getSheet(nomOnglet);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        return cell.getStringCellValue();
    }

    public static Workbook openWorkbook(String nomFichier) {
        File file = new File(nomFichier);
        try {
            OPCPackage pkg = OPCPackage.open(file);
            XSSFWorkbook wb = new XSSFWorkbook(pkg);
            pkg.close();
            return wb;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}

With IntelliJ IDEA it works fine every time.
And when I import it in Bonita BPM, it works 4 times, and after I have the same problem.

Here is the complete log :

2014-06-06 11:52:48 org.bonitasoft.engine.api.impl.ProcessAPIImpl 
Infos: THREAD_ID=37 | HOSTNAME=W28323 | TENANT_ID=1 | The user <YLombardi> has installed process <Pool4> in version <1.0> with id <5517258604722021641>
2014-06-06 11:52:48 org.bonitasoft.engine.api.impl.transaction.process.EnableProcess 
Infos: THREAD_ID=37 | HOSTNAME=W28323 | TENANT_ID=1 | The user <YLombardi> has enabled process <Pool4> in version <1.0> with id <5517258604722021641>
2014-06-06 11:52:51 org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/bonita].[jsp] 
Précis:  Disabling the response for futher output
2014-06-06 11:52:51 org.bonitasoft.engine.api.impl.ProcessStarter 
Infos: THREAD_ID=50 | HOSTNAME=W28323 | TENANT_ID=1 | The user <YLombardi> has started the process instance <7> of process <Pool4> in version <1.0> and id <5517258604722021641>
2014-06-06 11:52:51 org.bonitasoft.engine.execution.work.FailureHandlingBonitaWork 
Avertissement: THREAD_ID=87 | HOSTNAME=W28323 | TENANT_ID=1 | The work [ProcessInstanceContextWork: processInstanceId = 7] failed. The failure will be handled.
2014-06-06 11:52:51 org.bonitasoft.engine.execution.work.FailureHandlingBonitaWork 
Avertissement: THREAD_ID=87 | HOSTNAME=W28323 | TENANT_ID=1 | org.bonitasoft.engine.core.connector.exception.SConnectorException : "PROCESS_DEFINITION_ID=5517258604722021641 | PROCESS_NAME=Pool4 | PROCESS_VERSION=1.0 | PROCESS_INSTANCE_ID=7 | ROOT_PROCESS_INSTANCE_ID=7 | FLOW_NODE_DEFINITION_ID=-7980742042625586070 | FLOW_NODE_INSTANCE_ID=18 | FLOW_NODE_NAME=Step1 | CONNECTOR_DEFINITION_IMPLEMENTATION_CLASS_NAME=sf | CONNECTOR_INSTANCE_ID=7 | org.bonitasoft.engine.connector.exception.SConnectorException: java.util.concurrent.ExecutionException: java.lang.ExceptionInInitializerError"

[Tolls]

If there is no other information available there's not a huge amount we can say.
Does a simple Hello World app work?

public static main(String[] args) {
    System.out.println("Hello World!");
}

And then build up from there.

To be honest, I would expect more from some bit of code that's supposed to sit in a BPM system...

[SurfMan]

Why are you using OPCPackage? Any good reason not to use the org.apache.poi.ss.usermodel.WorkbookFactory?

Edit: Nevermind. The WorkbookFactory uses OPCPackage as well. Keep moving. Nothing to see here, people.

[YannickL]

If there is no other information available there's not a huge amount we can say.
Does a simple Hello World app work?

Yes, it works everytime with a simple Hello World.

I hope Bonita support will help me to have more information about why it doesn't work everytime or how to debug with their BPM.

[Tolls]

Then add in some bits.
Log each step of the Excel version and see where it falls over.