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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | JAlexoid:Aleksandr Panzin | 2009-07-12 03:57:26 | Re: calling a stored function which return set of records |
Previous Message | User | 2009-07-07 14:52:12 | Re: setQueryTimeout |