Trouble using cursors
Posted: 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:
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,
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));
}
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,