Reversing the Default_order in a TClientDataSet

Reversing the Default_order in a TClientDataSet

Post by abreinga » Tue, 27 Jun 2006 11:58:24



I am using a TClientDataSet attached to a provider to display rows from large tables in a grid. In some cases (for example when the user presses Ctl_End) the CDS needs to be populated by the last N (say 100) rows in the table but these should be in displayed in ascending order. So the TQuery.SQL might be elect * from transactions order by Transaction_number desc An index could then be created over the CDS in ascending order. Unfortunately this is not always possible (the server might use EBCDIC or might order on columns that are not in the select). I would like to order the CDS in the Defaut_Order but in reverse or descending sequence. Can any one suggest how I might go about this, as no obvious option seems to exist? Thanks in advance
 
 
 

Reversing the Default_order in a TClientDataSet

Post by Blackbird » Tue, 27 Jun 2006 21:41:40


Use the TClientDataSetGrid descendant that's on CodeCentral, written by, I
think, John Kaster and Anders Ohlssen (I apoligize if the spelling's wrong).

--
Be the first one on your block to read the uproariously hilarious novel "the
Zany Time Travels of Warble McGorkle" (Book 2 in a trilogy). Download it
here: http://www.yqcomputer.com/

 
 
 

Reversing the Default_order in a TClientDataSet

Post by Bill Tod » Tue, 27 Jun 2006 21:51:15


The only way to change the order of of rows in a CDS is to create an
index or use the IndexFieldNames property.

--
Bill Todd (TeamB)
 
 
 

Reversing the Default_order in a TClientDataSet

Post by abreinga » Sun, 16 Jul 2006 09:54:37


Given the above, is there any way to create an index over the CDS by reverse RecNo?

I have a need to display the last portion of a very large dataset (millions of rows) in ascending sequence in a grid, but also to allow changes to the dataset (via a TDataSetProvider), so I am selecting the records from the dataset in reverse order. What I now need to do is sequence the CDS in reverse arrival order (which might be quite arbitrary) while keeping the CDS attached to the provider. I cannot find a way to do this, but feel that this must be a common requirement.

Any tips gratefully accepted...


Al
 
 
 

Reversing the Default_order in a TClientDataSet

Post by Bill Tod » Sun, 16 Jul 2006 10:30:49


If you mean the RecNo property of the CDS the answer is no. The number
returned by RecNo is not stored in the data. It is a relative record
number.

--
Bill Todd (TeamB)
 
 
 

Reversing the Default_order in a TClientDataSet

Post by abreinga » Wed, 19 Jul 2006 07:47:55


Please advise me as to a good alternative. The CDS will be fairly small (100 - 500 rows) but must be in reverse deafault order.

I could add another column to the source dataset with a calculated field (effectively a RecNo) but this would be quite messy and require changing many SQL statements.

I have tried, but could not find a way of adding a calculated field to the CDS after it had "connected" to the source dataset.

I could try and write a descendant CDS, but I don't think I know enough about the internals to do the job.

Thanks in advance for any help.
 
 
 

Reversing the Default_order in a TClientDataSet

Post by Johannes B » Wed, 19 Jul 2006 08:16:43


If you can use persistent fields, your idea of adding a calculated
(fkInternalCalc) column should be straightforward. You will not need to
change any SQL statements. Add all fields using the fields editor
first, then add a new field of kind fkInternalCalc. If I remember
correctly, when the dataset is first opened, OnCalcFields will fire for
each row, and you can check the dataset state for dsInternalCalc (big
IRRC), then set your new column to RecNo.

I don't think it is possible to add fields to a dataset while it is
open.

--
 
 
 

Reversing the Default_order in a TClientDataSet

Post by Bill Tod » Wed, 19 Jul 2006 10:24:05


I do not have a good alternative.

--
Bill Todd (TeamB)
 
 
 

Reversing the Default_order in a TClientDataSet

Post by abreinga » Wed, 19 Jul 2006 13:04:40


Thanks Johannes; unfortunately this is used to build a set of
grids on the fly, and as you mention I have not been able to
add fields once open. I may try to close / add field / open
though I suspect this will not work
Al
 
 
 

Reversing the Default_order in a TClientDataSet

Post by Johannes B » Wed, 19 Jul 2006 13:37:15


Well, if you can't think of anything better yourself, here's a
suggestion. After opening the dataset, have second TClientDataSet where
you create FieldDefs based on the whatever the first one contains, then
finally add a RecNo column before calling CreateDataSet and copying the
data over. Shouldn't be too bad if you only have a few hundred records
at a time. If you need to apply updates back, it will get a wee bit
more complicated, though.

--
 
 
 

Reversing the Default_order in a TClientDataSet

Post by vava » Wed, 19 Jul 2006 15:13:54


before opening ("connecting") CDS you should get its FieldDefs
manually, then add definitions of needed calculated fields, then
open/"connect" your CDS


but beware of bug in midas.dll
for me State is never dsInternalCalc in OnCalcFields event handler, so
perhaps you'll have to live with dsCalcFields

--
Vladimir Ulchenko aka vavan
 
 
 

Reversing the Default_order in a TClientDataSet

Post by Johannes B » Thu, 20 Jul 2006 00:03:02


That is an interesting idea, how can you get the FieldDefs manually?

--
 
 
 

Reversing the Default_order in a TClientDataSet

Post by vava » Thu, 20 Jul 2006 15:15:42

On 18 Jul 2006 08:03:02 -0700, "Johannes Bjerregaard" <jozzb@hotmail>



very simple, just use YourDataSet->FieldDefs->Update()

--
Vladimir Ulchenko aka vavan
 
 
 

Reversing the Default_order in a TClientDataSet

Post by Martyn Aye » Thu, 27 Jul 2006 04:11:18

Hi,

I don't know if this helps any, but this is what I do, where I don't
want to use persistent fields, but I do want to add calculated field
*and* have a RecNo field:

Creating calculated fields "dynamically"
========================================

In e.g. my datamodule's OnCreate

1. Open the CDS using a dummy where clause (e.g. "where 1 = 2").

2. Do a CDS.FieldDefs.Update.

3. Iterate the FieldDefs, creating a TField from each one, with the
DataModule the owner of the individual TFields, using something like

Field := FieldDef.CreateField(Self);
Field.DataSet := CDS;

4. Create my calculated fields likewise. Note: for a RecNo field, I
use fkCalculated, not fkInternalCalc.

Adding a RecNo field
====================

In the CDS's OnCalcFields event do something like

if DataSet.State = dsCalcFields then
DataSet.FieldByName('RecNo').AsInteger := DataSet.RecNo; { or
DataSet.RecordCount - DataSet.RecNo + 1 for descending }

Of course, this will be thrown if you subsequently switch indexes on
the CDS. I had a project where I handled that succesfully, but I
can't lay hands on right now.

Cheers, Martyn