2 function returns refcursor:
CREATE OR REPLACE FUNCTION per_getjobmind(puid bigint)
RETURNS refcursor AS
$BODY$
declare
curs cursor for select workname,workold from per_jobmind where
uid=puid;
begin
open curs;
return curs;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION per_getjobmind_jobtype(puid bigint)
RETURNS refcursor AS
$BODY$
declare
curs cursor for select jobtypeid from per_jobmind_jobtype where
uid=puid;
begin
open curs;
return curs;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-------------------------------------------------------------------
java code:
conn.setAutoCommit(false);
cmd = conn.prepareCall("{?=call per_getjobmind(?)}");
cmd.registerOutParameter(1, Types.OTHER);
cmd.setLong(2, uid);
cmd.execute();
rs = (ResultSet) cmd.getObject(1);
if (rs.next()) {//just 1 line
workname = rs.getString("workname");
workold = rs.getInt("workold");
}
rs.close();
cmd.close();
cmd = conn.prepareCall("{?=call per_getjobmind_jobtype(?)}");
cmd.registerOutParameter(1, Types.OTHER);
cmd.setLong(2, uid);
cmd.execute();////////////////////////////////// error here," cursor
"curs" already in use"
rs = (ResultSet) cmd.getObject(1);
while (rs.next()) {
jobtypeid=rs.getInt("jobtypeid");
}
rs.close();
cmd.close();
-----------------------------------------------------------------------
Can I named a refcursor in JDBC? How?
