Prepared command not returning all rows when executed multiple times

[timonchristl]

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:

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));
		}

	}

}

The output I get is

Code: Select all

Read 160 rows
Read 50 rows

but it should be

Code: Select all

Read 160 rows
Read 160 rows

When I insert "command.FetchSize = 100;" before the first call to Read(), the output changes to

Code: Select all

Read 160 rows
Read 100 rows

When I comment out the call to Prepare(), the output changes to:

Code: Select all

Read 160 rows
Read 160 rows

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?

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[Pinturiccio]

We have fixed the bug with reexecuting a prepared query in the Direct mode. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Pinturiccio]

New version of dotConnect for Oracle 8.0 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=28131.