Working with Firedac LocalSQL

Home → Knowledgebase → Working with Firedac LocalSQL

Who ever knows or uses the Firedac component LocalSQL? Or the BatchMove? For most developers, these components are relatively unknown, even though they are very useful. In two blogs, I want to explain the added value of these components and show how they work. This blog is about the LocalSQL component.

The added value

Using the LocalSQL dataset, you can combine data from different datasets in a Firedac query. The component ensures that datasets become available as if they were database tables. Datasets can therefore be queried with an SQL query from a TFDQuery component.

This is very useful if you have data from different sources. For example from multiple databases. Or partly in memory and partly from the database. Or from different dataset components, for example ADO and Firedac. With LocalSQL, you can bring this data together very easily with a simple SQL query. It allows you to show the combined data very simply in a grid or create combined statistics.

Below I work out an example, combining data from a CSV with data from the database. In the database, I have stored customers with an id and name. In the CSV, only the id of the customer is used. When displaying the contents of the CSV, I want to show the customer’s name directly. This can be done with LocalSQL without too much effort.

The basic design

Below you can see the setup of my application. On the left is the connection to the database containing the table Customer. That is read through the QueryCustomers. On the right you see the CsvDataset, a Firedac MemTable. In this, the CsvMove (TFDBatchMove) loads the data from the CSV.

In the middle, you see the LocalSQL component, which I have named CombinedLocalSQL. Underneath, this component uses SQLite and therefore requires a SQLite connection. You don’t need to configure that connection any further, just indicate that the driver is SQLite. In this case, this is the LocalSQLConnection. The CombinedDataset component is a TFDQuery containing the SQL query that collects the data from the datasets.

The grid first shows two columns with the customer data from the database. The three columns after that come from the CSV.

The configuration

The LocalSQL component, as mentioned before, makes datasets available as if they were database tables. To make that possible, we specify which datasets we want to use and what “table name” they will be given. In the properties of the LocalSQL component, you will find the “DataSets” option for this purpose (see images below). Add the datasets and give them the name you want to use as the table name in the query.

By setting the LocalSQL component to Active, you can then run the query live after this and easily add the fields, for example. Be careful with that, because once you run the query, the underlying datasets are also set to Active.

The combined query

We use a Firedac query to apply the LocalSQL. The connection of this query is the same as the LocalSQL connection. And that’s all. We can now start creating the query as if we had a table Customer and a table CsvLine.

SELECT *
FROM Customer c
JOIN CsvLine csv ON (csv.CustomerId = c.CustomerId)

And of course you can extend and use this with all the possibilities that are in (SQLite) SQL. Think of the WHERE clause, but also aggegration functions such as SUM, COUNT, etc. The Firedac query then works as usual.

In summary

The LocalSQL component opens a diversity of possibilities to combine, filter and aggegrate data. It is widely applicable because it supports all forms of datasets. It can make complicated datasets with lookup fields a lot simpler. And it works quickly and easily. Highly recommended to apply in your projects.