Solved

Using TQuery with a TClientDataSet

Posted on 2001-08-29
30
2,568 Views
Last Modified: 2007-11-27
I would like to be able to use a TQuery with a TClientDataSet (reading its data from an XML file).

Some sample code would be nice!

Cheers,

Raymond.
0
Comment
Question by:rwilson032697
  • 12
  • 9
  • 8
  • +1
30 Comments
 
LVL 1

Expert Comment

by:ntony
Comment Utility
take a look of this :
CDS.FieldDefs.Clear;
for Count:=0 to QWork.FieldCount-1 do begin
   CDS.FieldDefs.Add(QWork.Fields[Count].FieldName, QWork.Fields[Count].DataType, QWork.Fields[Count].Size ,False);
end;
CDS.CreateDataSet;
CDS.LogChanges:=False;

QWork.First;
While not QWork.EOF do begin
   CDS.Append;
   for Count:=0 to QWork.FieldCount-1 do begin
   CDS[QWork.Fields[count].FieldName]:=QWork.Fields[count].value;
   end;
   QWork.Next;
end;


CDS=ClientDataset
QWork is the Query
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
Comment Utility
Hi Ray!

I see you are still trying to nut this one out!! :)

I'm still trying to work out what you actually want to do.  Do you want to do something like this:

TClientDataSet -> TQuery -> TDataSource -> TDBComponent??

So, you are using an XML DB through the TClientDataSet, but actually querying the records using a TQuery (similiar to if you where using a TDatabase component)???

I'm I missing the plot here, or is this what you want to be able to do?

Cheers

Stu.
0
 
LVL 1

Expert Comment

by:Kristian
Comment Utility
I happen to unterstand exactly what you want to do and i have the answer...

what you need to do is use a TDBProvider.

do this...

procedure TMyForm.AssignCDS;
var
  Provider : TDataSetProvider;
begin
  FClientDataSet.IndexDefs.Clear; // incase you re-use this option
  FClientDataSet.active := false; // incase you re-use
  Provider := TDataSetProvider.create(application); // has to be same parent as client dataset + query
  Provider.dataset := FQuery; // assign query to provider
  Provider.Name := 'MyLocalProviderName'; // give it a name
  FClientDataSet.providerName := Provider.Name; // give it the same name
  FClientDataSet.active := true; // this makes the client data set get the data
  FClientDataSet.providerName := ''; //null the provider name
  Provider.free; // free the provider.
end;



this was taught to me by one of the borland delphi developers at DCon this year...Kristian
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
OK - I may have been a bit vague in my description - sorry!

ntony: It looks like your code is injecting a query into a ClientDataSet? Interesting, but not what I wanted :-)

Stuart: Yes, that is what I want to do (aren't pictures worth a 1000 words!)

Kristian: Can you explain what the result of your code is? You destroy the provider at the end of it - why? Can you then execute an SQL statement through the Query?

Cheers,

Raymond.
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
Comment Utility
Ray,

I tried for hours (well, probably more like 20 minutes but it felt like hours) last night to get a model working, and it seems to me that it maybe you're not going to have much luck.  As I said in your other Q., I've only been playing with this stuff for a while so I'm learning at the same time as you.

What I had problems with was, in a traditional connection, you use a TDatabase to connect to a database.  You then use a TQuery, setting the DatabaseName property to the TDatabase's Database name.  Then the TQuery has access to the underlying tables (after logging in).  So, if you where to type:

select * from mylittletable order by bigfield

the TQuery would know you which database you where connecting too, thus being able to lookup the tables and fields.

When I tried doing the same sort of thing using a TClientDataset linked to a TQuery, I couldn't figure out how to enter the SQL (not physically, but logically).

Just thinking aloud here, but do you know if there is a native driver which could access an XML DB?  If there was, that would get around the ClientDataset issues.  Maybe even a ODBC (shudder) driver?

Stu.
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Hi Stuart,

Something I read about the MyBase support in D6 keeps nagging me. I can't track it down but I distinctly remember reading something that suggested you could use an XML DB just like any other type of table. If this is true then you should be able to use the TClientDataSet as a replacement for the data store and access the data with a TQuery...

Perhaps there is a clue in the way you can use a TQuery with a paradox table. You literally say 'Select * from fred.db'. You don't need a TDatabase or anything (this is how my current application does this via the BDE).

BTW - my impetus for wanting to use the XML MyBase feature of D6 is to get away from the drivers problem. A native, linked in XML database driver is about as perfect as it gets for my current application (once again I will have a single EXE installable that uses a data format that is a doddle to backup, understand, fiddle with and use elsewhere and if corrupted can be rescued with a text editor!). No more BDE! No more Paradox rescue utilities etc!

Cheers,

Raymond.
0
 
LVL 1

Expert Comment

by:Kristian
Comment Utility
hey rwilson,

the code i gave is a way of extracting data from an existing TQuery and pipes it into the TClientDataSet, the TDBProvider is just borlands way of doing this.

i will explain exactly what it does, ( and you can do it visuallt too ).

if you get a form
then create a TQuery, a TClientDataSet, ATDBProvider ( same tab as TClientDataset ), ATDataSource and A TDBGrid.
put them all of a form.

if you link the TQuery to the TDBProvider (dataset),
Link the TDBProvider to the TClientDataset (ProviderName),
Link the TClientDataSet to the TDatasource (Dataset),
and the TDatasource to the TDBGrid (Datasource).

now all controls are connected correctly.
all uyou have done is add the 2 extra components in between basically.

then set up your query ( the database and SQL ETC )
then set active to true.

then if you goto the client dataset and say active to true then the provider will populate the clientdataset and you can then set your query active to false, close it remove the provider( whatever ) as the client dataset is fully populated from the query and now stand alone. and as you can see by the grid you can use it etc even in design mode... ( and if you save it it also keeps the data in the DFM file so its there when you come back too ).

by doing it visually you can see what is going on easily and it is actually very easy once you have done it once..

ok number 2
you mentioned XML, well TClientdataset has the functionallity to load directly XML into it.
i saw it done ( ive never done it ) and its possible it might be just delphi 6. but try to use the loadfromfile and savetofile, as i beleive the format used is XML. but you will have to double check all of that, like i said it might be just D6.

hope that al made sence..
Kristian
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
Comment Utility
Hi Kristian,

I tried what you suggested and in D6 I dont have a TDBProvider (not that I could see anyway).  The tab I have TQuery on is BDE, what tab did you have the TDBProvider on (I'm using D6 Ent here).


Hi Ray,

I'd be interested to find out how to do it with XML.  I'm in the same situation as you are.  I'm tired of shipping a huge BDE for a simple app, or getting corrupt files because Paradox is a piece of crap.  If I could work out how to do this, it would sure save me a lot of headaches!  From what I have seen so far, I'm really impressed with XML.  The only thing that worries me a little about it, is the fact that it is plain text.  I could just imagine some of my clients going to town in notepad changing things because they didn't like something, only to stuff up the program ;)

Stu.

0
 
LVL 1

Expert Comment

by:Kristian
Comment Utility
well the DB provider was on the Midas tab ( which i believe has been renames DBSnap or something hasnt it ).
try that, or the help it is there trust me !!

and as you say you are using D6, i am 99% sure you can load XML directly into ClientDataSet, there are inbedded controls / functions for it, i have witnessed it, i cant help any more than that as i only have D5. but i absolutely promise CDS has XML functionallity built in!
Kristian
0
 
LVL 1

Expert Comment

by:Kristian
Comment Utility
additional....

there is a convertor tool as well built into D6, that allows you to map XML as well ( as formats can be slightly different ) and that pipes into ClientDataSet too, they where flogginh how great this was and showing it all off, like i said at DCon this year, you souldnt have to much of a problem. :)
K
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Kristian,

I have seen the references to the conversion tools etc, and I have seen (here in this Q and in other places) code samples of how to get data into a TClientDataSet. That much at least is a simple proposition. What I want to do is USE the data once it is in the TClientDataSet. I'd really like to be able to use a TQuery. If it can't be done with a TQuery, I'd like to know how you are supposed to USE the XML format data tables.

Cheers,

Raymond.
0
 
LVL 1

Expert Comment

by:Kristian
Comment Utility
basically i did the client data sets session at dcon ETC,
what i got from it was:

they did a query.
they then piped it into a client dataset using the provider.
then they disconected the provider.
then the ClientDataset was stand alone ( like a tquery )
then just access it using a datasource.
you can ament it ETC ( and all the rave about how great the client dataset was and what it can do )
then it sort of went into XML ( very briefly )
and they then showed that you could load and save directly to and from the clentdataset. ( like i said it was D6 )

i think the way to think about it is once the client dataset is populated it is basically a memory resident tquery, and access it in that manor. so you can add filters / set up indexdefs for sorting / set master datasets ETC, and access the data via a datasource. ( or directly by the field properties ETC ).

Kristian.

Different subject..
... i was reading throught the terminating an app quickly discussion, and i suddenly realised that, when i was 14 (15 years ago) my first ever job was calling bingo numbers ( dont take the p**s it was good for getting women! ) and when i did my boss was called Ray Wilson !!!

Ever owned a bingo hall ...???



0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Kristian,

From what you are saying it seems that you can't use the TClientDataSet as a dataprovider for a TQuery, is that correct? I have never used filters, preferring to use TQueries

And no, I have never owned a Bingo Hall :-)

Cheers,

Raymond.
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
Comment Utility
Kristian,

He did, but he is just blocking the memory out :)  

Stu
0
 
LVL 1

Expert Comment

by:Kristian
Comment Utility
No you are right you cant use TClientDataSet as a dataprovider for a TQuery, its the other way around the TQuery is the provider to the TClientDataSet (CDS).

just for info,
i have been playing the last couple of weeks with CDS, i have a new DBGrid Control, and have wrapped up a Datasource, and a CDS and just have a single load function that populates the CDS and some simple functions for setting columns titles, and masking ETC, and the grid itself has a bunch of standard operations, including the title bar buttons having nice graphics and sorting on clicks. now the sorting withing the CDS is, even with 30000 records Wizzy wizz fast ( 0.5 sec's ), but all manipulation of the CDS for my DrawCell events is causing me a couple of problems, and where the CDS is memory resident ( the data ) im not sure how this if i introduce it into our system, is going to affect recources ETC, and the whole populating the CDS is a bit nightmareish. when i came back knowing about CDS i was quite enthused to use it, but now i have been using it a bit im finding some good and some bad points and im not sure if im going to end up keeping it. its like my dinner time project at the moment rewriting our grid control, the main reason being all of the editing functionallity that i can get from the CDS. but using it for getting data and in all grids its a bit cumbersom!

just thinking out loud.
Kristian
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Hmmm... OK.

How about this little scenario:

I have a table (in an XML file). I can load it up into the a CDS fine. I then want to select all records according to some condition (eg: (CustID = 4) and (SomeOtherField < 2)). I then want to show only those records in a grid. Do I have to populate the grid myself, or can I use a data aware grid that will show only the selected records in the CDS?

Cheers,

Raymond.
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
Comment Utility
Hi Ray,

You can just set the filter to CustID = 4 and SomeOtherField < 2 (note the spaces between the operators, values and fieldnames).  Once you have the filter in place, set the FILTERED property to TRUE and Bob's your uncle, data!.  Just use a DBGrid to display the results (or any other DB aware control for that matter).  

I could have pointed that out ages ago if you wanted to do it like this, but the way Kristian was talking originally, it sounded like he had a solution for using the CDS as a data provider.

Stu.
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Hi Stu,

Hmm.. That sounds pretty simple! I assume sorting them is just as easy?

Cheers,

Raymond.
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
Comment Utility
Not too sure actually.  I know you can't do an order by.  I think the only way of doing it would be to create an index and set the index as ascending or descending.  I'll have a look when I have 30 seconds.

I still think there MUST be a way of using a TQuery with XML.  I wonder if TADOQuery would work...  Microsoft are pretty heavily into XML at present, and I know you can fetch an post data to MS SQL using XML (so I've read anyway, don't ask me how).  So maybe ADO has something in it to query XML recordsets.  Maybe that could be looked at too..

Stu.
0
 
LVL 1

Expert Comment

by:Kristian
Comment Utility
Stu is right on the filtering bit,

and i can tell you exactly how to do the sorting bit...

heres a code version.....

procedure DoCDSReOrder(CDS : TClientDataSet; ColumnName : string; Desc : boolean);
var
  idx : TIndexDef;
  ADstr  : string;
begin
  if (ColumnName = '')or(not CDS .Active) then
    exit;

  if Desc then
    ADstr := 'D'
  else
    ADstr := 'A';

// create index name by using column name and ## and A or D

  try
    { see if it exists first ( its done in a try except as if there is not result it creates an exception ( stupid i know ) but if it does exist it uses it otherwise creates it )}
    CDS.IndexDefs.Find(ColumnName+'##'+ADstr);
   
  except
    idx := CDS.IndexDefs.AddIndexDef;
    idx.Fields := ColumnName;
    idx.name := ColumnName+'##'+ADstr;

    if Desc then
      idx.Options := [ixDescending]
    else
      idx.Options := [];
  end;
 
  CDS.indexName := ColumnName+'##'+ADstr;
end;

// this could easily be made to do multiple cols ETC but is only single at the mo..

if you want to do it in design mode goto the CDS, create an index for a column(s) set Asc / Desc then select the index in the index name property of the CDS.

Kristian
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
Comment Utility
Hay Ray!  You awake there lad? :)

Sorry.  Now I have your attention, any news to share?

Stu.
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Hi Stu,

Yes - I'm awake, just been out of the loop for a few days :-)

I have rejigged my app to use TClientDataSets instead of TQueries and it goes pretty smoothly now (still a few little niggles but they are minor I think). I now have a single EXE distributable using XML tables that relies on nothing else - yay!

I am impressed how fast this stuff is - it beats using queries on Paradox tables hands down. The only down side is you need to manage the loading/saving of the data yourself, but that isn't a big problem.

Stu: You put me on to how the TClientDataSet is a TQuery replacement so I think you should get the points for this Q. OK?

Kristian: The indexing is also really useful so I'll post another 100 pt Q for you for your help - how does that sound? BTW, is using multiple index defs as simple as just adding a another one to the list?

Cheers,

Raymond.
0
 
LVL 6

Accepted Solution

by:
Stuart_Johnson earned 100 total points
Comment Utility
Hi Ray,

I know what you mean.  I only get around to checking EE once a week now.

I've been waiting for something like this for ages.  I was sick of deploying the BDE for something really simple (single table apps).  Now I don't have too.  It's marvellous!

I'm happy with the points.  I'm happy to do a 50/50 with Kristian if you like.  It's your call.

As for your index question, I'm actually doing some work on adding indexes today, so I can let you know how I go.  I know you can't add them to existing Paradox tables (please let me know if I'm wrong here!), but I'm not sure about XML.

I'm glad we helped you out!

Stu.
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Stu,

Here are the points. I'm happy giving you the all as I will be deleting the precursor Q to this one about MyBase XML databases.

Cheers,

Raymond.
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Stu,

Here are the points. I'm happy giving you the all as I will be deleting the precursor Q to this one about MyBase XML databases.

Cheers,

Raymond.
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
Comment Utility
Thanks Ray!

I've been playing with the index stuff all day, and I have had no luck at all in creating an index on an existing DB (even though the TTable.AddIndex function exists).  I dont know if I'm doing something wrong or not, but I can't get it going.

Just thought I'd let you know.

Cheers!

Stu.
0
 
LVL 1

Expert Comment

by:Kristian
Comment Utility
Hey guys,
Yeah cheers Ray the points are wicked thanks.
I know about the multiple indexing ive just not atually added it to my code yet, other more pressing deadlines 'n all that...

as for the speed :
i did a benchmark the other day i had a table with 290,000 rows and it was a 60 column person details table, ok it took a while to load into the dataset, but sorting took 2.5 secords when i added the index when it was added it took 0.5 seconds to use from then on!!!! now thats fast. ( mind you i had to use a 256MB ram machine as the 64Mb ram machine bombed ( not supprisingly with D3 + D5 + W2000 + Groupwise all open too i suppose! :)

Stu for the sorting read / use the code i added just up a bit, its only single col indexing, but its not5 hard to modify to make multiple column indexing.

Kris.
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Arrgghhh! So near and yet so far - <sob>!

I took my single installable executable and copied it to a machine that did not have Delphi installed on it and ran it. It promptly complained it could not find 'MIDAS.DLL'!

Any clues as to why it's asking for this?

Cheers,

Raymond.
0
 
LVL 12

Author Comment

by:rwilson032697
Comment Utility
Update! After more ferreting around in the help I found if you include midaslib.dcu in the uses clause you do not need to deploy midas.dll!

Cheers,

Raymond.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now