Re: [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA - Mailing list pgsql-general

Well Vladimir, i copy here only the part used in the java program to connect, set the parameters and execute the call
tothe 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,
charactervarying, integer, date) TO public;
 
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying,
charactervarying, integer, date) TO usr_dw_bsc_sys_adm;
 
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying,
charactervarying, 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
promiseto be more effective next time !!!!!
 

As I understand, it's all what you need, isn't you ????

-----Mensaje original-----
De: Vladimir Sitnikov [mailto:sitnikov.vladimir@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
suiteas a regression test.
 
Vladimir