Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Ok. I understand, to put there a pull request, I must to register into this webpage ?? -Mensaje original- De: Vladimir Sitnikov [mailto:sitnikov.vladi...@gmail.com] Enviado el: lunes, 14 de diciembre de 2015 10:53 a.m. Para: Corradini, Carlos CC: Kevin Grittner; Adrian Klaver; List; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA >As I understand, it's all what you need, isn't you Ideally I would like to see a pull request at https://github.com/pgjdbc/pgjdbc/pulls, however your code seems to be good enough so somebody else can pick it up, simplify a bit, and file a PR. Vladimir -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
I forgot one more thing ... The return I made in the stored function is : RETURNS RECORD AS ' Excuse me for the forget ... -Mensaje original- De: Kevin Grittner [mailto:kgri...@gmail.com] Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m. Para: Corradini, Carlos CC: Adrian Klaver; pgsql-j...@postgresql.org; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos wrote: > with your and Mr. Kevin explanations, the Java program have worked > fine and have printed the data obtained from a two cursors inside a > PostgreSQL Database Stored Function. > > Then, I can confirm that this version of DB ( 9.4 ) use the OUT > parameter with refcursors and works fine. The JDBC interface provided > by the Server Postgresql can read the data inserted into these two > cursors via a callablestatement.registeroutparameter. For the benefit of others who may later have a similar problem and find this thread, it would be great if you could provide a little self-contained example of a Java program which uses the technique that you settled on. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Yes, I was thinking to post my solution in this list for any who need to know how to receive more than one cursors from a function stored in a PostgreSQL database, but, living and working in Argentina, made an impossible work last week ( abnormal tasks to do are normal here ) , but now I have 5 minutes, then, I decided to post the java code. The solution is very, very simple having a stored function with, for example, 2 input parameters and 2 cursors returned, I do not use RETURNS SETOF REFCURSORS in the function, but I have use the key OUT as out parameter ( just I use in Oracle Stored Procedures ) in the function, then, in the java program, after made the connection to the database, I have used the CallableStatement setting the value of all the inputs parameters with the values and type needed in the function, and as for the data returned ( in this case the cursors ), I have used the REGISTEROUTPARAMETER ( this method is provided by the calllablestatement java class ) with each value returned ( in this case the cursors ) numbered from 1 for the first out parameter and n for the last, informing the type TYPES.OTHER too. After made the java call ( callablestatement.execute() ), I set a public ( in this example ) variable as a RECORDSET and assigning each cursor to each RECORDSET variable : I use the integer variable called num_cursor for the first returned and num_cursor_02 for the second ( je .. I have broken my mind and brain thinking for the perfect names ... ). rs = (ResultSet) cs.getObject(num_cursor); rs1 = (ResultSet) cs.getObject(num_cursor_02); the data obtained by the GETOBJECT method, being a cursor, must be parsed to a RECORDSET, and not, is very important this point, never use the name you gave to each cursor in the function stored, simply provide a number for which cursor want to put in each recordset variable. after, using a java loop as "while recordset.next()" method, you can extract each value with getxxx(namedvariable) returned into each cursor and it's all ... I hope I have been the most clear as my poor level of English could be Many thanks for all and specially to the postgresql community list !! -Mensaje original- De: Kevin Grittner [mailto:kgri...@gmail.com] Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m. Para: Corradini, Carlos CC: Adrian Klaver; pgsql-j...@postgresql.org; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos wrote: > with your and Mr. Kevin explanations, the Java program have worked > fine and have printed the data obtained from a two cursors inside a > PostgreSQL Database Stored Function. > > Then, I can confirm that this version of DB ( 9.4 ) use the OUT > parameter with refcursors and works fine. The JDBC interface provided > by the Server Postgresql can read the data inserted into these two > cursors via a callablestatement.registeroutparameter. For the benefit of others who may later have a similar problem and find this thread, it would be great if you could provide a little self-contained example of a Java program which uses the technique that you settled on. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
;, v_id; RAISE NOTICE 'v_name : %', v_name; RAISE NOTICE 'v_short_desc : %', v_short_desc; RAISE NOTICE 'v_descr : %', v_descr; RAISE NOTICE 'v_user_id : %',v_user_id; RAISE NOTICE 'v_fecha : %', v_fecha; insert into dw_bsc.perspective (name, short_desc, description, usr_id_ins, usr_date_ins, usr_id_upd, usr_date_upd) values (v_name, v_short_desc, v_descr, v_user_id, v_fecha, null, null); open perspectives_cursor FOR select 'ok. insert' as resultado1; c1 := perspectives_cursor; -- return next perspectives_cursor; open goals_persps_cursor FOR select 'null' as resultado2; c2 := goals_persps_cursor; -- return next goals_persps_cursor; else RAISE NOTICE 'El id pasado al procedure no es 0, imposible procesar INSERT !!!'; open perspectives_cursor FOR select 'NULL' as resultado1; c1 := perspectives_cursor; -- return next perspectives_cursor; open goals_persps_cursor FOR select 'null' as resultado2; c2 := goals_persps_cursor; -- return next goals_persps_cursor; end if; -- exception -- when others then -- raise notice 'Se dio el error número %',sqlstate,' con descripción %', sqlerrm; -- end; end case; end; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100; ALTER FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying, character varying, integer, date) OWNER TO usr_dw_bsc_sys_adm; GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying, character varying, integer, date) TO public; GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying, character varying, integer, date) TO usr_dw_bsc_sys_adm; GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying, character varying, integer, date) TO ro_dw_bsc_sys_adm; One more thing, I am a DBA ORACLE and not a Developer, please, excuse me if I made "horrors" in the programming, I promise to be more effective next time ! As I understand, it's all what you need, isn't you -Mensaje original- De: Vladimir Sitnikov [mailto:sitnikov.vladi...@gmail.com] Enviado el: lunes, 14 de diciembre de 2015 10:20 a.m. Para: Corradini, Carlos CC: Kevin Grittner; Adrian Klaver; List; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA > I hope I have been the most clear as my poor level of English could be.. It would be great if you could express that in java + sql as well, so the exact code can be added to JDBC driver test suite as a regression test. Vladimir -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Dear Mr. Adrian Well, I must be honest, with your and Mr. Kevin explanations, the Java program have worked fine and have printed the data obtained from a two cursors inside a PostgreSQL Database Stored Function. Then, I can confirm that this version of DB ( 9.4 ) use the OUT parameter with refcursors and works fine. The JDBC interface provided by the Server Postgresql can read the data inserted into these two cursors via a callablestatement.registeroutparameter. Many thanks -Mensaje original- De: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Enviado el: viernes, 11 de diciembre de 2015 12:27 p.m. Para: Corradini, Carlos; pgsql-j...@postgresql.org; pgsql-general@postgresql.org CC: bo...@ejurka.com Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA On 12/11/2015 07:10 AM, Corradini, Carlos wrote: > Mr. Adrian, first let me say many thanks for your replies, were very > helpful for me. But, I must to say this other . > > I take a copy from the function from the gui tool of pgadmin III > called query sql, the original function name all the parameters, I do > not know why this gui tool change that. I have no I idea either, seems silly to me. > > You say that I am returning only one cursor, but I can say that I am > doing exactly what are you explaining in your example, I am doing > return next for each cursor to return. From your previous post: CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives( character varying, integer, character varying, character varying, character varying, integer, date) RETURNS SETOF refcursor AS You use two cursors internally, but are returning only one above. Unless you do something like Kevin Grittner and I showed there is no way for Postgres to know how to assign the internal cursors to return values that can be used externally. > > I think I am doing something wrong in the java program but I can't > realize what is this .I will go now to see the examples in the > URL's you did put below. > > Many thanks for all and excuse me for disturbing you and make you > waste your free time in this. Well the point of the list is to answer questions and if I did not have the time or the interest I would not answer, so do not worry about it. > > Again, many thanks > > -Mensaje original- > De: Adrian Klaver [mailto:adrian.kla...@aklaver.com] > Enviado el: viernes, 11 de diciembre de 2015 11:37 a.m. > Para: Corradini, Carlos; pgsql-j...@postgresql.org; > pgsql-general@postgresql.org > CC: bo...@ejurka.com > Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF > refcursor in JAVA > > On 12/11/2015 04:56 AM, Corradini, Carlos wrote: >> Mr. Adrian, here i transcribe the code of the function > > Notes in line. > >> >> -- Function: dw_bsc.proc_perspectives(character varying, integer, >> character varying, character varying, character varying, integer, > date) >> >> -- DROP FUNCTION dw_bsc.proc_perspectives(character varying, integer, >> character varying, character varying, character varying, integer, > date); >> >> CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives( >> character varying, >> integer, >> character varying, >> character varying, >> character varying, >> integer, >> date) >> RETURNS SETOF refcursor AS > > First you can name your function parameters: > > http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.ht > ml > #PLPGSQL-DECLARATION-PARAMETERS > > 40.3.1. Declaring Function Parameters > > > If you do that then you can simplify the below. In other words in > above the first parameter becomes: > > v_oper varchar(1) > > Saves creating a NULL variable and assigning to it as below. > >> $BODY$ >> declare >> >> v_oper varchar(1) := null; >> v_id integer := null; >> v_name varchar(50) := null; >> v_short_desc varchar(150) := null; >> v_descr varchar(500) := null; >> v_user_id integer := null; >> v_fecha date := null; >> v_resu integer := null; >> perspectives_cursor refcursor := null; >> goals_persps_cursor refcursor := null; >> null_cursor refcursor := null; >> >> >> begin >> >> v_oper := $1; >> v_id := $2; >> v_name := $3; >> v_short_desc := $4; >> v_descr := $5; >> v_user_id := $6; >> v_fecha := $7; >> >> >> -- oper R = READ >> -- oper D = DELETE >> -- oper M = UPDATE >> -- oper I = INSERT > > > &g
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Mr. Adrian, first let me say many thanks for your replies, were very
helpful for me. But, I must to say this other .
I take a copy from the function from the gui tool of pgadmin III called
query sql, the original function name all the parameters, I do not know
why this gui tool change that.
You say that I am returning only one cursor, but I can say that I am
doing exactly what are you explaining in your example, I am doing return
next for each cursor to return.
I think I am doing something wrong in the java program but I can't
realize what is this .I will go now to see the examples in the URL's
you did put below.
Many thanks for all and excuse me for disturbing you and make you waste
your free time in this.
Again, many thanks
-Mensaje original-
De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Enviado el: viernes, 11 de diciembre de 2015 11:37 a.m.
Para: Corradini, Carlos; pgsql-j...@postgresql.org;
pgsql-general@postgresql.org
CC: bo...@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA
On 12/11/2015 04:56 AM, Corradini, Carlos wrote:
> Mr. Adrian, here i transcribe the code of the function
Notes in line.
>
> -- Function: dw_bsc.proc_perspectives(character varying, integer,
> character varying, character varying, character varying, integer,
date)
>
> -- DROP FUNCTION dw_bsc.proc_perspectives(character varying, integer,
> character varying, character varying, character varying, integer,
date);
>
> CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
> character varying,
> integer,
> character varying,
> character varying,
> character varying,
> integer,
> date)
>RETURNS SETOF refcursor AS
First you can name your function parameters:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html
#PLPGSQL-DECLARATION-PARAMETERS
40.3.1. Declaring Function Parameters
If you do that then you can simplify the below. In other words in above
the first parameter becomes:
v_oper varchar(1)
Saves creating a NULL variable and assigning to it as below.
> $BODY$
> declare
>
> v_oper varchar(1) := null;
> v_id integer := null;
> v_name varchar(50) := null;
> v_short_desc varchar(150) := null;
> v_descr varchar(500) := null;
> v_user_id integer := null;
> v_fecha date := null;
> v_resu integer := null;
> perspectives_cursor refcursor := null;
> goals_persps_cursor refcursor := null;
> null_cursor refcursor := null;
>
>
> begin
>
> v_oper := $1;
> v_id := $2;
> v_name := $3;
> v_short_desc := $4;
> v_descr := $5;
> v_user_id := $6;
> v_fecha := $7;
>
>
> -- oper R = READ
> -- oper D = DELETE
> -- oper M = UPDATE
> -- oper I = INSERT
>
> I can extract the data contained into cursor named perspectives_cursor
> in the java application, but the data caontained into cursor named
> goals_persps_cursor not.
Well you are only returning one refcursor, so that is all you are going
to get. To return more than one cursor, modified example from the docs:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html
See bottom of page for original example.
CREATE OR REPLACE FUNCTION public.myfunc(cur_a refcursor, cur_b
refcursor, arg_1 varchar(1))
RETURNS SETOF refcursor
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'arg_1 is %', arg_1;
OPEN cur_a FOR SELECT * FROM tbl_a;
RETURN NEXT $1;
OPEN cur_b FOR SELECT * FROM tbl_b;
RETURN NEXT $2;
END;
$function$
;
test=> begin ;
BEGIN
test=> SELECT * FROM myfunc('a', 'b', '1');
NOTICE: arg_1 is 1
myfunc
a
b
(2 rows)
test=> fetch all from a;
fld_1
---
1
2
3
(3 rows)
test=> fetch all from b;
fld_1
---
4
5
6
(3 rows)
See this post from Kevin Grittner for an alternate method:
http://www.postgresql.org/message-id/CACjxUsMy_zngFHBia+-QQuR8pOy87VU-L1
e6hppwndu2skj...@mail.gmail.com
He also includes some notes on how to make this work with JDBC.
>
> This function was tested by this
>
>
> begin;
> select * from dw_bsc.proc_perspectives('R', 1, null, null, null, null,
> null);
>
> fetch all from "";
> end;
>
> inside the query gui tool provided by pgAdmin III
>
> The connection into the java application was changed to
> con.setAutoCommit(false);
>
> I think I do not forget nothing else
>
> Some help will be appreciated very, very, very much !
>
>
> -Mensaje original-
> De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
> Para: Corradini, Carlos; pgsql-j...@postgresql.
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
update DW_BSC.PERSPECTIVE p
set p.NAME = v_name,
p.DESCRIPTION = v_descr,
p.SHORT_DESC = v_short_desc,
p.USR_ID_UPD = v_user_id,
p.USR_DATE_UPD = v_fecha
where P.ID = v_id;
open perspectives_cursor FOR select 'ok.
update' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
end if;
when 'I' then
if (v_id = 0) then
RAISE NOTICE 'v_name : %', v_name;
RAISE NOTICE 'v_short_desc : %',
v_short_desc;
RAISE NOTICE 'v_descr : %', v_descr;
RAISE NOTICE 'v_user_id : %',v_user_id;
RAISE NOTICE 'v_fecha : %', v_fecha;
insert into dw_bsc.perspective
(name, short_desc, description,
usr_id_ins, usr_date_ins, usr_id_upd, usr_date_upd)
values
(v_name, v_short_desc, v_descr,
v_user_id, v_fecha, null, null);
open perspectives_cursor FOR select 'ok.
insert' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
else
RAISE NOTICE 'El id pasado al procedure
no es 0, imposible procesar INSERT !!!';
open perspectives_cursor FOR select
'NULL' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
end if;
end case;
end;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100
ROWS 1000;
ALTER FUNCTION dw_bsc.proc_perspectives(character varying, integer,
character varying, character varying, character varying, integer, date)
OWNER TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO public;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO ro_dw_bsc_sys_adm;
I can extract the data contained into cursor named perspectives_cursor
in the java application, but the data caontained into cursor named
goals_persps_cursor not.
This function was tested by this
begin;
select * from dw_bsc.proc_perspectives('R', 1, null, null, null, null,
null);
fetch all from "";
end;
inside the query gui tool provided by pgAdmin III
The connection into the java application was changed to
con.setAutoCommit(false);
I think I do not forget nothing else
Some help will be appreciated very, very, very much !
-Mensaje original-
De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
Para: Corradini, Carlos; pgsql-j...@postgresql.org;
pgsql-general@postgresql.org
CC: bo...@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA
On 12/10/2015 05:38 AM, Corradini, Carlos wrote:
> Dear Gurus :
>
> First let me say hello from Buenos Aires, Argentina.
> I took this emails addresses from internet ( page www.postgresql.org
> <http://www.postgresql.org> )
>
> Now I will try to explain which is
[GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Dear Gurus : First let me say hello from Buenos Aires, Argentina. I took this emails addresses from internet ( page www.postgresql.org ) Now I will try to explain which is my problem ( excuse my poor level of English, please ). I have a Java application that must read a data provided by two ( 2 ) cursors returned by a function stored in a database. I know to retrieve data if the function have one ( 1 ) cursor, but with two I can't. I will very pleased if any of you, in your free time of course, can explain me how, inside the java program, after connecting via jdbc to the database, I extract the data returned by the second cursor. Many thanks ( muchas gracias ) and I wait for yours replies as soon as you can.
