You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Fredrik Widengren <fr...@gmail.com> on 2016/04/14 22:17:27 UTC
StoredProcedures and Cayenne
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)
Fwd: StoredProcedures and Cayenne
Posted by Fredrik Widengren <fr...@gmail.com>.
Reposting my question, just in case.
---------- Forwarded message ----------
From: Fredrik Widengren <fr...@gmail.com>
Date: 2016-04-14 22:17 GMT+02:00
Subject: StoredProcedures and Cayenne
To: user@cayenne.apache.org
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)
Re: StoredProcedures and Cayenne
Posted by Andrus Adamchik <an...@objectstyle.org>.
Great. I am glad there was an easy fix :)
> On Apr 24, 2016, at 3:08 PM, Fredrik Widengren <fr...@gmail.com> wrote:
>
> Hello Andrus,
>
> Problem seam to have been the driver version for MariaDB. I now use the
> same as you 1.4.2.
>
> Many thanks for the help!
>
> Best regards,
> Fredrik
>
>
>
> 2016-04-22 20:56 GMT+02:00 Andrus Adamchik <an...@objectstyle.org>:
>
>> I finally got around to building an environment on the same version of
>> MariaDB. I used the following simplified versions of 2 stored procedures
>> (as of course I don't have the exact definitions of your tables) :
>>
>> DELIMITER $$
>> CREATE DEFINER=`root`@`localhost` PROCEDURE
>> `readShoplistIngredientsSortedByStore`(IN `id_shoplist` INT, IN `id_store`
>> INT)
>> NO SQL
>> BEGIN
>> CALL StoreDepartments(id_store);
>> SELECT * from departments WHERE id > id_shoplist;
>> END$$
>>
>> CREATE DEFINER=`root`@`localhost` PROCEDURE
>> `StoreDepartments`(IN `id_store` INT)
>> NO SQL
>> BEGIN
>> INSERT INTO departments VALUES();
>> END$$
>> DELIMITER ;
>>
>> .. and it worked. No errors, I get data and all... I tried with both
>> mysql-connector v. 5.1.38 and mariadb-java-client v. 1.4.3 drivers. No
>> difference. It always works. Also tried with Cayenne 3.1 and 4.0.M3. No
>> difference again.
>>
>> What version of the driver are you using?
>>
>> Also repeating my question about the contents of "StoreDepartments" stored
>> procedure. Maybe there's something unusual about it?
>>
>> Andrus
>>
>>
>>
>>> On Apr 22, 2016, at 10:00 AM, Andrus Adamchik <an...@objectstyle.org>
>> wrote:
>>>
>>> Thanks for more info. Trying to find time to setup a test env with your
>> exact procedure.
>>>
>>>>> CALL StoreDepartments(id_store);
>>>
>>> I asked before, but I don't see you mentioning it anywhere.
>> 'StoreDepartments' is called from within
>> 'readShoplistIngredientsSortedByStore'. Could you post a definition of
>> 'StoreDepartments'?
>>>
>>> Thanks,
>>> Andrus
>>>
>>>
>>>> On Apr 22, 2016, at 7:54 AM, Fredrik Widengren <
>> fredrik.widengren@gmail.com> wrote:
>>>>
>>>> Hello Andrus,
>>>>
>>>> I'm now running Cayenne 4.0.M3.
>>>>
>>>> However, seem like I get the same error on this procedure call. I'm
>> using
>>>> some other procedures which work fine. The difference is that they only
>>>> return one value which also is declared as OUT parameter. This is also
>>>> reflected in the datamap.xml file.
>>>>
>>>> The procedure I'm tryign to run now returns a list and does not declare
>> any
>>>> OUT parameters. The datamap does only contain in parameters.
>>>>
>>>> I get the error when calling:
>>>> QueryResponse result = context.performGenericQuery(query);
>>>>
>>>> Any ideas? Thanks for the help!
>>>>
>>>> /Fredrik
>>>>
>>>>
>>>> Stack trace:
>>>> INFO: {call readShoplistIngredientsSortedByStore(?, ?)} [bind: 1:0, 2:4]
>>>> apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
>>>> logSelectCount
>>>> INFO: === returned 0 rows. - took 1 ms.
>>>> apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
>>>> logQueryError
>>>> INFO: *** error.
>>>> java.lang.NullPointerException
>>>> at
>>>>
>> org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
>>>> at
>>>>
>> org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
>>>> at
>>>>
>> org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
>>>> at
>> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
>>>> at
>>>>
>> org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
>>>> at
>>>>
>> org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
>>>> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
>>>> 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:985)
>>>> at
>>>>
>> org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
>>>> at
>>>>
>> controller.PrintController.getSortedIngredients(PrintController.java:168)
>>>> at controller.PrintController.setLocalObject(PrintController.java:91)
>>>> 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)
>>>> at
>>>>
>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
>>>> at
>>>>
>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
>>>> at
>>>>
>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
>>>> at
>>>>
>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
>>>> at
>>>>
>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
>>>> at
>>>>
>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
>>>> at
>>>>
>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
>>>> at
>>>>
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
>>>> at java.lang.Thread.run(Thread.java:722)
>>>>
>>>> catch exception
>>>> org.apache.cayenne.CayenneRuntimeException: [v.4.0.M3 Feb 08 2016
>> 17:09:41]
>>>> Query exception.
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:582)
>>>> at
>> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:312)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
>>>> at
>>>>
>> org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
>>>> at
>>>>
>> org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
>>>> at
>>>>
>> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
>>>> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
>>>> 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:985)
>>>> at
>>>>
>> org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
>>>> at
>>>>
>> controller.PrintController.getSortedIngredients(PrintController.java:168)
>>>> at controller.PrintController.setLocalObject(PrintController.java:91)
>>>> 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)
>>>> at
>>>>
>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
>>>> at
>>>>
>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
>>>> at
>>>>
>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
>>>> at
>>>>
>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
>>>> at
>>>>
>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
>>>> at
>>>>
>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
>>>> at
>>>>
>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
>>>> at
>>>>
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
>>>> at java.lang.Thread.run(Thread.java:722)
>>>> Caused by: java.lang.NullPointerException
>>>> at
>>>>
>> org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
>>>> at
>>>>
>> org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
>>>> at
>>>>
>> org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
>>>> at
>> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
>>>> ... 55 more
>>>> End of setLocalObject
>>>>
>>>> 2016-04-20 6:10 GMT+02:00 Fredrik Widengren <
>> fredrik.widengren@gmail.com>:
>>>>
>>>>> 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 <an...@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.widengren@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)
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>
>>
>>
Re: StoredProcedures and Cayenne
Posted by Fredrik Widengren <fr...@gmail.com>.
Hello Andrus,
Problem seam to have been the driver version for MariaDB. I now use the
same as you 1.4.2.
Many thanks for the help!
Best regards,
Fredrik
2016-04-22 20:56 GMT+02:00 Andrus Adamchik <an...@objectstyle.org>:
> I finally got around to building an environment on the same version of
> MariaDB. I used the following simplified versions of 2 stored procedures
> (as of course I don't have the exact definitions of your tables) :
>
> DELIMITER $$
> CREATE DEFINER=`root`@`localhost` PROCEDURE
> `readShoplistIngredientsSortedByStore`(IN `id_shoplist` INT, IN `id_store`
> INT)
> NO SQL
> BEGIN
> CALL StoreDepartments(id_store);
> SELECT * from departments WHERE id > id_shoplist;
> END$$
>
> CREATE DEFINER=`root`@`localhost` PROCEDURE
> `StoreDepartments`(IN `id_store` INT)
> NO SQL
> BEGIN
> INSERT INTO departments VALUES();
> END$$
> DELIMITER ;
>
> .. and it worked. No errors, I get data and all... I tried with both
> mysql-connector v. 5.1.38 and mariadb-java-client v. 1.4.3 drivers. No
> difference. It always works. Also tried with Cayenne 3.1 and 4.0.M3. No
> difference again.
>
> What version of the driver are you using?
>
> Also repeating my question about the contents of "StoreDepartments" stored
> procedure. Maybe there's something unusual about it?
>
> Andrus
>
>
>
> > On Apr 22, 2016, at 10:00 AM, Andrus Adamchik <an...@objectstyle.org>
> wrote:
> >
> > Thanks for more info. Trying to find time to setup a test env with your
> exact procedure.
> >
> >>> CALL StoreDepartments(id_store);
> >
> > I asked before, but I don't see you mentioning it anywhere.
> 'StoreDepartments' is called from within
> 'readShoplistIngredientsSortedByStore'. Could you post a definition of
> 'StoreDepartments'?
> >
> > Thanks,
> > Andrus
> >
> >
> >> On Apr 22, 2016, at 7:54 AM, Fredrik Widengren <
> fredrik.widengren@gmail.com> wrote:
> >>
> >> Hello Andrus,
> >>
> >> I'm now running Cayenne 4.0.M3.
> >>
> >> However, seem like I get the same error on this procedure call. I'm
> using
> >> some other procedures which work fine. The difference is that they only
> >> return one value which also is declared as OUT parameter. This is also
> >> reflected in the datamap.xml file.
> >>
> >> The procedure I'm tryign to run now returns a list and does not declare
> any
> >> OUT parameters. The datamap does only contain in parameters.
> >>
> >> I get the error when calling:
> >> QueryResponse result = context.performGenericQuery(query);
> >>
> >> Any ideas? Thanks for the help!
> >>
> >> /Fredrik
> >>
> >>
> >> Stack trace:
> >> INFO: {call readShoplistIngredientsSortedByStore(?, ?)} [bind: 1:0, 2:4]
> >> apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
> >> logSelectCount
> >> INFO: === returned 0 rows. - took 1 ms.
> >> apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
> >> logQueryError
> >> INFO: *** error.
> >> java.lang.NullPointerException
> >> at
> >>
> org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
> >> at
> >>
> org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
> >> at
> >>
> org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
> >> at
> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
> >> at
> >>
> org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
> >> at
> >>
> org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
> >> at
> >>
> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
> >> at
> >>
> org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
> >> at
> >>
> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
> >> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
> >> 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:985)
> >> at
> >>
> org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
> >> at
> >>
> controller.PrintController.getSortedIngredients(PrintController.java:168)
> >> at controller.PrintController.setLocalObject(PrintController.java:91)
> >> 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)
> >> at
> >>
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
> >> at
> >>
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
> >> at
> >>
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
> >> at
> >>
> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
> >> at
> >>
> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
> >> at
> >>
> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
> >> at
> >>
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> >> at
> >>
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> >> at java.lang.Thread.run(Thread.java:722)
> >>
> >> catch exception
> >> org.apache.cayenne.CayenneRuntimeException: [v.4.0.M3 Feb 08 2016
> 17:09:41]
> >> Query exception.
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:582)
> >> at
> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:312)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
> >> at
> >>
> org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
> >> at
> >>
> org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
> >> at
> >>
> org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
> >> at
> >>
> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
> >> at
> >>
> org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
> >> at
> >>
> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
> >> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
> >> 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:985)
> >> at
> >>
> org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
> >> at
> >>
> controller.PrintController.getSortedIngredients(PrintController.java:168)
> >> at controller.PrintController.setLocalObject(PrintController.java:91)
> >> 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)
> >> at
> >>
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
> >> at
> >>
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
> >> at
> >>
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
> >> at
> >>
> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
> >> at
> >>
> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
> >> at
> >>
> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
> >> at
> >>
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> >> at
> >>
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> >> at java.lang.Thread.run(Thread.java:722)
> >> Caused by: java.lang.NullPointerException
> >> at
> >>
> org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
> >> at
> >>
> org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
> >> at
> >>
> org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
> >> at
> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
> >> ... 55 more
> >> End of setLocalObject
> >>
> >> 2016-04-20 6:10 GMT+02:00 Fredrik Widengren <
> fredrik.widengren@gmail.com>:
> >>
> >>> 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 <an...@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.widengren@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)
> >>>>>
> >>>>
> >>>>
> >>>
> >
>
>
Re: StoredProcedures and Cayenne
Posted by Andrus Adamchik <an...@objectstyle.org>.
I finally got around to building an environment on the same version of MariaDB. I used the following simplified versions of 2 stored procedures (as of course I don't have the exact definitions of your tables) :
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE
`readShoplistIngredientsSortedByStore`(IN `id_shoplist` INT, IN `id_store` INT)
NO SQL
BEGIN
CALL StoreDepartments(id_store);
SELECT * from departments WHERE id > id_shoplist;
END$$
CREATE DEFINER=`root`@`localhost` PROCEDURE
`StoreDepartments`(IN `id_store` INT)
NO SQL
BEGIN
INSERT INTO departments VALUES();
END$$
DELIMITER ;
.. and it worked. No errors, I get data and all... I tried with both mysql-connector v. 5.1.38 and mariadb-java-client v. 1.4.3 drivers. No difference. It always works. Also tried with Cayenne 3.1 and 4.0.M3. No difference again.
What version of the driver are you using?
Also repeating my question about the contents of "StoreDepartments" stored procedure. Maybe there's something unusual about it?
Andrus
> On Apr 22, 2016, at 10:00 AM, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> Thanks for more info. Trying to find time to setup a test env with your exact procedure.
>
>>> CALL StoreDepartments(id_store);
>
> I asked before, but I don't see you mentioning it anywhere. 'StoreDepartments' is called from within 'readShoplistIngredientsSortedByStore'. Could you post a definition of 'StoreDepartments'?
>
> Thanks,
> Andrus
>
>
>> On Apr 22, 2016, at 7:54 AM, Fredrik Widengren <fr...@gmail.com> wrote:
>>
>> Hello Andrus,
>>
>> I'm now running Cayenne 4.0.M3.
>>
>> However, seem like I get the same error on this procedure call. I'm using
>> some other procedures which work fine. The difference is that they only
>> return one value which also is declared as OUT parameter. This is also
>> reflected in the datamap.xml file.
>>
>> The procedure I'm tryign to run now returns a list and does not declare any
>> OUT parameters. The datamap does only contain in parameters.
>>
>> I get the error when calling:
>> QueryResponse result = context.performGenericQuery(query);
>>
>> Any ideas? Thanks for the help!
>>
>> /Fredrik
>>
>>
>> Stack trace:
>> INFO: {call readShoplistIngredientsSortedByStore(?, ?)} [bind: 1:0, 2:4]
>> apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
>> logSelectCount
>> INFO: === returned 0 rows. - took 1 ms.
>> apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
>> logQueryError
>> INFO: *** error.
>> java.lang.NullPointerException
>> at
>> org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
>> at
>> org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
>> at
>> org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
>> at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
>> at
>> org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
>> at
>> org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
>> at
>> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
>> at
>> org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
>> at
>> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
>> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
>> 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:985)
>> at
>> org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
>> at
>> controller.PrintController.getSortedIngredients(PrintController.java:168)
>> at controller.PrintController.setLocalObject(PrintController.java:91)
>> 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)
>> at
>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
>> at
>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
>> at
>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
>> at
>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
>> at
>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
>> at
>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
>> at
>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
>> at
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
>> at java.lang.Thread.run(Thread.java:722)
>>
>> catch exception
>> org.apache.cayenne.CayenneRuntimeException: [v.4.0.M3 Feb 08 2016 17:09:41]
>> Query exception.
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:582)
>> at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:312)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
>> at
>> org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
>> at
>> org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
>> at
>> org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
>> at
>> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
>> at
>> org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
>> at
>> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
>> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
>> 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:985)
>> at
>> org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
>> at
>> controller.PrintController.getSortedIngredients(PrintController.java:168)
>> at controller.PrintController.setLocalObject(PrintController.java:91)
>> 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)
>> at
>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
>> at
>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
>> at
>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
>> at
>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
>> at
>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
>> at
>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
>> at
>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
>> at
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
>> at java.lang.Thread.run(Thread.java:722)
>> Caused by: java.lang.NullPointerException
>> at
>> org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
>> at
>> org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
>> at
>> org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
>> at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
>> ... 55 more
>> End of setLocalObject
>>
>> 2016-04-20 6:10 GMT+02:00 Fredrik Widengren <fr...@gmail.com>:
>>
>>> 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 <an...@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.widengren@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)
>>>>>
>>>>
>>>>
>>>
>
Re: StoredProcedures and Cayenne
Posted by Andrus Adamchik <an...@objectstyle.org>.
Thanks for more info. Trying to find time to setup a test env with your exact procedure.
>> CALL StoreDepartments(id_store);
I asked before, but I don't see you mentioning it anywhere. 'StoreDepartments' is called from within 'readShoplistIngredientsSortedByStore'. Could you post a definition of 'StoreDepartments'?
Thanks,
Andrus
> On Apr 22, 2016, at 7:54 AM, Fredrik Widengren <fr...@gmail.com> wrote:
>
> Hello Andrus,
>
> I'm now running Cayenne 4.0.M3.
>
> However, seem like I get the same error on this procedure call. I'm using
> some other procedures which work fine. The difference is that they only
> return one value which also is declared as OUT parameter. This is also
> reflected in the datamap.xml file.
>
> The procedure I'm tryign to run now returns a list and does not declare any
> OUT parameters. The datamap does only contain in parameters.
>
> I get the error when calling:
> QueryResponse result = context.performGenericQuery(query);
>
> Any ideas? Thanks for the help!
>
> /Fredrik
>
>
> Stack trace:
> INFO: {call readShoplistIngredientsSortedByStore(?, ?)} [bind: 1:0, 2:4]
> apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
> logSelectCount
> INFO: === returned 0 rows. - took 1 ms.
> apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
> logQueryError
> INFO: *** error.
> java.lang.NullPointerException
> at
> org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
> at
> org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
> at
> org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
> at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
> at
> org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
> at
> org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
> at
> org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
> at
> org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
> at
> org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
> at
> org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
> at
> org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
> at
> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
> at
> org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
> at
> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
> 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:985)
> at
> org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
> at
> controller.PrintController.getSortedIngredients(PrintController.java:168)
> at controller.PrintController.setLocalObject(PrintController.java:91)
> 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)
> at
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
> at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
> at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
> at
> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
> at
> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
> at
> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:722)
>
> catch exception
> org.apache.cayenne.CayenneRuntimeException: [v.4.0.M3 Feb 08 2016 17:09:41]
> Query exception.
> at
> org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:582)
> at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:312)
> at
> org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
> at
> org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
> at
> org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
> at
> org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
> at
> org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
> at
> org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
> at
> org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
> at
> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
> at
> org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
> at
> org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
> at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
> 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:985)
> at
> org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
> at
> controller.PrintController.getSortedIngredients(PrintController.java:168)
> at controller.PrintController.setLocalObject(PrintController.java:91)
> 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)
> at
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
> at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
> at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
> at
> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
> at
> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
> at
> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:722)
> Caused by: java.lang.NullPointerException
> at
> org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
> at
> org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
> at
> org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
> at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
> ... 55 more
> End of setLocalObject
>
> 2016-04-20 6:10 GMT+02:00 Fredrik Widengren <fr...@gmail.com>:
>
>> 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 <an...@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.widengren@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)
>>>>
>>>
>>>
>>
Re: StoredProcedures and Cayenne
Posted by Fredrik Widengren <fr...@gmail.com>.
Hello Andrus,
I'm now running Cayenne 4.0.M3.
However, seem like I get the same error on this procedure call. I'm using
some other procedures which work fine. The difference is that they only
return one value which also is declared as OUT parameter. This is also
reflected in the datamap.xml file.
The procedure I'm tryign to run now returns a list and does not declare any
OUT parameters. The datamap does only contain in parameters.
I get the error when calling:
QueryResponse result = context.performGenericQuery(query);
Any ideas? Thanks for the help!
/Fredrik
Stack trace:
INFO: {call readShoplistIngredientsSortedByStore(?, ?)} [bind: 1:0, 2:4]
apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
logSelectCount
INFO: === returned 0 rows. - took 1 ms.
apr 22, 2016 6:47:02 FM org.apache.cayenne.log.CommonsJdbcEventLogger
logQueryError
INFO: *** error.
java.lang.NullPointerException
at
org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
at
org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
at
org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
at
org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
at
org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
at
org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
at
org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
at
org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
at
org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
at
org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
at
org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
at
org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
at
org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
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:985)
at
org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
at
controller.PrintController.getSortedIngredients(PrintController.java:168)
at controller.PrintController.setLocalObject(PrintController.java:91)
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)
at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
catch exception
org.apache.cayenne.CayenneRuntimeException: [v.4.0.M3 Feb 08 2016 17:09:41]
Query exception.
at
org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:582)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:312)
at
org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:439)
at
org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:71)
at
org.apache.cayenne.access.DataDomainQueryAction$2.perform(DataDomainQueryAction.java:412)
at
org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
at
org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:409)
at
org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
at
org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:559)
at
org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
at
org.apache.cayenne.tx.TransactionFilter.onQuery(TransactionFilter.java:49)
at
org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:730)
at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:551)
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:985)
at
org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:944)
at
controller.PrintController.getSortedIngredients(PrintController.java:168)
at controller.PrintController.setLocalObject(PrintController.java:91)
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)
at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
Caused by: java.lang.NullPointerException
at
org.apache.cayenne.dba.mysql.MySQLProcedureAction.processResultSet(MySQLProcedureAction.java:88)
at
org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:71)
at
org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:306)
... 55 more
End of setLocalObject
2016-04-20 6:10 GMT+02:00 Fredrik Widengren <fr...@gmail.com>:
> 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 <an...@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.widengren@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)
>> >
>>
>>
>
Re: StoredProcedures and Cayenne
Posted by Fredrik Widengren <fr...@gmail.com>.
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 <an...@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.widengren@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)
> >
>
>
Re: StoredProcedures and Cayenne
Posted by Andrus Adamchik <an...@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 <fr...@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)
>