Delphi Question : Multiple Tables in a TClientDataset?

Question

Is it possible to put the results from more than one query on more than one table into a TClientDataset?

Just something like

SELECT * from t1; SELECT * from t2; SELECT * from t3;

I can't seem to figure out a way to get a data provider (SetProvider) to pull in results from more than one table at a time.

Answer 1

There is not a way to have multiple table data in the same TClientDataSet like you referenced. The TClientDataSet holds a single cursor for a single dataset.

Answer 2

The only way would be to join the tables. But then you have to provide the criteria of the join through joined foreign keys.

select * from t1, t2, t3 where t1.key = t2.key and t2.key = t3.key;

Now suppose you came up with a key (like LineNr) that would allow for such a join. You then could use a full outer join to include all records (important if not all tables have the same number of rows). But this would somehow be a hack. Be sure not to take auto_number for the key, as it does not reuse keys and therefore tends to leave holes in the numbering, resulting in many lines that are only partially filled with values.

If you want to populate a clientdataset from multiple tables that have the same set of fields, you can use the UNION operator to do so. This will just use the same columns and combine all rows into one table.

Answer 3

ClientDatasets can contain fields that are themselves other datasets. So if you want to create three tables in a single dataset, create three ClientDatasets holding the three result sets that you want, and then you can put them into a single clientdataset.

This article:

http://dn.codegear.com/article/29001

shows you how to do it both at runtime and at designtime. Pay particular attention to the section entitled:

"Creating a ClientDataSet's Structure at Runtime using TFields"