Hello Andrus,
Thanks for trying to help out. I'm using MariaDB
(10.1.12-MariaDB-1~trusty), (org.mariadb.jdbc.Driver, JAR file
mariadb-java-client-1.2.0.jar).
I can check Cayenne version 4.0.M3 also.
/Fredrik
Here's the stored procedure I try to call.
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
2016-04-19 9:12 GMT+02:00 Andrus Adamchik <and...@objectstyle.org>:
> 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)
> >
>
>
