Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread Why Database Returns 0 Records When Number Of Rows Is Different?
Fri, Jun 22 2018 4:52 AMPermanent Link

Frederick Chin

I have a DBISAM table with 54 records (attached) and the dataset has the following query:-

select a.*, if(a.payee<>'',a.payee,a.desc) as payeedesc
from gltran a
where
(a.tranno like {cFilter1='%'}
or upper(a.payee) like {cFilter2='%'}
or upper(a.desc) like {cFilter3='%'})
and a.trandate>={dDatefrom='2018-01-01'}
order by a.trandate, a.tranno

In a button click, I have the following code:-

  with database.dataset do begin
       AfterLoad:=AfterLoadds;
       params.clear;
       params.Add('cFilter1='+quotedstr('%%'));
       params.Add('cFilter2='+quotedstr('%%'));
       params.Add('cFilter3='+quotedstr('%%'));
       params.Add('dDatefrom='+quotedstr('2014-01-01'));
       database.LoadRows(database.dataset);
  end;

In the AfterLoadDS procedure, I display the number of rows in a TLabel:-

Label1.Caption:=IntToStr(database.dataset.RowCount);

If the table has 54 records and above, the RowCount will show 0 records. If I delete any record from the table to bring the record count to 53 records and below, RowCount will show the exact number of records in the table.

Is there an EWB anomaly happening here?

--
Frederick



Attachments: glran.zip
Fri, Jun 22 2018 7:20 AMPermanent Link

Chris Holland

SEC Solutions Ltd.

Avatar

Team Elevate Team Elevate

I tried it here and it returned 54 records (55 when I added another one)

Chris Holland

[Team Elevate]


On 22/06/2018 09:52, Frederick Chin wrote:
> I have a DBISAM table with 54 records (attached) and the dataset has the following query:-
>
> select a.*, if(a.payee<>'',a.payee,a.desc) as payeedesc
>   from gltran a
>   where
>   (a.tranno like {cFilter1='%'}
>   or upper(a.payee) like {cFilter2='%'}
>   or upper(a.desc) like {cFilter3='%'})
>   and a.trandate>={dDatefrom='2018-01-01'}
>   order by a.trandate, a.tranno
>
> In a button click, I have the following code:-
>
>     with database.dataset do begin
>          AfterLoad:=AfterLoadds;
>          params.clear;
>          params.Add('cFilter1='+quotedstr('%%'));
>          params.Add('cFilter2='+quotedstr('%%'));
>          params.Add('cFilter3='+quotedstr('%%'));
>          params.Add('dDatefrom='+quotedstr('2014-01-01'));
>          database.LoadRows(database.dataset);
>     end;
>
> In the AfterLoadDS procedure, I display the number of rows in a TLabel:-
>
> Label1.Caption:=IntToStr(database.dataset.RowCount);
>
> If the table has 54 records and above, the RowCount will show 0 records. If I delete any record from the table to bring the record count to 53 records and below, RowCount will show the exact number of records in the table.
>
> Is there an EWB anomaly happening here?
>
> --
> Frederick
>




Attachments: gltranTest.zip
Fri, Jun 22 2018 11:31 AMPermanent Link

Frederick Chin

Chris Holland wrote:

/*
I tried it here and it returned 54 records (55 when I added another one)
*/

I have included the project with the data files (GLTRAN54.* for 54 records and GLTRAN53.* for 53 records) and a video (TEST5.MP4) of the running program.

--
Frederick



Attachments: test5.zip
Sat, Jun 23 2018 11:15 AMPermanent Link

Raul

Team Elevate Team Elevate

On 6/22/2018 11:31 AM, Frederick Chin wrote:
> I have included the project with the data files (GLTRAN54.* for 54 records and GLTRAN53.* for 53 records) and a video (TEST5.MP4) of the running program.
>

I'm seeing it here as well and it does appear to be a dbisam related
somehow.

I tried query below with DBSYS 4.45 Build 3 and it returns 0 records (i
assume this is same version built into ewb hence the 0).

I had older dbsys 4.42 Build 2 around and that one returns 54 records.

Query:
select a.*, if(a.payee<>'',a.payee,a.desc) as payeedesc
from gltran54 a
where (a.tranno like '%' or upper(a.payee) like '%' or upper(a.desc)
like '%') and a.trandate >= '2014-01-01'
order by a.trandate, a.tranno


This is definitely something Tim needs to look at

Raul
Sat, Jun 23 2018 2:17 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Frederick,

Raul is correct - it's an optimization bug in DBISAM 4.45.  The issue is with how DBISAM is handling the wildcards combined with a performance optimization added in 4.45 for making ANDs more efficient with set processing.  Luckily, the fix doesn't affect the performance optimization.

I'll have a fix for it out by tomorrow in a 4.45 Build 4.

Tim Young
Elevate Software
www.elevatesoft.com
Sat, Jun 23 2018 6:41 PMPermanent Link

Frederick Chin

Tim,

/*
I'll have a fix for it out by tomorrow in a 4.45 Build 4.
*/

Thanks.

I suppose that EWB also needs to be updated because the same query in the Preview tab of the Database Manager also returns 0 records.

--
Frederick
Mon, Jun 25 2018 3:09 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Frederick,

<< I suppose that EWB also needs to be updated because the same query in the Preview tab of the Database Manager also returns 0 records. >>

Yes, EWB will need updated, also.

Tim Young
Elevate Software
www.elevatesoft.com
Image