setof refcursor function problem

setof refcursor function problem

Postby aegeavaz » Mon 16 Apr 2012 08:33

Hi, we are using a postgresql function that returns two refcursors. The first time we execute the function from .NET code (under a transaction), it works fine and retrieve two result sets correctly. But when we execute a second time, we get the "" problem.

Table:

Code: Select all
CREATE TABLE t1
(
  c1 integer,
  c2 character varying(20)
)
WITH (
  OIDS=FALSE
);


Function:

Code: Select all
CREATE OR REPLACE FUNCTION getmultiple()
  RETURNS SETOF refcursor AS
$BODY$
  DECLARE
  ref1 refcursor;
  ref2 refcursor;
BEGIN
   OPEN ref1 FOR
   SELECT * FROM T1;
   OPEN ref2 FOR
   SELECT * FROM T1;

   return next ref1;
   return next ref2;
   return;
END;
$BODY$
  LANGUAGE plpgsql


.NET Code:

Code: Select all
string connectionString = @"Server=----; Port=----; Database=-----; User Id=------; Password=------; Unicode = true; Max Pool Size=200; Min Pool Size=20; Connection Lifetime=120; Connection Timeout=120; Default Command Timeout=120; Pooling=true";
            using (PgSqlConnection conn = new PgSqlConnection(connectionString))
            {
                conn.Open();
                using (PgSqlTransaction transaction = conn.BeginTransaction())
                {
                    using (PgSqlCommand command = new PgSqlCommand("getmultiple", conn, transaction))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        using (IDataReader reader = command.ExecuteReader())
                        {
                            Dictionary result1 = new Dictionary();
                            Dictionary result2 = new Dictionary();

                            int idOrdinal = reader.GetOrdinal("c1");
                            int nameOrdinal = reader.GetOrdinal("c2");

                            while (reader.Read())
                            {
                                result1.Add(reader.GetInt32(idOrdinal), reader.GetString(nameOrdinal));
                            }

                            reader.NextResult();

                            while (reader.Read())
                            {
                                result2.Add(reader.GetInt32(idOrdinal), reader.GetString(nameOrdinal));
                            }

                            reader.Close();
                        }
                    }

                    transaction.Commit();
                }
                conn.Close();
            }


We are using last dotConnect for PostgreSQL build (5.80.341) and PostgreSQL Server 9.1.2 64bits (Windows).

Any help please???
Thanks.[/list]
aegeavaz
 
Posts: 7
Joined: Mon 06 Feb 2012 10:34
Location: Madrid

Postby Pinturiccio » Tue 17 Apr 2012 14:37

We could not reproduce the issue. Your code successfully runs a lot of times.
1. How many records do you have in the t1 table?
2. Did you change the code before running it the second time?
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44

Postby aegeavaz » Tue 17 Apr 2012 16:53

Hi, thanks for the reply.

There are only 4 records in t1 table.
No, we didn't change the code between executions.

We can send you an small c# project with the code that is getting the error, but it is slightly the same as the code we wrote in the first post.

We'll appreciate any advice to move on in resolving the issue...
Thanks.
aegeavaz
 
Posts: 7
Joined: Mon 06 Feb 2012 10:34
Location: Madrid

Postby Pinturiccio » Wed 18 Apr 2012 11:23

Please send us a small test project with DDL/DML scripts for reproducing the issue.
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44

Postby Pinturiccio » Thu 19 Apr 2012 12:44

We have fixed the bug with multiple execution of stored procedures returning REF CURSORs. We will notify you when the corresponding build of dotConnect for PostgreSQL is available for download.
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44

Postby aegeavaz » Thu 19 Apr 2012 13:35

Great!!
Thank you very much.

Is there any chance to get a fix or the dll with the fix before wait to the next product build? We have a demo next week and we are in a rush to get this issues fixed.

Thanks again.
Regards.
aegeavaz
 
Posts: 7
Joined: Mon 06 Feb 2012 10:34
Location: Madrid

Postby Pinturiccio » Fri 20 Apr 2012 13:22

We have answered you by e-mail.
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44

Postby Pinturiccio » Fri 27 Apr 2012 06:37

The new build of dotConnect for PostgreSQL 5.80.350 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23998
Pinturiccio
Devart Team
 
Posts: 1979
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL