We are using Boomi to do an integration between a DB and an MS Access DB. We are using uCanAccess as our JDBC so we can read/write the MS Access DB.
However if our initial query returns more than 49 rows, we receive the following error from Boomi and in turn uCanAccess. I tried using flow control in order to only process 10 documents at a time, but that didn't work. Same error. I tried changing the batch count. Nothing.
Test execution of RYN-EMPDATA-BadgeDB completed with errors. Embedded message: Timer already cancelled.
uCanAccess site Version 3.0.6 with the recommended Jackcess and HSQLDB versions
The insert operations succeed but the update operations do not. We are using a dynamic update DB profile that has been imported from the Access File.
Some new information. Turns out the failure comes at a specific record. I think it's complaining about a NULL value being passed but I can't be 100%
Here is the top part of the Java error:
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.6 null
at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:212)
at com.boomi.connector.database.executor.SQLWriteExecutor.checkCommit(SQLWriteExecutor.java:44)
at com.boomi.connector.database.executor.SQLWriteExecutor.execute(SQLWriteExecutor.java:65)
at com.boomi.connector.database.DatabaseSend.executeData(DatabaseSend.java:192)
at com.boomi.connector.database.DatabaseSend.executeStatement(DatabaseSend.java:144)
at com.boomi.connector.database.DatabaseSend.executeDocument(DatabaseSend.java:121)
at com.boomi.connector.database.DatabaseSend.handle(DatabaseSend.java:297)
at com.boomi.connector.database.DatabaseSend.send(DatabaseSend.java:72)
at com.boomi.connector.custom.CustomSendConnectorAction.invoke(CustomSendConnectorAction.java:60)
at com.boomi.connector.base.BaseConnectorAction.invokeBase(BaseConnectorAction.java:295)
at com.boomi.connector.base.BaseConnectorAction.invoke(BaseConnectorAction.java:234)
at sun.reflect.GeneratedMethodAccessor30.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.boomi.util.ClassUtil$InvokerWithClassLoader.invoke(ClassUtil.java:360)
at com.sun.proxy.$Proxy30.invoke(Unknown Source)
at com.boomi.process.shape.ConnectorShapeHandler.execute(ConnectorShapeHandler.java:152)
at com.boomi.process.shape.ConnectorShape.execute(ConnectorShape.java:143)
at com.boomi.process.graph.ProcessShape.executeShape(ProcessShape.java:550)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:488)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:509)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.executeShape(ProcessGraph.java:509)
at com.boomi.process.graph.ProcessGraph.executeNextShapes(ProcessGraph.java:572)
at com.boomi.process.graph.ProcessGraph.execute(ProcessGraph.java:307)
at com.boomi.process.ProcessExecution.call(ProcessExecution.java:783)
at com.boomi.execution.ExecutionTask.call(ExecutionTask.java:920)
at com.boomi.execution.ExecutionTask.call(ExecutionTask.java:63)
at com.boomi.util.concurrent.CancellableFutureTask.run(CancellableFutureTask.java:172)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.6 null
at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:348)
at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:205)
... 32 more
Caused by: java.lang.StackOverflowError
at java.nio.DirectByteBuffer.put(Unknown Source)
at java.nio.DirectByteBuffer.put(Unknown Source)
9/6 After extensive testing the cause of the problem has to do with getting to a field that contains binary data in one of the fields. We are not updating that field, but for some reason once uCanAccess hits a row that has binary data (a photo stored by the program) it fails.
Here is the output from trying to update the same record via the uCanAccess Console:
UCanAccess>update badgelisting set firstname="Test", lastname="stuff", active="false" where idnumber=58;
java.lang.StackOverflowError
at java.nio.DirectByteBuffer.put(Unknown Source)
at java.nio.ByteBuffer.put(Unknown Source)
at java.nio.DirectByteBuffer.put(Unknown Source)
at java.nio.DirectByteBuffer.put(Unknown Source)
at sun.nio.ch.IOUtil.write(Unknown Source)
at sun.nio.ch.FileChannelImpl.writeInternal(Unknown Source)
at sun.nio.ch.FileChannelImpl.write(Unknown Source)
at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:335)
at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
at com.healthmarketscience.jackcess.impl.UsageMap.removePageNumber(UsageMap.java:322)
at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:339)
at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.createNewUsageMapPage(UsageMap.java:748)
at com.healthmarketscience.jackcess.impl.UsageMap$ReferenceHandler.addOrRemovePageNumber(UsageMap.java:732)
at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.ja
---
A lot of this repeated a lot...
---
va:339)
at com.healthmarketscience.jackcess.impl.TempPageHolder.setNewPage(TempPageHolder.java:104)
UCanAccess>UCAExc:::3.0.6 null
A lot of this came down to timing. If someone was in the DB just before processing, I would get errors. Also, I had to break up the processing into bunches of 30 items. Then I had to setup a process that would run a compact/repair after each run because it was bloating the DB about 30%.
As for Boomi, writing to an AccessDB should be a simple task. I shouldn't have to revert to UCanAccess in order to get this done.