FetchRows problem

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
FredLemay
Posts: 8
Joined: Tue 12 Dec 2006 20:28

FetchRows problem

Post by FredLemay » Thu 19 Jun 2008 15:54

We have a problem with the FetchRows property on a TSmartQuery on a specific query.
The query returns 100 records instead of 92. The property FetchAll is set to true and FetchRows property stays to 25 (default value).
The problem occur with ODAC 5.80.0.41 for Delphi 6 on Oracle Database 10g Enterprise Edition Release 10.2.0.1.0.
I have tested the problem on Oracle 8i and everything was good.
I can't verify all queries in the program.

I made a program to reproduce the problem.

Code: Select all

program BugODAC;

uses
  Ora, Dialogs, OraSmart, OdacVcl, SysUtils;

var
   OraSession1: TOraSession;
   SmartQuery1: TSmartQuery;
   ConnectDialog1: TConnectDialog;

begin
   OraSession1 := TOraSession.Create(nil);
   SmartQuery1 := TSmartQuery.Create(nil);
   ConnectDialog1 := TConnectDialog.Create(nil);
   try
      OraSession1.ConnectDialog := ConnectDialog1;
      with SmartQuery1 do
      begin
         Session := OraSession1;
         SQL.Add('SELECT ID_PB_CENTRERESP FROM PB_CENTRERESP C WHERE ID_PB_STRUCTURE IN');
         SQL.Add('(SELECT ID_PB_STRUCTURE FROM PB_Budget B WHERE B.Statut = 2');
         SQL.Add('and B.ID_PB_FeuilleSommaire=105)');
         FetchAll := True;
         Open;
         ShowMessage(Format('The query should return 92 records, but it returns %s records.  It works correctly with ADO', [RecordCount]));
         Close;

         SQL.Clear;
         SQL.Add('SELECT ID_PB_CENTRERESP FROM PB_CENTRERESP C WHERE');
         SQL.Add('(SELECT COUNT(*) FROM PB_Budget B WHERE B.STATUT = 2');
         SQL.Add('and B.ID_PB_FEUILLESOMMAIRE=105 AND C.ID_PB_STRUCTURE=B.ID_PB_STRUCTURE)  0');
         Open;
         ShowMessage(Format('The query should return 92 records, and it correctly returns %s records.', [RecordCount]));
         Close;
      end;
      SmartQuery1.Close;
      OraSession1.Close;
   finally
      ConnectDialog1.Free;
      SmartQuery1.Free;
      OraSession1.Free;
   end;
end.
You can use this script.

Code: Select all

drop table PB_BUDGET;
drop table PB_CENTRERESP;
create table PB_BUDGET
(
  ID_PB_BUDGET           NUMBER(10) not null,
  STATUT                 NUMBER(1) default 1 not null,
  ID_PB_FEUILLESOMMAIRE  NUMBER(10) not null,
  ID_PB_STRUCTURE        NUMBER(10) not null
)
;
create table PB_CENTRERESP
(
  ID_PB_CENTRERESP       NUMBER(10) not null,
  ID_PB_STRUCTURE        NUMBER(10) not null
)
;
insert into PB_BUDGET (ID_PB_BUDGET, STATUT, ID_PB_FEUILLESOMMAIRE, ID_PB_STRUCTURE) values (142, 2, 105, 1);
insert into PB_BUDGET (ID_PB_BUDGET, STATUT, ID_PB_FEUILLESOMMAIRE, ID_PB_STRUCTURE) values (174, 2, 157, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (54, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (55, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (56, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (57, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (58, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (59, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (60, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (61, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (62, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (63, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (64, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (65, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (66, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (67, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (69, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (70, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (71, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (72, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (73, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (74, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (75, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (76, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (77, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (78, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (79, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (80, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (81, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (83, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (84, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (85, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (86, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (87, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (88, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (89, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (90, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (91, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (92, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (93, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (94, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (95, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (96, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (97, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (98, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (104, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (99, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (100, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (101, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (46, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (47, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (48, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (49, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (50, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (51, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (52, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (2, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (5, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (6, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (8, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (9, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (10, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (11, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (14, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (13, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (15, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (16, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (17, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (18, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (19, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (20, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (21, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (22, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (23, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (26, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (27, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (28, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (29, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (30, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (31, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (32, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (33, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (34, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (35, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (36, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (37, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (38, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (39, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (40, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (41, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (42, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (43, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (44, 1);
insert into PB_CENTRERESP (ID_PB_CENTRERESP, ID_PB_STRUCTURE) values (45, 1);
commit;
Tanks a lot.
Top
Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Fri 20 Jun 2008 07:58

This bug is fixed in the latest version of ODAC 6.50.0.35. We will fix it in ODAC 5.80. Please contact us by e-mail.
Top
Post Reply

Return to “Oracle Data Access Components”