refcursor

refcursor

Postby fredbest » Thu 15 Apr 2010 02:52

Hi Guys
I have the following function

Code: Select all
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
fredbest
 
Posts: 4
Joined: Thu 15 Apr 2010 02:19

Postby 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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby 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
fredbest
 
Posts: 4
Joined: Thu 15 Apr 2010 02:19

Postby 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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for PostgreSQL