Skip site navigation (1) Skip section navigation (2)

calling a stored function which return set of records

From: Szabó Tamás <szabta(at)hdsnet(dot)hu>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: calling a stored function which return set of records
Date: 2009-07-10 15:43:52
Message-ID: 4A5761B8.7030401@hdsnet.hu (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hello!

I have a stored function in a postgresql databse, and I want to call it 
from my java program.

The stored function is like this:

CREATE OR REPLACE FUNCTION get_recipe_data(recipe_id integer,
   OUT recipe_id integer,
   OUT kcal real,
   OUT kj real,
   OUT protein real,
   OUT fat real,
   OUT carbohydrates real
) RETURNS SETOF record

    AS $$

BEGIN

RETURN QUERY
SELECT recipes_ingredients_conn.recipe_id,
  sum(recipes_ingredients_conn.amount / ingredients.amount * 
ingredients.kcal),
  sum(recipes_ingredients_conn.amount / ingredients.amount * 
ingredients.kj),
  sum(recipes_ingredients_conn.amount / ingredients.amount * 
ingredients.protein),
  sum(recipes_ingredients_conn.amount / ingredients.amount * 
ingredients.fat),
  sum(recipes_ingredients_conn.amount / ingredients.amount * 
ingredients.carbohydrates)

FROM recipes_ingredients_conn, ingredients
WHERE (recipes_ingredients_conn.ingredient_id = 
ingredients.ingredient_id) AND (recipes_ingredients_conn.recipe_id = 
recipe_id)
GROUP BY recipes_ingredients_conn.recipe_id;
END

$$

    LANGUAGE plpgsql;


The code sheet from my java program is like:

...
// Turn transactions off.
            c.setAutoCommit(false);
            // Procedure call, i don't know if this is the right way to 
define the stored function
           // Maybe do i use a refcursor or something like this???
            CallableStatement upperProc = c.prepareCall("{ (?,?,?,?,?,?) 
= call get_recipe_kcal( ? ) }");
           
            upperProc.registerOutParameter(1, Types.INTEGER);
            upperProc.registerOutParameter(2, Types.REAL);
            upperProc.registerOutParameter(3, Types.REAL);
            upperProc.registerOutParameter(4, Types.REAL);
            upperProc.registerOutParameter(5, Types.REAL);
            upperProc.registerOutParameter(6, Types.REAL);

            upperProc.setInt(7, 1);
           
            upperProc.execute();
            double i = upperProc.getDouble(3);
           
            System.out.println(i);
            upperProc.close();
...

When I try to run the java program I get the following error message: 
Error: Malformed function or procedure escape syntax at offset 2.
I don't really know what I'm doing wrong, i read through some articles 
about the problem, but I couldn't find a solution.
Please help me If you can. Thnaks!


Responses

pgsql-jdbc by date

Next:From: JAlexoid:Aleksandr PanzinDate: 2009-07-12 03:57:26
Subject: Re: calling a stored function which return set of records
Previous:From: UserDate: 2009-07-07 14:52:12
Subject: Re: setQueryTimeout

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group