setof refcursor function problem

aegeavaz · Post by **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]

Pinturiccio · Post by **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?

aegeavaz · Post by **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.

Pinturiccio · Post by **Pinturiccio** » Wed 18 Apr 2012 11:23

Please send us a small test project with DDL/DML scripts for reproducing the issue.

Pinturiccio · Post by **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.

aegeavaz · Post by **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.

Pinturiccio · Post by **Pinturiccio** » Fri 20 Apr 2012 13:22

We have answered you by e-mail.

Pinturiccio · Post by **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