Error when calling stored procedure in code

Error when calling stored procedure in code

Postby 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.
oana
 
Posts: 7
Joined: Tue 15 Nov 2016 11:37

Re: Error when calling stored procedure in code

Postby 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/postgresql/docs/?Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlCursor.html
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44

Re: Error when calling stored procedure in code

Postby oana » Mon 22 May 2017 06:06

Thanks a lot. It works.
oana
 
Posts: 7
Joined: Tue 15 Nov 2016 11:37


Return to dotConnect for PostgreSQL