Accessing oracle.sql.ARRAY values (filled from a mysql query)

Hi,

I am using BOS 6.2.1 and I am trying to access the ResultSet data from a query on my mySQL database. I configure the connector using de wizard and set the tableResult (on the last form of the configuration process, the grafics mode) to my pool variable rset (of type java.sql.ARRAY). When I test the connector, it's just work properly, and I can see the data on the form test result.

I need to show the data on a table widget on the next task form and I tried to setup the widget on a script with the code below (that I found here: http://community.bonitasoft.com/answers/fill-table-widget-values-db), but it does'nt work for me.

final List<List<Object>> resultTable = new ArrayList<List<Object>>();
int maxColumn = rset.getMetaData().getColumnCount()+1;
    while(rset.next()){
        final List<Object> row = new ArrayList<Object>();
            for(int colIndex = 1; colIndex < maxColumn ; colIndex++){
                row.add(rset.getObject(colIndex));
            }
            resultTable.add(Collections.unmodifiableList(row));
    }
return resultTable;

Here is the engine log result:

Jan 22, 2014 10:32:07 AM org.bonitasoft.console.server.listener.StudioWatchdogListener$1 run
Advertência: Bonita Studio watchdog process has started on 6969 with a delay of 20000ms
Jan 22, 2014 10:34:29 AM org.bonitasoft.forms.server.provider.impl.FormServiceProviderImpl resolveExpressions
Grave: Error while communicating with the engine.
org.bonitasoft.forms.server.exception.BPMEngineException: Error when evaluating expressions on activity instance 4
    at org.bonitasoft.forms.server.accessor.api.ExpressionEvaluatorEngineClient.evaluateExpressionsOnActivityInstance(ExpressionEvaluatorEngineClient.java:44)
    at org.bonitasoft.forms.server.api.impl.FormExpressionsAPIImpl.evaluateActivityExpressions(FormExpressionsAPIImpl.java:876)
    at org.bonitasoft.forms.server.api.impl.FormWorkflowAPIImpl.getActivityFieldsValues(FormWorkflowAPIImpl.java:807)
    at org.bonitasoft.forms.server.provider.impl.FormServiceProviderImpl.resolveExpressions(FormServiceProviderImpl.java:887)
    at org.bonitasoft.forms.server.api.impl.util.FormFieldValuesUtil.setFormWidgetsValues(FormFieldValuesUtil.java:877)
    at org.bonitasoft.forms.server.FormsServlet.getFormFirstPage(FormsServlet.java:218)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
    at org.bonitasoft.forms.server.FormsServlet.processCall(FormsServlet.java:130)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
    at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.bonitasoft.console.security.SessionFixationValve.invoke(SessionFixationValve.java:77)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Unknown Source)
Caused by: org.bonitasoft.engine.expression.ExpressionEvaluationException: org.bonitasoft.engine.expression.exception.SInvalidExpressionException: Declared return type unknown: oracle.sql.ARRAY for expression rset
    at org.bonitasoft.engine.api.impl.ProcessAPIImpl.evaluateExpressionsOnActivityInstance(ProcessAPIImpl.java:5418)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.bonitasoft.engine.api.impl.ServerAPIImpl.invokeAPI(ServerAPIImpl.java:292)
    at org.bonitasoft.engine.api.impl.ServerAPIImpl.access$000(ServerAPIImpl.java:63)
    at org.bonitasoft.engine.api.impl.ServerAPIImpl$1.call(ServerAPIImpl.java:261)
    at org.bonitasoft.engine.transaction.JTATransactionServiceImpl.executeInTransaction(JTATransactionServiceImpl.java:207)
    at org.bonitasoft.engine.api.impl.ServerAPIImpl.invokeAPIInTransaction(ServerAPIImpl.java:268)
    at org.bonitasoft.engine.api.impl.ServerAPIImpl.invokeAPI(ServerAPIImpl.java:219)
    at org.bonitasoft.engine.api.impl.ServerAPIImpl.invokeMethod(ServerAPIImpl.java:105)
    at org.bonitasoft.engine.api.impl.ClientInterceptor.invoke(ClientInterceptor.java:88)
    at com.sun.proxy.$Proxy13.evaluateExpressionsOnActivityInstance(Unknown Source)
    at org.bonitasoft.forms.server.accessor.api.ExpressionEvaluatorEngineClient.evaluateExpressionsOnActivityInstance(ExpressionEvaluatorEngineClient.java:42)
    ... 29 more
Caused by: org.bonitasoft.engine.expression.exception.SInvalidExpressionException: Declared return type unknown: oracle.sql.ARRAY for expression rset
    at org.bonitasoft.engine.expression.impl.ExpressionServiceImpl.checkReturnType(ExpressionServiceImpl.java:161)
    at org.bonitasoft.engine.expression.impl.ExpressionServiceImpl.evaluate(ExpressionServiceImpl.java:132)
    at org.bonitasoft.engine.core.expression.control.api.impl.ExpressionResolverServiceImpl.evaluateExpressionsOfKind(ExpressionResolverServiceImpl.java:161)
    at org.bonitasoft.engine.core.expression.control.api.impl.ExpressionResolverServiceImpl.evaluateExpressionsFlatten(ExpressionResolverServiceImpl.java:104)
    at org.bonitasoft.engine.core.expression.control.api.impl.ExpressionResolverServiceImpl.evaluate(ExpressionResolverServiceImpl.java:72)
    at org.bonitasoft.engine.api.impl.transaction.expression.EvaluateExpressionsInstanceLevel.execute(EvaluateExpressionsInstanceLevel.java:69)
    at org.bonitasoft.engine.api.impl.ProcessAPIImpl.evaluateExpressionsInstanceLevel(ProcessAPIImpl.java:5457)
    at org.bonitasoft.engine.api.impl.ProcessAPIImpl.evaluateExpressionsOnActivityInstance(ProcessAPIImpl.java:5414)
    ... 43 more
Caused by: java.lang.ClassNotFoundException: oracle.sql.ARRAY
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1680)
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1526)
    at org.bonitasoft.engine.classloader.BonitaClassLoader.loadClass(BonitaClassLoader.java:136)
    at org.bonitasoft.engine.classloader.VirtualClassLoader.loadClass(VirtualClassLoader.java:57)
    at org.bonitasoft.engine.classloader.ParentRedirectClassLoader.loadClass(ParentRedirectClassLoader.java:23)
    at org.bonitasoft.engine.classloader.BonitaClassLoader.loadClass(BonitaClassLoader.java:136)
    at org.bonitasoft.engine.classloader.VirtualClassLoader.loadClass(VirtualClassLoader.java:57)
    at org.bonitasoft.engine.classloader.ParentRedirectClassLoader.loadClass(ParentRedirectClassLoader.java:23)
    at org.bonitasoft.engine.classloader.BonitaClassLoader.loadClass(BonitaClassLoader.java:136)
    at org.bonitasoft.engine.classloader.VirtualClassLoader.loadClass(VirtualClassLoader.java:57)
    at org.bonitasoft.engine.expression.impl.ExpressionServiceImpl.checkReturnType(ExpressionServiceImpl.java:154)
    ... 50 more

Jan 22, 2014 10:34:29 AM org.bonitasoft.forms.server.FormsServlet getFormFirstPage
Grave: Error while getting the first page for application Pool4--1.0--Passo2$entry
org.bonitasoft.forms.server.exception.FormNotFoundException: Error while communicating with the engine.
    at org.bonitasoft.forms.server.provider.impl.FormServiceProviderImpl.resolveExpressions(FormServiceProviderImpl.java:941)
    at org.bonitasoft.forms.server.api.impl.util.FormFieldValuesUtil.setFormWidgetsValues(FormFieldValuesUtil.java:877)
    at org.bonitasoft.forms.server.FormsServlet.getFormFirstPage(FormsServlet.java:218)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
    at org.bonitasoft.forms.server.FormsServlet.processCall(FormsServlet.java:130)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
    at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.bonitasoft.console.security.SessionFixationValve.invoke(SessionFixationValve.java:77)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Unknown Source)

Thanks for any help!!!

Bene.

Hello bjnobre,

The problem comes from the call of : rset.getObject(colIndex) in your script.

The getObject method returns a oracle.sql.ARRAY object that is not in the classpath of the process.

To avoid this use a more typed method from the ResultSet.

The fact that this object is returned also implied that your not querying a MySQL database but an Oracle database.

Regards.

Thank U, rb.

I used the help from Christopherf and Haris at http://community.bonitasoft.com/answers/fill-table-widget-values-db and it just worked for me.

But your information will be usefull in the future.