cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: StoredProcedures and Cayenne
Date Tue, 19 Apr 2016 07:12:30 GMT
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)
> 


Mime
View raw message