fetching refcursor results

fetching refcursor results

Postby bradk » Mon 15 Jan 2007 03:29

I'm trying to figure out how to get a datareader for a refcursor returned by a plpgsql proc:

CREATE OR REPLACE FUNCTION test_reffunc()
RETURNS refcursor AS
$BODY$
DECLARE
v_refcursor refcursor;

BEGIN
OPEN v_refcursor FOR SELECT col1 FROM test;
RETURN v_refcursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test_reffunc() OWNER TO postgres;

However, the IDataReader result from ExecuteReader just returns the name of the cursor: in a single row. Is there a way to have the fetch all accomplished by the corelab component on this cursor ? I am using the enteprise library components.
bradk
 
Posts: 34
Joined: Thu 20 Apr 2006 01:42

Postby Alexey » Mon 15 Jan 2007 08:01

If you execute your function in pgAdmin, you'll get the same results, so this is not PostgreSQLDirect .NET problem. For information on how to work with refcursors please refer to PostgreSQL help documentation.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby cool_m4rk » Sat 10 Feb 2007 07:18

Hi there brad! Still there? Maybe I can help you on your problem.. I don't know if the current version of corelab's component is capable of retrieving a refcursor's resultset, but i think you are still using their previous releases.

Anyway, about your problem, you can actually solve that by having 2 commands, 1 to execute your stored procedure that returns a refcursor, and the other 1 to fetch the results from the refcursor. But you must put these inside a transaction, since refcursor must be within a transaction to function properly (based on postgres documentation).. So based on your example, we can code this in VB.Net :


dim trans as pgsqltransaction ' must be on a transaction
trans = conn.begintransaction()

dim command1 as new pgsqlcommand("test_reffunc",conn)
command1.commandtype = commandtype.storedprocedure
command1.prepare()
command1.executenonquery()

dim command2 as new pgsqlcommand("Fetch all from v_refcursor",conn)
dim reader as pgsqldatareader = command2.executereader()

'-- do some code here --'

trans.commit ' <-- dont forget to commit

' -- Continue your code here --'

Hope this helps.. :wink:

To the corelab team, hope you had done something about this issue.. 8)
cool_m4rk
 
Posts: 3
Joined: Sat 10 Feb 2007 03:21

Postby Alexey » Wed 21 Feb 2007 13:56

Hello, bradk.
Does this code help you. It produces an error on my end (cursor "v_refcursor" does not exist).
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby cool_m4rk » Fri 23 Feb 2007 11:09

You will have to revise a slight change on the code for it to work properly. Just find a way to call the refcursor's name. To be able to do this, you should change your stored procedure code like this:

CREATE OR REPLACE FUNCTION test_reffunc(ref_cursor)
RETURNS refcursor AS
$BODY$

BEGIN
OPEN $1 FOR SELECT col1 FROM test;
RETURN $1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test_reffunc(ref_cursor) OWNER TO postgres;


Then change your code in .net like this:

dim command1 as new pgsqlcommand("Select * from test_reffunc('v_refcursor')")

Hope this code help you... :D
cool_m4rk
 
Posts: 3
Joined: Sat 10 Feb 2007 03:21

Postby Alexey » Fri 23 Feb 2007 11:24

What is "ref_cursor"? If I execute this creation statement with pgAdmin, I get the error: "type ref_cursor does not exist". Please advise.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby cool_m4rk » Sat 24 Feb 2007 02:11

Ok, sorry.. Its just refcursor ... Remove the _ ... :D
cool_m4rk
 
Posts: 3
Joined: Sat 10 Feb 2007 03:21

Postby Alexey » Thu 01 Mar 2007 08:42

This time it works.
Thanks for your consideration.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL