Thread: calling a stored function which return set of records

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!

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@postgresql.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.

Now I modified my java code :
            c.setAutoCommit(false);
            // Procedure call.
            CallableStatement cs = (CallableStatement) c.prepareCall("{
call ? = get_recipe_data[(?,?,?,?,?,?,?)] }");
            cs.setInt(1, 1);
            cs.registerOutParameter(2, Types.INTEGER);
            cs.registerOutParameter(3, Types.FLOAT);
            cs.registerOutParameter(4, Types.FLOAT);
            cs.registerOutParameter(5, Types.FLOAT);
            cs.registerOutParameter(6, Types.FLOAT);
            cs.registerOutParameter(7, Types.FLOAT);
            cs.execute();

            cs.close();

Now I get an error message that parameter 8 is not specified, which I
understand beacuse there are 8 placeholders ( "?" ) , but I can't find
out what should i define for the type of SET OF RECORDS as the functions
returns this type of data.

However the stored procedure called from postgresql client program works
well.
> 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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>>
>
>