refcursor

fredbest · Post by **fredbest** » Thu 15 Apr 2010 02:52

Hi Guys
I have the following function

CREATE OR REPLACE FUNCTION getcategories() RETURNS refcursor AS
DECLARE cur_OUT refcursor;
BEGIN
	OPEN cur_OUT  FOR
	SELECT  wc.*,'' as CATEGORY
        FROM "WORKFLOWCATEGORY" WC  
	Where wc."WORKFLOWCATEGORY_ID" is null 
	order by wc."SHORTDESCRIPTION";
	RETURN cur_OUT;
END;   
LANGUAGE 'plpgsql'

I am using Enterprise Libraries and the following code in c#.net

Code: Select all

 Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "GETCATEGORIES";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
DataSet ds = db.ExecuteDataSet(dbCommand);

I get this error
cursor "" does not exist

What Am I doing wrong?? Please Help

using devart trial version - devart.Data.dll version is 5.0.81.0

TIA

StanislavK · Post by **StanislavK** » Thu 15 Apr 2010 09:32

Please try starting a transaction on your connection before retrieving the cursor:

Code: Select all

dbCommand.Connection.BeginTransaction();

Please tell us if this helps.

fredbest · Post by **fredbest** » Fri 16 Apr 2010 01:30

Why do I need a transaction??
I am using common piece of code for Oracle, SQL Server & PGSQL

StanislavK · Post by **StanislavK** » Fri 16 Apr 2010 11:48

This is the problem specific for PostgreSQL. The PostgreSQL portal which is created for the cursor lives inside a transaction only. If no transaction is opened explicitly, a single transaction implicitly starts for each statement being executed. In this case, the portal which was created on the stored procedure execution, will be unavailable after this operation is over.