Re: calling a stored function which return set of records - Mailing list pgsql-jdbc
From | Szabó Tamás |
---|---|
Subject | Re: calling a stored function which return set of records |
Date | |
Msg-id | 4A599B14.4090002@hdsnet.hu Whole thread Raw |
In response to | Re: calling a stored function which return set of records ("JAlexoid:Aleksandr Panzin" <jalexoid@gmail.com>) |
List | pgsql-jdbc |
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 >> >> >> > >
pgsql-jdbc by date: