Prepared command not returning all rows when executed multiple times

Prepared command not returning all rows when executed multiple times

Postby timonchristl » 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:

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?
timonchristl
 
Posts: 1
Joined: Mon 02 Sep 2013 10:43

Re: Prepared command not returning all rows when executed multiple times

Postby Pinturiccio » Thu 05 Sep 2013 09:39

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: Prepared command not returning all rows when executed multiple times

Postby Pinturiccio » Fri 04 Oct 2013 10:27

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
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: Prepared command not returning all rows when executed multiple times

Postby Pinturiccio » Fri 18 Oct 2013 08:53

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.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle