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.
fetching refcursor results
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..
To the corelab team, hope you had done something about this issue..
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..
To the corelab team, hope you had done something about this issue..
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...
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...