CODEWITHSUNDEEP
javastored-proceduresapache-cayenne
Pefero
Pefero

Reputation: 11

Launching stored procedure which returns data via Apache Cayenne ORM does not work

I'm trying to launch a stored procedure in my MariaDB database. I'm using Apache Cayenne as ORM. The error message indicates that it can't find the stored procedure, but I have this working already for procedures that do not return data. Anyone familiar with stored procedures and Apache Cayenne that can give some guiding?

The stored procedure is available in the datamap.map.xml file that Cayenne uses:

<procedure name="copyRecipe" catalog="foodbase" returningValue="true">
    <procedure-parameter name="id_in" type="INTEGER" direction="in"/>
    <procedure-parameter name="id_new" type="INTEGER" direction="out"/>
</procedure>

I use the following code to launch the SP.

  public void copyRecipes() {
        ProcedureQuery query = new ProcedureQuery("copyRecipe");

        // Set "IN" parameter values
        query.addParameter("id_in", recipes.getId());

        // run query
        System.out.println("TEst 1");
        try {
        QueryResponse result = context.performGenericQuery(query);

        System.out.println("Test 2");

        for (result.reset(); result.next();) {
            if (result.isList()) {
                List<String> objIdList = (List<String>) result.currentList();
                Iterator<String> it = objIdList.iterator();


                recipes = getRecipes(it.next());
            } else {
                FacesContext context = FacesContext.getCurrentInstance();

                context.addMessage(null, new FacesMessage("Copy recipe failed"));

            }
        }

        } catch (Exception e) {
            FacesContext context = FacesContext.getCurrentInstance();

            context.addMessage(null, new FacesMessage("Copy recipe failed"));
        }



  }

When launching the method copyRecipes(), Tomcat returns the following error message:

TEst 1
mar 30, 2016 6:16:12 FM org.apache.cayenne.log.CommonsJdbcEventLogger logBeginTransaction
INFO: --- transaction started.
mar 30, 2016 6:16:12 FM org.apache.cayenne.log.CommonsJdbcEventLogger logQuery
INFO: {? = call copyRecipe(?)} [bind: 1:425, 2:'[OUT]']
mar 30, 2016 6:16:12 FM org.mariadb.jdbc.internal.mysql.MySQLProtocol getResult
WARNING: Could not execute query select copyRecipe(@_jdbc_var_2) into @_jdbc_var_1: FUNCTION foodbase.copyRecipe does not exist
mar 30, 2016 6:16:12 FM org.apache.cayenne.log.CommonsJdbcEventLogger logQueryError
INFO: *** error.
java.sql.SQLSyntaxErrorException: FUNCTION foodbase.copyRecipe does not exist
    at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:138)
    at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
    at org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:252)
    at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:278)
    at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:369)
    at org.mariadb.jdbc.MySQLCallableStatement.execute(MySQLCallableStatement.java:1251)
    at org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:58)
    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.RecepieController.copyRecipes(RecepieController.java:172)
    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.UICommand.broadcast(UICommand.java:315)
    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: org.mariadb.jdbc.internal.common.QueryException: FUNCTION foodbase.copyRecipe does not exist
    at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:984)
    at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1038)
    at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1020)
    at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:271)
    ... 57 more

EDIT: Here's how the stored procedure is defined:

CREATE DEFINER=`foodbase_admin`@`localhost` PROCEDURE `copyRecipe`(IN `id_in` INT, OUT `id_new` INT)
    NO SQL
    COMMENT 'Copy a recipe w associated ingredients, structure and categories'
BEGIN
#DECLARE id_new INT DEFAULT 0;

INSERT INTO recipes (Username, ID_privileges,Comment,Name,No_of_servings,Instruction,Source,Rating) SELECT Username,ID_privileges,Comment,CONCAT(Name," - copy") AS Name,No_of_servings,Instruction,Source,Rating FROM recipes WHERE ID = id_in;

SELECT LAST_INSERT_ID() INTO id_new;

INSERT INTO ingredients (
 Username,ID_privileges,Comment,ID_recipes,ID_groceries,ID_units,Amount)
    SELECT Username,ID_privileges,Comment,id_new AS ID_recipes,ID_groceries,ID_units,Amount FROM
    ingredients WHERE ID_recipes = id_in;

INSERT INTO recipe_structures (
 Username,ID_privileges,Comment,ID_recipes_super,ID_recipes_sub,Factor,Date)
SELECT Username,ID_privileges,Comment,id_new AS ID_recipes_super,ID_recipes_sub,Factor,Date FROM recipe_structures
WHERE ID_recipes_super = id_in;

INSERT INTO recipe_categories (
    Username,ID_privileges,Comment,ID_recipes,ID_categories)
    SELECT Username,ID_privileges,Comment,id_new AS ID_recipes,ID_categories FROM recipe_categories WHERE ID_recipes = id_in;



END

EDIT2: I have this working for another procedure. It looks like this:

CREATE DEFINER=`foodbase_admin`@`localhost` PROCEDURE `copyStructRecipeIngredients`(IN `id_in` INT, IN `factor` DOUBLE, IN `shoplist_in` INT)
    NO SQL
    SQL SECURITY INVOKER
BEGIN

CALL StructRecipeWeighted(id_in,factor);

#SELECT * FROM structrecipesweighted;

DROP TABLE IF EXISTS tmptbl10;
DROP TABLE IF EXISTS tmptbl11;

CREATE TEMPORARY TABLE tmptbl10 (
Username VARCHAR(255),
ID_privileges int,
ID_groceries int,
ID_units int,
Amount double
    );

INSERT INTO tmptbl10 (Username, ID_privileges, ID_groceries, ID_units, Amount)
SELECT ingredients.Username AS Username, ingredients.ID_privileges AS ID_privileges, ingredients.ID_groceries AS ID_groceries, ingredients.ID_units AS ID_units, structrecipesweighted.Weight*ingredients.Amount AS Amount FROM ingredients
JOIN structrecipesweighted ON ingredients.ID_recipes = structrecipesweighted.ID
    ;

#SELECT * from tmptbl10;

CREATE TEMPORARY TABLE tmptbl11 (
Username VARCHAR(255),
ID_privileges int,
ID_groceries int,
ID_units int,
Amount double,
ID_statuses int
    );

INSERT INTO tmptbl11 (Username, ID_privileges, ID_groceries, ID_units, Amount, ID_statuses)
    SELECT tmptbl10.Username AS Username, tmptbl10.ID_privileges AS ID_privileges, tmptbl10.ID_groceries AS ID_groceries, tmptbl10.ID_units AS ID_units, tmptbl10.Amount AS Amount, groceries.ID_statuses AS ID_statuses
    FROM tmptbl10 JOIN groceries ON tmptbl10.ID_groceries = groceries.ID;

#SELECT * from tmptbl11;

INSERT INTO shoppinglist_items (Username, ID_privileges, ID_shoppinglists, ID_groceries, ID_units, ID_statuses, Amount) 
    SELECT Username AS Username, ID_privileges AS ID_privileges, shoplist_in AS ID_shoppinglists, ID_groceries AS ID_groceries, ID_units AS ID_units, ID_statuses AS ID_statuses, SUM(Amount) AS Amount FROM tmptbl11
    GROUP BY ID_groceries, ID_units
    ;

#SELECT * from shoppinglist_items WHERE ID_shoppinglists = shoplist_in;


END

I'm calling it in the same way:

@SuppressWarnings("unused")
private void copyIngredients(Shoppinglists sl, Recipes base) {

    try {

        ProcedureQuery query = new ProcedureQuery("copyStructRecipeIngredients");
        System.out.println("test e");

        // Set "IN" parameter values
        query.addParameter("id_in", base.getId());
        query.addParameter("factor", base.getNoOfServings());
        query.addParameter("shoplist_in", sl.getId());

        // run query
        QueryResponse result = contextShopList.performGenericQuery(query);
    } catch (Exception e) {
        FacesContext context = FacesContext.getCurrentInstance();

        context.addMessage(null, new FacesMessage("Creation of shopping list fail.\n"+e.getLocalizedMessage()));

    }

Upvotes: 0

Views: 442

Answers (2)

Pefero
Pefero

Reputation: 11

It seams to be some bug when calling stored procedures that return values. In the datamap.xml file, I removed the attribute called returningValue="true" on xml tag procedure. Then the stored procedure executes in the database.

Here's how I modified the datamap.xml file. Compare to the one in my question.

<procedure name="copyRecipe" catalog="foodbase">
    <procedure-parameter name="id_in" type="INTEGER" direction="in"/>
    <procedure-parameter name="id_new" type="INTEGER" direction="out"/>

</procedure>

Upvotes: 1

andrus_a
andrus_a

Reputation: 2563

Looks like Cayenne fails to append a catalog "foodbase" to the stored procedure during the call. I suspect this causes the error above. I opened a bug report in Cayenne. Will make sure it gets addressed. For now in your MySQL connection properties you can add the name of the catalog to the connection String. E.g. jdbc:mysql://127.0.0.1/foodbase . Hopefully you don't have stored procedures in multiple catalogs. Otherwise this won't solve the problem.

Upvotes: 0

Related Questions