Trouble using cursors

Trouble using cursors

Postby migle » Mon 16 Aug 2010 10:37

Hi,

I'm running into two different problems when using cursors.

First, the sample in the documentation for PgSqlCursor.GetDataReader shows a function which has a named output parameter, p, to let the cursor out of the function. The parameter collection for the command that calls the function is filled in automatically using ParameterCheck.

Now, I wish I didn't have to do that: first, I would really dislike having to name the output parameter for the cursor, I would like to return it as the single return value; second, I would prefer to avoid ParameterCheck (I understand it will cause an additional round trip to the server).

However, I don't seem to be able to use PgSqlCursor this way. Here's how I'm doing it:

Code: Select all
            PgSqlCommand comm = new PgSqlCommand("myfunction", conn);
            comm.CommandType = CommandType.StoredProcedure;
            //comm.ParameterCheck = true; // doesn't work with ParameterCheck either

            // Add parameters.
            comm.Parameters.Add(new PgSqlParameter("a", PgSqlType.Int));
            comm.Parameters.Add(new PgSqlParameter("b", PgSqlType.Int));
            comm.Parameters.Add(new PgSqlParameter("return_value", PgSqlType.VarChar)).Direction = ParameterDirection.Output; // doesn't work with ReturnValue either

            comm.Parameters["a"].Value = 10;
            comm.Parameters["b"].Value = 3;

            comm.ExecuteNonQuery();

            PgSqlCursor cursor = comm.Parameters["return_value"].PgSqlValue as PgSqlCursor;

            using (PgSqlDataReader rd = cursor.GetDataReader()) {
                while (rd.Read())
                     Console.WriteLine(rd.GetValue(0));
            }


I find that comm.Parameters["return_value"] actually contains a cursor in there (in the debugger I see a {PgSqlCursor} in the PgSqlValue property of this parameter); AFAICT, all properties of this PgSqlParameter are set to the same values as are the ones for the generated parameter when the code is exactly like the sample; however, rd.Read() always returns false and there's no output.

What am I doing wrong ?
Do I really need to resort to naming the output parameter for the cursor and using ParameterCheck ?

As to the second problem: when the code is exactly like the sample in the documentation, I find that cursor.GetDataReader() actually fetches the entire dataset from the server (GetDataReader takes a lot of time, whereas the while loop does not involve communication).
Now, this kind of beats the purpose of using a cursor, doesn't it?
Can I work around this behaviour?
I already tried something called FetchSize but seems not to be related.

Please help,
migle
 
Posts: 27
Joined: Tue 19 Jan 2010 13:52

Postby StanislavK » Tue 17 Aug 2010 14:25

1) We've reproduced the issue with the empty reader got from the cursor. We will analyze the situation and inform you about the results. As a workaround, please try calling the ExecuteReader method instead of ExecuteNonQuery. It should return the data reader corresponding to the cursor returned by the function:
Code: Select all
PgSqlDataReader rd = comm.ExecuteReader();
while (rd.Read())
  Console.WriteLine(rd.GetValue(0));

2) The PgSqlDataReader class either fetches whole result set at once or retrieves its parts of specified size. This behaviour depends on the FetchAll and FetchSize properties of the corresponding PgSqlCommand. The problem is that these properties do not affect the cursor behaviour. We will fix this, the cursor will be fetched depending on the mentioned properties of the command.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Fri 24 Sep 2010 16:46

We've fixed the problem with the empty data reader returned by the cursor. The fix is available in the new 4.95.170 build of dotConnect for PostgreSQL. It can be downloaded from
http://www.devart.com/dotconnect/postgresql/download.html
(the trial version) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

The detailed information about the fixes and improvements implemented in dotConnect for PostgreSQL 4.95.170 is available at
http://www.devart.com/forums/viewtopic.php?t=19070
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Thu 14 Oct 2010 15:43

We have fixed the problem with the FetchAll and FetchSize properties not influencing on the cursor behaviour. The fix is available in the new 4.95.180 build of dotConnect for PostgreSQL. You can download it from
http://www.devart.com/dotconnect/postgresql/download.html
(the trial version only) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

For more information about the improvements and fixes available in dotConnect for PostgreSQL 4.95.180, please refer to
http://www.devart.com/forums/viewtopic.php?t=19239
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for PostgreSQL