Hi Fredrik, Sorry for late reply. So from the stack trace this is Cayenne 3.1:
https://github.com/apache/cayenne/blob/STABLE-3.1/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/dba/mysql/MySQLProcedureAction.java#L101 Interesting ... So Cayenne thinks there's a ResultSet, while there's none. We have a very similar stored procedure in Cayenne unit tests and it works fine on MySQL. Wondering what version of MySQL you are using? And can you also post the contents of "StoreDepartments" procedure? Perhaps I'll be able to reproduce. Also would it be possible for you to try it with Cayenne 4.0.M3? Andrus > On Apr 14, 2016, at 11:17 PM, Fredrik Widengren <fredrik.wideng...@gmail.com> > wrote: > > Hello, > > I'm trying to launch a stored procedure which is returning rows with a number > of columns. > > I try to follow the documentation but don't understand why I get these errors. > > As you can see, the text "After query" is not printed in the log. Which I > then assume mean that something goes wrong in the store procedure. > > When running the stored procedure from myPhpAdmin I get correct result (see > attached image) > > > If someone have some ideas, please share them. > > Many thanks, > Fredrik > ------------------------------------------------------------ > data map contains the following: > <procedure name="readShoplistIngredientsSortedByStore" catalog="foodbase"> > <procedure-parameter name="id_shoplist" type="INTEGER" > direction="in"/> > <procedure-parameter name="id_store" type="INTEGER" direction="in"/> > </procedure> > > > The stored procedure looks like this: > CREATE DEFINER=`foodbase_admin`@`localhost` PROCEDURE > `readShoplistIngredientsSortedByStore`(IN `id_shoplist` INT, IN `id_store` > INT) > NO SQL > BEGIN > > CALL StoreDepartments(id_store); > > SELECT departments.Name AS Department, shoppinglist_items.Amount AS Amount, > units.Name AS Unit, groceries.Name AS Groceries > FROM shoppinglist_items > JOIN groceries ON > groceries.ID=shoppinglist_items.ID_groceries > JOIN storedepartmentsorder ON > groceries.ID_departments=storedepartmentsorder.ID_departments > JOIN units ON > units.ID=shoppinglist_items.ID_units > JOIN departments ON > departments.ID=groceries.ID_departments > WHERE shoppinglist_items.ID_shoppinglists=id_shoplist > ORDER BY storedepartmentsorder.deptorder, groceries.Name > ; > > END > > > Java code: > > @SuppressWarnings("unchecked") > public String getSortedIngredients() { > try { > System.out.println("get sorted ingred...."); > System.out.println("Store ID.............."+storeID); > > ProcedureQuery query = new > ProcedureQuery("readShoplistIngredientsSortedByStore"); > > query.addParameter("id_shoplist", activeShoppingList.getId()); > query.addParameter("id_store", storeID); > > System.out.println("before query..."); > > // run query > QueryResponse result = context.performGenericQuery(query); > System.out.println("After query"); > > > for (result.reset(); result.next();) { > if (result.isList()) { > shoppinglistItems = (List<ShoppinglistItems>) > result.currentList(); > // ... > > } > else { > int[] updateCounts = result.currentUpdateCount(); > // ... > } > } > } catch (Exception e) { > System.out.println("catch exception"); > e.printStackTrace(); > } > > return null; > } > > > Tomcat log: > > get sorted ingred.... > Store ID..............2 > before query... > apr 14, 2016 9:43:06 EM org.apache.cayenne.log.CommonsJdbcEventLogger > logConnect > INFO: Opening connection: jdbc:mariadb://127.0.0.1:3306/foodbase > Login: foodbase_admin > Password: ******* > apr 14, 2016 9:43:06 EM org.apache.cayenne.log.CommonsJdbcEventLogger > logConnectSuccess > INFO: +++ Connecting: SUCCESS. > apr 14, 2016 9:43:06 EM org.apache.cayenne.log.CommonsJdbcEventLogger log > INFO: Detected and installed adapter: > org.apache.cayenne.dba.mysql.MySQLAdapter > apr 14, 2016 9:43:06 EM org.apache.cayenne.log.CommonsJdbcEventLogger > logBeginTransaction > INFO: --- transaction started. > apr 14, 2016 9:43:06 EM org.apache.cayenne.log.CommonsJdbcEventLogger logQuery > INFO: {call readShoplistIngredientsSortedByStore(?, ?)} [bind: 1:1006, 2:2] > apr 14, 2016 9:43:06 EM org.apache.cayenne.log.CommonsJdbcEventLogger > logSelectCount > INFO: === returned 18 rows. - took 2 ms. > apr 14, 2016 9:43:06 EM org.apache.cayenne.log.CommonsJdbcEventLogger > logQueryError > INFO: *** error. > java.lang.NullPointerException > at > org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:101) > at > org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:74) > at > org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87) > at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:280) > at > org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:453) > at > org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:70) > at > org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:426) > at > org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:877) > at > org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:423) > at > org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:122) > at > org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:758) > at > org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:1009) > at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:748) > at > org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:350) > at > org.apache.cayenne.util.ObjectContextQueryAction.executePostCache(ObjectContextQueryAction.java:106) > at > org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:93) > at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:989) > at > org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:948) > at > controller.PrintController.getSortedIngredients(PrintController.java:101) > at controller.PrintController.setLocalObject(PrintController.java:71) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:601) > at org.apache.el.parser.AstValue.invoke(AstValue.java:278) > at > org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:274) > at > org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:40) > at > org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:50) > at > com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105) > at > javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87) > at > com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102) > at javax.faces.component.UIViewAction.broadcast(UIViewAction.java:559) > at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790) > at > javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282) > at > com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81) > at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101) > at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198) > at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646) > at > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) > at > org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) > at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) > at > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) > at > org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) > at > org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) > at > org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) > at > org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) > at > org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) > at > org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100) >
