Re: StoredProcedures not working

[dollj]

Hi Fredrik

It would appear from the exception that the procedure can't be found in the 
database.
Are you sure that you have created the procedure in foodbase and that the 
spelling is the same ?

Regards
Jurgen


-----Original Message----- 
From: Fredrik Widengren
Sent: Tuesday, March 15, 2016 7:44 AM
To: user@cayenne.apache.org
Subject: StoredProcedures not working

Hello,
Has anyone experience of this issue? I'm executing a stored procedure from
via cayenne.

I have succeeded with another one, which is workign correctly. That one
does nto return any data.

However, when I try to run this procedure I get the error below. It seam
like it does not find the procedure.

I call the procedure according to the documentation:
(the "Test 2" code is never printed in the out stream).

The procedure is available in the datamap.xml file:
   <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>


Any ideas?

Many thanks,
/Fredrik


           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) {


TEst 1
mar 15, 2016 6:36:23 FM org.apache.cayenne.log.CommonsJdbcEventLogger
logBeginTransaction
INFO: --- transaction started.
mar 15, 2016 6:36:23 FM org.apache.cayenne.log.CommonsJdbcEventLogger
logQuery
INFO: {? = call copyRecipe(?)} [bind: 1:30, 2:'[OUT]']
mar 15, 2016 6:36:23 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 15, 2016 6:36:23 FM org.apache.cayenne.log.CommonsJdbcEventLogger
logQueryError
INFO: *** error.
java.sql.SQLSyntaxErrorException: FUNCTION foodbase.copyRecipe does not
exist