Error when calling stored procedure in code

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
oana
Posts: 7
Joined: Tue 15 Nov 2016 11:37

Error when calling stored procedure in code

Post by oana » Wed 17 May 2017 10:40

Hi,

I am trying to call a stored procedure from code and I get this error:
Error: 34000: cursor "<unnamed portal 1>" does not exist

My stored procedure:

Code: Select all

CREATE OR REPLACE FUNCTION public.getteamwork2(
	ref refcursor)
    RETURNS refcursor
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE NOT LEAKPROOF 
AS $function$

    BEGIN
      OPEN ref FOR 
      	select tw.id, tw.date, tw.recipecode, sum(twd.quantity) as quantFinished, sum(tw.quantity) as quantInWork, 
            tw.idorder, tw.idemployee, tw.idteam, tw.itemcodeparent  from teamwork tw
            left join teamworkdetail twd on twd.idteamwork = tw.id
            group by twd.idteamwork, tw.id, tw.date, tw.recipecode, tw.idorder, tw.idemployee, tw.idteam, tw.itemcodeparent
            order by date desc;
      RETURN ref;                                                       -- Return the cursor to the caller
    END;
    
$function$;
My code:

Code: Select all

PgSqlConnection pgSqlConn = new PgSqlConnection();
pgSqlConn.ConnectionString = "User Id=xxx;Password=xxx;Host=xxx;database=xxx";
pgSqlConn.Open();

PgSqlCommand command = new PgSqlCommand("GetTeamWork2", pgSqlConn);
command.CommandType = CommandType.StoredProcedure;
command.ParameterCheck = true;
var reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);                    
Can you please help? Thanks in advance.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Error when calling stored procedure in code

Post by Pinturiccio » Fri 19 May 2017 13:03

PgSqlTransaction is required when your procedure returns a ref cursor value. Please replace your code with the following:

Code: Select all

PgSqlConnection pgSqlConn = new PgSqlConnection();
pgSqlConn.ConnectionString = "User Id=xxx;Password=xxx;Host=xxx;database=xxx";
pgSqlConn.Open();

PgSqlCommand command = new PgSqlCommand("GetTeamWork2", pgSqlConn);
command.CommandType = CommandType.StoredProcedure;
command.ParameterCheck = true;
PgSqlTransaction trans = conn.BeginTransaction();
command.Transaction = trans;
var reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
For more information, please refer to https://www.devart.com/dotconnect/postg ... ursor.html

oana
Posts: 7
Joined: Tue 15 Nov 2016 11:37

Re: Error when calling stored procedure in code

Post by oana » Mon 22 May 2017 06:06

Thanks a lot. It works.

Post Reply