Re: [GENERAL] plpgsql function with RETURNS SETOF refcursor in JAVA

Well Vladimir, i copy here only the part used in the java program to connect, set the parameters and execute the call to the stored function ( I show how I manage both oracle and postgresql, but advises to better programming are welcome !!!!) :

1) connecting, setting the parameters and execute the CallableStatement

con = DriverManager.getConnection(DB_URL+DB_NAME, clsUserName, clsUserPass);

switch(DBAccess) {
case "ORACLE":
num_cursor = 8;
num_cursor_02 = 9;
call_proc_DB = "{call dw_bsc.proc_perspectives(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
cs = con.prepareCall(call_proc_DB);
cs.setString(1, operac);
cs.setInt(2, pId);
cs.setNull(3, java.sql.Types.NULL);
cs.setNull(4, java.sql.Types.NULL);
cs.setNull(5, java.sql.Types.NULL);
cs.setNull(6, java.sql.Types.NULL);
cs.setNull(7, java.sql.Types.NULL);
cs.registerOutParameter(8, OracleTypes.CURSOR);
cs.registerOutParameter(9, OracleTypes.CURSOR);
cs.registerOutParameter(10, OracleTypes.INTEGER);
break;
case "POSTGRE":
num_cursor = 1;
num_cursor_02 = 2;
call_proc_DB = "{call dw_bsc.proc_perspectives(?, ?, ?, ?, ?, ?, ?)}";
con.setAutoCommit(false);
cs = con.prepareCall(call_proc_DB);
cs.setString(1, operac);
cs.setInt(2, pId);
cs.setNull(3, java.sql.Types.VARCHAR);
cs.setNull(4, java.sql.Types.VARCHAR);
cs.setNull(5, java.sql.Types.VARCHAR);
cs.setNull(6, java.sql.Types.INTEGER);
cs.setNull(7, java.sql.Types.DATE);
cs.registerOutParameter(1, Types.OTHER);
cs.registerOutParameter(2, Types.OTHER);
break;
}
cs.execute();
rs = (ResultSet) cs.getObject(num_cursor);
rs1 = (ResultSet) cs.getObject(num_cursor_02);

2) looping for extract the data in the cursors

try {
while(rs.next()) {
txtPerspName.setText(rs.getString("name"));
taShortDesc.setText(rs.getString("short_desc"));
taDescription.setText(rs.getString("description"));
}
operac = "M";
while(rs1.next()) {
tblGoalsPerspModel.addRow(new Object[tblPerspGoals.getRowCount()]);
tblGoalsPerspModel.setValueAt((tblPerspGoals.getRowCount()), (tblPerspGoals.getRowCount()-1), 0);
tblGoalsPerspModel.setValueAt(rs1.getString("id"), (tblPerspGoals.getRowCount()-1), 1);
tblGoalsPerspModel.setValueAt(rs1.getString("description"), (tblPerspGoals.getRowCount()-1), 2);
}
} catch (SQLException sqle) {
Logger.getLogger(frmSetPerspectives.class.getName()).log(Level.SEVERE, null, sqle);
} catch (Exception ex) {
hacer_igual = "I";
}
3) the sql postgre code for the stored function I have created

CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
IN character varying,
IN integer,
IN character varying,
IN character varying,
IN character varying,
IN integer,
IN date,
OUT c1 refcursor,
OUT c2 refcursor)
RETURNS record AS
$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 := 'perspectives_cursor';
goals_persps_cursor refcursor := 'goals_persps_cursor';

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

case v_oper
when 'R' then
-- begin
if (v_id = 0) then
RAISE NOTICE 'Estoy en el select sin filtros';
OPEN perspectives_cursor FOR
SELECT p.id, p.name, p.short_desc, p.description
FROM dw_bsc.perspective p
order by p.name asc;
-- return next perspectives_cursor;
c1 := perspectives_cursor;
open goals_persps_cursor FOR select 'null' as resultado2;
-- return next goals_persps_cursor;
c2 := goals_persps_cursor;

else
RAISE NOTICE 'Estoy en el select de un solo id';
OPEN perspectives_cursor FOR
SELECT p.name, p.short_desc, p.description
FROM dw_bsc.perspective p
WHERE P.ID = v_id;
-- return next perspectives_cursor;
c1 := perspectives_cursor;


OPEN goals_persps_cursor FOR
SELECT GP.ID, GP.DESCRIPTION
FROM DW_BSC.GOALS_PERSPECTIVE gp
WHERE GP.PER_ID = v_id;
-- return next goals_persps_cursor;
c2 := goals_persps_cursor;

end if;
-- end;
when 'D' then
-- begin
if (v_id = 0) then
RAISE NOTICE 'El id pasado al procedure es null, imposible procesar DELETE!!!';
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;

else
RAISE NOTICE 'Estoy en el delete con id : %', v_id;
RAISE NOTICE 'Borrando GOALS ....';
delete from DW_BSC.GOALS_PERSPECTIVE gp
WHERE GP.PER_ID = v_id;
RAISE NOTICE 'Borrando PERSPECTIVE ....';
delete from DW_BSC.PERSPECTIVE p
WHERE P.ID = v_id;
open perspectives_cursor FOR select 'ok. delete perspectives' as resultado1;
c1 := perspectives_cursor;
-- return next perspectives_cursor;
open goals_persps_cursor FOR select 'ok. Delete goals' as resultado2;
c2 := goals_persps_cursor;
-- return next goals_persps_cursor;

end if;
-- end;
when 'M' then
-- begin
if (v_id = 0) then
RAISE NOTICE 'El id pasado al procedure es null, imposible procesar UPDATE !!!';
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;

else
RAISE NOTICE 'Estoy en el update con id : %', v_id;
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;
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;
-- end;
when 'I' then
-- begin
if (v_id = 0) then
RAISE NOTICE 'Estoy en el insert con id : %', 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(dot)vladimir(at)gmail(dot)com]
Enviado el: lunes, 14 de diciembre de 2015 10:20 a.m.
Para: Corradini, Carlos
CC: Kevin Grittner; Adrian Klaver; List; pgsql-general(at)postgresql(dot)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