Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Setting TClientDataSet.IndexName reduces record count


This question is answered.


Permlink Replies: 13 - Last Post: Oct 31, 2016 7:46 AM Last Post By: Kevin Morris
Kevin Morris

Posts: 52
Registered: 1/8/13
Setting TClientDataSet.IndexName reduces record count  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 25, 2016 3:23 PM
I am stepping through the records of a TClientDataSet using various Indexes. I start with a call to EmptyDataSet, with one index, load 54 records and record count is correct at 54.

I then change the index to a different index, and the record count drops to 6. This is not correct. Regardless of which index is chosen the record count should always be 54.

I do not have a filter active. I do not have a range active.

--- QUESTION ---

What am I missing that is causing 48 of my records to disappear?

--- Code ---

var
  pdf: TClientDataSet;
begin
  pdf := cdsPdfExt;  //  <== Index is already set at design time.  Index is on one field.
 
  pdf.EmptyDataSet;
  pdf.IndexName := '';
  pdf.Filtered := False;
  pdf.Filter := '';
 
  // Not Shown:  Code to load 54 records
  // RecordCount = 54    <=== Correct
 
 
  pdf.IndexName := 'cdsPdfExtIdxFldTyp';  //  <== Index is on a single field, different from the field the original index is built on.
  pdf.Filtered := False;
  pdf.Filter := '';
  pdf.First;
 
  // No other code is involved.  Just what is shown here.  There is no OnCalcFields event.  There is no OnFilterRecord event.  
  // RecordCount = 6    <=== NOT Correct   Record count should be 54 not 6.
 
 
 


Edited by: Kevin Morris on Oct 27, 2016 9:55 AM
Peter Below

Posts: 1,227
Registered: 12/16/99
Re: Setting TClientDataSet.Filter reduces record count  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 26, 2016 9:29 AM   in response to: Kevin Morris in response to: Kevin Morris
Kevin Morris wrote:

I am stepping through the records of a TClientDataSet using various
Indexes. I start with a call to EmptyDataSet, with one index, load
54 records and record count is correct at 54.

I then change the index to a different index, and the record count
drops to 6. This is not correct. Regardless of which index is
chosen the record count should always be 54.

Recreate the index, it is not up to date.


--
Peter Below
TeamB

Kevin Morris

Posts: 52
Registered: 1/8/13
Re: Setting TClientDataSet.Filter reduces record count  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 26, 2016 9:44 AM   in response to: Peter Below in response to: Peter Below
Hi Peter, yes, that was my first thought, but I cannot find any function that will allow me to rebuild, refresh, update the index.

What is the proper/best way to recreate/ or update the index?
I have 6 indexes, is there a way to just force all indexes to be updated?
How does the index get out of date in the first place? (I empty the dataset and reload it often. Shouldn't the index be kept up to date automatically?)
Should I be updating all my indexes every time I add a new record? Every time I empty the data set?

Thanks.

Peter Below wrote:
Kevin Morris wrote:

I am stepping through the records of a TClientDataSet using various
Indexes. I start with a call to EmptyDataSet, with one index, load
54 records and record count is correct at 54.

I then change the index to a different index, and the record count
drops to 6. This is not correct. Regardless of which index is
chosen the record count should always be 54.

Recreate the index, it is not up to date.


--
Peter Below
TeamB


Edited by: Kevin Morris on Oct 26, 2016 9:46 AM
Peter Below

Posts: 1,227
Registered: 12/16/99
Re: Setting TClientDataSet.Filter reduces record count [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 26, 2016 10:21 PM   in response to: Kevin Morris in response to: Kevin Morris
Kevin Morris wrote:

Hi Peter, yes, that was my first thought, but I cannot find any
function that will allow me to rebuild, refresh, update the index.

I'm afraid I cannot help you here. I never worked with a TClientdataset
before.

--
Peter Below
TeamB
Kevin Morris

Posts: 52
Registered: 1/8/13
Re: Setting TClientDataSet.IndexName reduces record count  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2016 9:50 AM   in response to: Kevin Morris in response to: Kevin Morris
This is now driving me crazy. I have made IndexDefs at design time, and have StoreDefs set to True.

I am learning that Indexes are different than IndexDefs. They seem to be loosely associated with each other. Using TClientDataSet.DeleteIndex to delete an index causes IndexDefs to increase from the design time count (5) by 2 (the 2 "built in" indexes) for a total of 7. If you then call .IndexDefs.Clear, the IndexDefs.Count goes back to 2. Adding an index by using .IndexDefs.Add() adds the index (I assume) and also adds an item to .IndexDefs (for a total of 3 now), but if after adding the index you call .IndexDefs.Update it removes the IndexDefs you just added and goes back down to just the 2 "built in" indexes. However, if you then go and try to recreate the index you just "lost" by doing the .IndexDefs.Update it throws a runtime error that the index you are trying to create already exists, even though it's not listed in the IndexDefs item list. What is going on here... cancel that... I don't care.... can some one just answer the simple question...

What is the correct and proper way to rebuild / refresh / update actual indexes (not just the index definitions) when you have IndexDefs set up at design time and StoreDefs set to true? (There seems to be no way to do this)

Thanks in advance. (Also, why should I have to refresh/ update indexes at all? When .EmptyDataSet is called or as new records are appended, why don't the indexes get updated automatically?)

Edited by: Kevin Morris on Oct 27, 2016 10:08 AM
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Setting TClientDataSet.IndexName reduces record count [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2016 11:02 AM   in response to: Kevin Morris in response to: Kevin Morris
Kevin Morris wrote:
I am stepping through the records of a TClientDataSet using various Indexes. I start with a call to EmptyDataSet, with one index, load 54 records and record count is correct at 54.

I then change the index to a different index, and the record count drops to 6. This is not correct. Regardless of which index is chosen the record count should always be 54.

I do not have a filter active. I do not have a range active.

--- QUESTION ---

What am I missing that is causing 48 of my records to disappear?

--- Code ---

var
  pdf: TClientDataSet;
begin
  pdf := cdsPdfExt;  //  <== Index is already set at design time.  Index is on one field.
 
  pdf.EmptyDataSet;
  pdf.IndexName := '';
  pdf.Filtered := False;
  pdf.Filter := '';
 
  // Not Shown:  Code to load 54 records
  // RecordCount = 54    <=== Correct
 
 
  pdf.IndexName := 'cdsPdfExtIdxFldTyp';  //  <== Index is on a single field, different from the field the original index is built on.
  pdf.Filtered := False;
  pdf.Filter := '';
  pdf.First;
 
  // No other code is involved.  Just what is shown here.  There is no OnCalcFields event.  There is no OnFilterRecord event.  
  // RecordCount = 6    <=== NOT Correct   Record count should be 54 not 6.
 
 
 


Edited by: Kevin Morris on Oct 27, 2016 9:55 AM

I can't reproduce this in Berlin update 1. I created a cds with 3 fields, an
int (ID), string (str), int (int). I put a single column IndexDef on each (the
last column being descending). The dfm definition for the CDS looks like

   object cds1: TClientDataSet
     PersistDataPacket.Data = {
       490000009619E0BD010000001800000003000000000003000000490002494404
       00010000000000037374720100490000000100055749445448020002000A0003
       696E7404000100000000000000}
     Active = True
     Aggregates = <>
     FieldDefs = <
       item
         Name = 'ID'
         DataType = ftInteger
       end
       item
         Name = 'str'
         DataType = ftString
         Size = 10
       end
       item
         Name = 'int'
         DataType = ftInteger
       end>
     IndexDefs = <
       item
         Name = 'cds1IndexID'
         Fields = 'ID'
       end
       item
         Name = 'cds1IndexStr'
         Fields = 'str'
       end
       item
         Name = 'cds1IndexInt'
         DescFields = 'Int'
         Fields = 'int'
         Options = [ixDescending]
       end>
     IndexName = 'cds1IndexID'
     Params = <>
     StoreDefs = True
     Left = 292
     Top = 20
     object cds1ID: TIntegerField
       FieldName = 'ID'
     end
     object cds1str: TStringField
       FieldName = 'str'
       Size = 10
     end
     object cds1int: TIntegerField
       FieldName = 'int'
     end
   end


The populate code just looks like

procedure TForm1.btn1Click(Sender: TObject);
var
   I: Integer;
begin
   cds1.EmptyDataSet;
   for I := 54 downto 1 do
   begin
     cds1.Insert;
     cds1ID.AsInteger := i;
     cds1str.AsString := i.ToString;
     cds1int.AsInteger := Random(1000);
     cds1.Post;
   end;
   // Filtered part unnecessary but there since you are doing this
   cds1.Filtered := False;
   cds1.Filter := '';
   cds1.First;
   lbl1.Caption := cds1.RecordCount.ToString;
   lbl2.Caption := cds1.IndexName;
end;


and the cycling of indexes looks like

procedure TForm1.btn2Click(Sender: TObject);
var
   i : Integer;
begin
   i := cds1.IndexDefs.IndexOf(cds1.IndexName);
   if i = cds1.IndexDefs.Count - 1 then
     cds1.IndexName := ''
   else
     cds1.IndexName := cds1.IndexDefs.Items[i + 1].Name;
   // Filtered part unnecessary but there since you are doing this
   cds1.Filtered := False;
   cds1.Filter := '';
   cds1.First;
   lbl1.Caption := cds1.RecordCount.ToString;
   lbl2.Caption := cds1.IndexName;
end;


the record count was always 54. The IndexDef count was always 2 (which is three
index defs since it is zero based).

Those test were done without any visual DB components hooked up, when I hooked
up a DBGrid I see the sorting change as I cycle indexes the RecordCount is
always 54.

The code to display in the grid (the downto in populate was so the first index
was different than the insert order to see in the grid the visual difference
between no index and the ID index). Initially no datasource hooked up to the
CDS to prevent things like fetches causing changes to the record count if there
was a bug.

procedure TForm1.btn3Click(Sender: TObject);
begin
   if Assigned(ds1.DataSet) then
     ds1.DataSet := nil
   else
     ds1.DataSet := cds1;
end;


--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Kevin Morris

Posts: 52
Registered: 1/8/13
Re: Setting TClientDataSet.IndexName reduces record count [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2016 1:42 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Hi Jeff, thanks for looking into this. Without any index refreshes / updates / recreates the code I have always works on my development machine, no matter how many times I test. However, on the production system, the real-life data some how is different in a way that I cannot yet determine, in such a way that, the indexes defined at design time in Delphi are getting "out-of-step" with the data that is actually in the fields. I'm on Delphi XE3 and have never had this problem before on any other TClientDataSet (used just for storing data locally) before (20 years using Delphi since Delphi 1).

At the moment I'm using a hodge-podge of ridiculous workarounds that should not be necessary, but I'll be putting this version into production and see what happens. Here is the code:


  procedure DeleteCdsIndex(const cds: TClientDataSet; const idx_nm: String);
  var i: Integer;
    begin
      cds.IndexDefs.Update;  // <-- Gotta use this here, or else the .IndexOf() can't find the added indexes
      i := cds.IndexDefs.IndexOf(idx_nm);
      if (i > -1) then
        begin
          cds.DeleteIndex(idx_nm);
          cds.IndexDefs.Delete(i);
        end;
      cds.IndexDefs.Update;
    end;
 
//-------------------------------------------
 
  DeleteCdsIndex(dmCln.cdsPdfExt, 'cdsPdfExtIdxFldTypFldID');
  DeleteCdsIndex(dmCln.cdsPdfExt, 'cdsPdfExtIdxOrd');
  DeleteCdsIndex(dmCln.cdsPdfExt, 'cdsPdfExtIdxFldTyp');
  DeleteCdsIndex(dmCln.cdsPdfExt, 'cdsPdfExtIdxStsOrd');
  DeleteCdsIndex(dmCln.cdsPdfExt, 'cdsPdfExtIdxTgtFldNm');
 
  dmCln.cdsPdfExt.IndexDefs.Add('cdsPdfExtIdxFldTypFldID', 'FldTyp;FldID', []);
  dmCln.cdsPdfExt.IndexDefs.Add('cdsPdfExtIdxOrd', 'Ord', []);
  dmCln.cdsPdfExt.IndexDefs.Add('cdsPdfExtIdxFldTyp', 'FldTyp', []);
  dmCln.cdsPdfExt.IndexDefs.Add('cdsPdfExtIdxStsOrd', 'StsOrd', []);
  dmCln.cdsPdfExt.IndexDefs.Add('cdsPdfExtIdxTgtFldNm', 'TgtFldNm', []);
  //dmCln.cdsPdfExt.IndexDefs.Update;  // <-- This deletes all the indexe definitions just added above.  Don't use it.
 
 
//-------------------------------------------
 
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Setting TClientDataSet.IndexName reduces record count [Edit]
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2016 2:45 PM   in response to: Kevin Morris in response to: Kevin Morris
Kevin Morris wrote:
Hi Jeff, thanks for looking into this. Without any index refreshes / updates / recreates the code I have always works on my development machine, no matter how many times I test. However, on the production system, the real-life data some how is different in a way that I cannot yet determine, in such a way that, the indexes defined at design time in Delphi are getting "out-of-step" with the data that is actually in the fields. I'm on Delphi XE3 and have never had this problem before on any other TCli
entDataSet (used just for storing data locally) before (20 years using Delphi since Delphi 1).

At the moment I'm using a hodge-podge of ridiculous workarounds that should not be necessary, but I'll be putting this version into production and see what happens. Here is the code:


Are you statically linking midas into your app or relying on the dll? If so you
might be loading an outdated dll, I tend to always use midaslib to avoid that.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Kevin Morris

Posts: 52
Registered: 1/8/13
Re: Setting TClientDataSet.IndexName reduces record count [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2016 3:34 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Excellent point Jeff. This project was started 5 years ago, and since it "worked" all this time, I never checked that. I see that I was distributing the .Dll, so I added MidasLib to my Uses clause. I will remove all my work-around code and deploy. (It works in test as usual, but that may be because I'm pointing to the correct .DLL on my dev machine and a different one on my production machine. Fingers crossed.

Are you statically linking midas into your app or relying on the dll? If so you
might be loading an outdated dll, I tend to always use midaslib to avoid that.

--
Jeff Overcash (TeamB)
Kevin Morris

Posts: 52
Registered: 1/8/13
Re: Setting TClientDataSet.IndexName reduces record count [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 27, 2016 5:55 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Just so you know.... the TClientDataSet is repeatedly .EmptyDataSet and reloaded with exactly 54 records about 100 times per day. The unexpected behavior of the .RecordCount dropping after a mere .IndexName change starts to occur some time after the first few cycles work correctly. It may be 5 times, or 20 times... somewhere in there it seems the indexes are "forgotten" and they get corrupted, then when I change index, the record count drops. The first few always work correctly.
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Setting TClientDataSet.IndexName reduces record count [Edit]
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 28, 2016 10:04 AM   in response to: Kevin Morris in response to: Kevin Morris
Kevin Morris wrote:
Just so you know.... the TClientDataSet is repeatedly .EmptyDataSet and reloaded with exactly 54 records about 100 times per day. The unexpected behavior of the .RecordCount dropping after a mere .IndexName change starts to occur some time after the first few cycles work correctly. It may be 5 times, or 20 times... somewhere in there it seems the indexes are "forgotten" and they get corrupted, then when I change index, the record count drops. The first few always work correctly.

2 alternatives you might try. Instead of EmptyDataset, call Close then Open
again. Or call Close then call CreateDataset, this will rebuild all the indexes
(which will be empty). Either of these should rebuild all the indexes (very
fast cause it is just an empty dataset at that point) and get around if somehow
the indexes are getting corrupted.

If the Indexes are getting corrupted (which is what seems to be happening based
on your description) I would go with the CreateDataset method as it throws out
the old indexes then creates new ones from the IndexDefs.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Kevin Morris

Posts: 52
Registered: 1/8/13
Re: Setting TClientDataSet.IndexName reduces record count [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 28, 2016 10:18 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
This is now starting to make sense. Thanks Jeff, I will give this a try.
Linden ROTH

Posts: 467
Registered: 11/3/11
Re: Setting TClientDataSet.IndexName reduces record count [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 30, 2016 11:33 PM   in response to: Kevin Morris in response to: Kevin Morris
Kevin Morris wrote:
Just so you know.... the TClientDataSet is repeatedly .EmptyDataSet and reloaded with exactly 54 records about 100 times per day. The unexpected behavior of the .RecordCount dropping after a mere .IndexName change starts to occur some time after the first few cycles work correctly. It may be 5 times, or 20 times... somewhere in there it seems the indexes are "forgotten" and they get corrupted, then when I change index, the record count drops. The first few always work correctly.

Just a thought is there a chance that your new IndexName contains non unique data

--
Linden
"Mango" was Cool but "Wasabi" was Hotter but remember it's all in the "source"
Kevin Morris

Posts: 52
Registered: 1/8/13
Re: Setting TClientDataSet.IndexName reduces record count [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 31, 2016 7:46 AM   in response to: Linden ROTH in response to: Linden ROTH
Hi Linden,

Just a thought is there a chance that your new IndexName contains non unique data

That's a good question. The values in all the fields are unique, except if they are empty, in which case, they are either an 'OFF', (string) or an empty string ''.

None of the indexes are marked as Primary, so, I would think that even if several rows had the same value, the record count should still be 54 (which is correct). Am I right?
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02