cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Fredrik Widengren <fredrik.wideng...@gmail.com>
Subject Fwd: StoredProcedures and Cayenne
Date Thu, 14 Apr 2016 20:22:03 GMT
Reposting my question, just in case.

---------- Forwarded message ----------
From: Fredrik Widengren <fredrik.widengren@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)

Mime
  • Unnamed multipart/mixed (inline, None, 0 bytes)
View raw message