Thread: Refcursor bug

Hi,
 I encountered a bug with refcursors with the latest JDBC driver. I cannot get two refcursors as OUT parameter if both of them point to the same underlying refcursor.

Steps to produce:
1- Create table:
CREATE TABLE testref ( a  int4, b  varchar);

2-Create the function:
CREATE OR REPLACE FUNCTION reftwo(
 A OUT REFCURSOR, B OUT REFCURSOR)
RETURNS record AS $$  DECLARE
  V_REF REFCURSOR;
BEGIN
     OPEN V_REF FOR SELECT * FROM testref;
     A:=V_REF;
     B:=V_REF;
END;  $$ LANGUAGE 'plpgsql'

3- Run the following code:

        con.setAutoCommit(false);
        CallableStatement stm = con.prepareCall("{call reftwo(?,?)}");       
        stm.registerOutParameter(1,Types.OTHER);
        stm.registerOutParameter(2,Types.OTHER);      
        stm.execute();      
        ResultSet rs = (ResultSet) stm.getObject(1);
        ResultSet rs2 = (ResultSet) stm.getObject(2);      
        con.setAutoCommit(true);

When i execute this code, it reports the following problem:
org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

Am i doing something wrong?

--Altaf Malik

---

Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.

Did this work in previous versions ?

Yes this worked in previous versions.

As you close the cursor say '<unnamed portal 1>' when you fetch it, it closes the underlying cursor. And the send variable also points to the same variable, and you send a FETCH ALL..... which throws exception because cursor has already been closed.

--Altaf

Dave Cramer <pg@fastcrypt.com> wrote:

Did this work in previous versions ?

I made a change recently which closes the refcursor immediately after reading it, because it was leaking on large batches

Dave

On 27-Apr-07, at 8:20 AM, Altaf Malik wrote:

Hi,
 I encountered a bug with refcursors with the latest JDBC driver. I cannot get two refcursors as OUT parameter if both of them point to the same underlying refcursor.

Steps to produce:
1- Create table:
CREATE TABLE testref ( a  int4, b  varchar);

2-Create the function:
CREATE OR REPLACE FUNCTION reftwo(
 A OUT REFCURSOR, B OUT REFCURSOR)
RETURNS record AS $$  DECLARE
  V_REF REFCURSOR;
BEGIN
     OPEN V_REF FOR SELECT * FROM testref;
     A:=V_REF;
     B:=V_REF;
END;  $$ LANGUAGE 'plpgsql'

3- Run the following code:

        con.setAutoCommit(false);
        CallableStatement stm = con.prepareCall("{call reftwo(?,?)}");       
        stm.registerOutParameter(1,Types.OTHER);
        stm.registerOutParameter(2,Types.OTHER);      
        stm.execute();      
        ResultSet rs = (ResultSet) stm.getObject(1);
        ResultSet rs2 = (ResultSet) stm.getObject(2);      
        con.setAutoCommit(true);

When i execute this code, it reports the following problem:
org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

Am i doing something wrong?

--Altaf Malik

---

Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.

---

Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.

On Fri, 27 Apr 2007, Altaf Malik wrote:

> Hi, I encountered a bug with refcursors with the latest JDBC driver. I
> cannot get two refcursors as OUT parameter if both of them point to the
> same underlying refcursor.
>

Is this an actual problem or just something you came up with to make it
fail?  I don't see the use case here because the previous driver version
would return the full ResultSet and then an empty one because everything
had been fetched from it.  So that would be pretty useless.

Kris Jurka

Yes it is useless but at least we cannot prevent the user from doing it. Does it mean we should throw exception and not allow user to get even the first resultset? Because exception comes internally when processing the resultset.

--Altaf

Kris Jurka <books@ejurka.com> wrote:

On Fri, 27 Apr 2007, Altaf Malik wrote:

> Hi, I encountered a bug with refcursors with the latest JDBC driver. I
> cannot get two refcursors as OUT parameter if both of them point to the
> same underlying refcursor.
>

Is this an actual problem or just something you came up with to make it
fail? I don't see the use case here because the previous driver version
would return the full ResultSet and then an empty one because everything
had been fetched from it. So that would be pretty useless.

Kris Jurka


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

---

Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.