How to extract a set of records with a specific field from a Delphi in-memory data table

Question

I've an in-memory table that I've populated with data from a custom-format file. I'd like to use this as a repository for the data without duplicating it elsewhere. Each record has a special tag field which can be 1..30. I'd like to create up to 30 virtual 'tables' that I can supply to DBGrid's or DBChart's that look like they each contain the relevant record set corresponding to my tag field. I'm new to database work but know a lot about Delphi so I could DIY a solution and create more in-memory tables using a simple iterative process. I'd rather have something that was a bit more scalable though. I see there is an SQL select statement but on a quick fiddle (using TQuery) I could not see how this would work on an in-memory table (from DevExpress).

Answer 1

1. Some in-memory tables, like a TClientDataSet, TkbmMemTable, TADMemTable allows to clone the main table. Then you can apply a filter, a sort order, a range to the cloned copy. So, you may have the few virtual views build on the same set of the records. And each view looks like a SELECT * FROM tab WHERE ... ORDER BY ... For details check the help for the method CloneCursor and properties Filter, Filtered, IndexFieldNames, etc.

2. TQuery cannot use an in-memory table as a datasource. There are few (single ?) other products, like a xQuery, which allows to execute a SQL query against ordinal TDataSet descendants.

3. Finally, you can load data into some embedded DB, like a SQLite or FB Embedded, then just to make query to this database.

Answer 2

if your dataset is not sorted by the specific field, then you have to visit each record, however if it is sorted then you only have to find the first non match which is greater than the value your looking for.

Answer 3

AidAilm's Free (for personal use) SQLMemTable components: these include : SQLMemTable, SQLMemQuery and SQLMemDatabse.

Each component acts as its "traditional" counterpart.

1. Drop a TSQLMemDatabase; modify its DatabaseName property to suit your needs.
2. Add a TSQLMemTable component. modify its TableName property to suit your needs. Add some fields. Activate the table; it will be automatically added to your TSQLMemDatabase component.

TSQLMemQuery is linked to a TSQLMemTable component through TDataSource. The TSQLMemTable's tablename property is used to identify it in the TSQLMemQuery SQL queries. You can check the Online documentation for all possible SQL commands.

Here is the link for more info :http://www.aidaim.com/in-memory_sql_database_delphi.htm