Delphi ADO Query - Concatenate result sets

Question

I am writing an application that allows a user to submit a query to multiple systems. I then save each report from each system using SaveToFile, which saves it as an XML document.

The user can then inspect each report by selecting it from a menu, and the report is displayed by loading the report back into an ADO Query component (using LoadFromFile) and then linking this to a listview.

Now, my problem is that the user needs to be able to select any or all of the reports and join them together to create one large report. For example:

The user may define a query to be SELECT * FROM (table) and he can then run this query on System A, System B and System C. Instead of looking at three separate reports, he needs to have the option to join the reports together from the three systems, so that he can just look at one large report, covering all three systems.

Is it possible to do this using the ADO Query components? If not, as the file is saved as an XML document, would it be possible to join the reports together using the XML documents instead?

I have no idea where to start with this so any pointers would be appreciated.

EDIT: Important information which I somehow managed to forget to include at first:

1. The user needs to be able to access the data offline - i.e. he needs to be able to run the queries over various systems, then work with the reports at a later date. There is therefore a requirement to be able to save the reports to disk and then re-load them at a later date, when not connected to any of the systems that the queries were ran on. The user may want to concatenate the reports at this time.

2. All reports that are to be joined together will be of exactly the same type - i.e. they will all have been produced from the same query, so will give the same number/type of fields etc.

Answer 1

Instead of saving these as separate XML files, You could create a ClientDataSet and append the results from all of the sources into one dataset. This will let you look at all of the results at once or just for a single source at a time. The contents of the ClientDataSet can be saved as a single file, which should be easier than dealing with multiple files.

Of course, this assumes you are getting similar data (same columns) from all of your sources.

Answer 2

I've just about finished looking into this. My application allows the user to generate reports by querying their databases. I can get this to work and it is very efficient for small result sets - however, as this is a reporting application, and it's entirely possible that hundreds of thousands of records can be returned, using a ClientDataSet gives massive performance problems. Once you get above around 50,000 records (reasonable, given the customer base), processing starts to increase exponentially, so this is now basically moot.