Re: calling a stored function which return set of records

You are creating the CallableStatement wrong.
c.prepareCall("{ call get_recipe_kcal( ?,?,?,?,?,? ) }")

Read more here:
http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/callablestatement.html#1003293

I suggest reading the whole thing here:
http://java.sun.com/javase/6/docs/technotes/guides/jdbc/

Szabó Tamás wrote:
>
> 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!
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

--
View this message in context: http://www.nabble.com/calling-a-stored-function-which-return-set-of-records-tp24430047p24445698.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.