Prepared command not returning all rows when executed multiple times
Posted: Mon 02 Sep 2013 13:28
Hello,
I'm using DevArt.Data.Oracle 7.8.301.0 in direct mode to connect to an Oracle 11g server. I'm not using anything fancy, just plain old ADO.Net components.
One particular SELECT query in the application I'm working on returns 160 rows the first time the prepared command is executed, but returns only 50 rows when executed again. I distilled a small test program from the actual application:
The output I get is
but it should be
When I insert "command.FetchSize = 100;" before the first call to Read(), the output changes to
When I comment out the call to Prepare(), the output changes to:
It seems to me that multiple executions of a prepared command are bugged in the current version, in that subsequent executions return at most FetchSize rows. If FetchSize was set explicitly, it uses that value, otherwise some value seems to be determined during the first execution (50 for 160 rows, in another test it was 200 for 22220 rows).
Can anyone confirm this?
I'm using DevArt.Data.Oracle 7.8.301.0 in direct mode to connect to an Oracle 11g server. I'm not using anything fancy, just plain old ADO.Net components.
One particular SELECT query in the application I'm working on returns 160 rows the first time the prepared command is executed, but returns only 50 rows when executed again. I distilled a small test program from the actual application:
Code: Select all
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Devart.Data.Oracle;
namespace TestOracleDataReaderProblem
{
class Program
{
static void Main(string[] args)
{
string connectionString = @"User Id=***;Password=***;Server=***;Direct=True;Sid=***";
using(OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
using(OracleCommand command = connection.CreateCommand())
{
command.ParameterCheck = false;
command.CommandText = @"
SELECT
c.CELLID,
c.SECTIONID,
c.COLNUMBER,
c.ROWNUMBER,
c.SLAVECOLNUMBER,
c.MASTERCOLNUMBER,
c.CELLNAME,
c.CELLTYPE,
c.READONLY,
c.CRLFAFTER,
c.FORMATSTRING
FROM
CELLS c
INNER JOIN SECTIONDEFINITIONS s ON c.SECTIONID = s.SECTIONID
WHERE
s.TESTTYPEID = :TESTTYPEID
AND s.LANGUAGEID = :LANGUAGEID
";
command.Parameters.Add(new OracleParameter("TESTTYPEID", OracleDbType.Number));
command.Parameters.Add(new OracleParameter("LANGUAGEID", OracleDbType.VarChar));
command.Prepare();
using(OracleTransaction transaction = connection.BeginTransaction())
{
command.Transaction = transaction;
Read(command, 74, "00");
Read(command, 74, "00");
}
}
Console.ReadKey();
}
}
private static void Read(OracleCommand command, int p1, string p2)
{
command.Parameters["TESTTYPEID"].Value = p1;
command.Parameters["LANGUAGEID"].Value = p2;
int numRows = 0;
using(OracleDataReader reader = command.ExecuteReader())
{
while(reader.Read())
{
int cellId = Convert.ToInt32(reader["CELLID"]);
// ...
numRows++;
}
}
Console.WriteLine(string.Format("Read {0} rows", numRows));
}
}
}
Code: Select all
Read 160 rows
Read 50 rows
Code: Select all
Read 160 rows
Read 160 rows
Code: Select all
Read 160 rows
Read 100 rows
Code: Select all
Read 160 rows
Read 160 rows
Can anyone confirm this?