Reposting my question, just in case.
---------- Forwarded message ----------
From: Fredrik Widengren <[email protected]>
Date: 2016-04-14 22:17 GMT+02:00
Subject: StoredProcedures and Cayenne
To: [email protected]
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)
