calling a stored function which return set of records
От | Szabó Tamás |
---|---|
Тема | calling a stored function which return set of records |
Дата | |
Msg-id | 4A5761B8.7030401@hdsnet.hu обсуждение исходный текст |
Ответы |
Re: calling a stored function which return set of records
|
Список | 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!
В списке pgsql-jdbc по дате отправления: