FireDAC vs Other Data Access Libraries: Which One to Choose for Delphi?
Delphi in Depth: FireDAC
Delphi is a powerful and versatile programming language that allows you to create cross-platform applications for Windows, Linux, macOS, iOS, Android, and more. Delphi also provides a rich set of tools and components for database development, such as FireDAC.
Delphi in Depth: FireDAC free 11
FireDAC is a universal data access library that enables you to connect to a wide variety of databases, such as Oracle, SQL Server, MySQL, PostgreSQL, SQLite, InterBase, Firebird, MongoDB, and many others. FireDAC also offers many features and options that help you optimize your connection configurations, access data efficiently, manipulate data flexibly, and perform advanced operations on any dataset.
In this article, you will learn how to use FireDAC for Delphi database development. You will discover the main features and benefits of FireDAC, such as:
Basic data access with FireDAC
Advanced data access with FireDAC
Flexible queries with FireDAC
Sophisticated features with FireDAC
By the end of this article, you will have a deeper understanding of FireDAC and how it can help you create powerful and elegant database applications with Delphi.
Basic Data Access with FireDAC
The first step in using FireDAC is to create a connection to a database. To do this, you need to use the TFDConnection component. The TFDConnection component allows you to specify the database driver name, connection parameters, login credentials, pooling options, transaction isolation level, and other properties that affect how you connect to a database.
To create a TFDConnection component at design time, you can simply drop it from the Tool Palette onto a form or a data module. Then you can use the Object Inspector to set its properties. Alternatively, you can use the Connection Editor dialog box that appears when you double-click on the TFDConnection component. The Connection Editor dialog box provides a graphical interface for configuring the connection properties.
To create a TFDConnection component at run time, you can use code like this:
var FDConnection: TFDConnection; begin FDConnection := TFDConnection.Create(nil); try FDConnection.DriverName := 'MSSQL'; FDConnection.Params.Database := 'AdventureWorks2017'; FDConnection.Params.UserName := 'sa'; FDConnection.Params.Password := 'password'; FDConnection.LoginPrompt := False; FDConnection.Connected := True; // do something with the connection finally FDConnection.Free; end; end;
Once you have a connection to a database, you can use FireDAC components to access data from different sources. For example, you can use the TFDQuery component to execute SQL queries and return data in a tabular format. You can also use the TFDTable component to access data from a single table without writing SQL. Alternatively, you can use the TFDStoredProc component to execute stored procedures and return data or output parameters.
To create a FireDAC dataset component at design time, you can drop it from the Tool Palette onto a form or a data module. Then you can use the Object Inspector to set its properties, such as Connection, SQL, TableName, StoredProcName, Params, and so on. You can also use the Fields Editor dialog box that appears when you double-click on the dataset component. The Fields Editor dialog box allows you to add, edit, or delete fields for the dataset.
To create a FireDAC dataset component at run time, you can use code like this:
var FDQuery: TFDQuery; begin FDQuery := TFDQuery.Create(nil); try FDQuery.Connection := FDConnection; FDQuery.SQL.Text := 'SELECT * FROM Customers WHERE Country = :Country'; FDQuery.Params.ParamByName('Country').AsString := 'USA'; FDQuery.Open; // do something with the dataset finally FDQuery.Free; end; end;
After you have a FireDAC dataset component with data, you can use it to navigate and edit data using various methods and properties. For example, you can use the First, Next, Prior, Last, MoveBy, Locate, Lookup, and Find methods to move the cursor to different records in the dataset. You can also use the EOF and BOF properties to check whether the cursor has reached the end or the beginning of the dataset. Moreover, you can use the Edit, Post, Cancel, Insert, Append, Delete, and ApplyUpdates methods to modify data in the dataset. You can also use the State property to check whether the dataset is in browse, edit, or insert mode.
Advanced Data Access with FireDAC
FireDAC also provides many features and options that allow you to access data more efficiently and flexibly. For example, you can create and use indexes, searches, and filters with FireDAC datasets. You can also create and use virtual fields and persisted datasets with FireDAC. Furthermore, you can use FDMemTables for in-memory data manipulation and caching.
Creating and Using Indexes
An index is a sorted order of records in a dataset that allows you to access data faster and easier. FireDAC supports two types of indexes: physical indexes and logical indexes. A physical index is an index that is defined and maintained by the database server. A logical index is an index that is defined and maintained by FireDAC in memory.
To create a physical index for a FireDAC dataset, you need to use SQL statements or database tools to define the index on the database server. Then you need to set the IndexFieldNames property of the dataset component to specify which fields are used for sorting. For example:
// Assume that there is a physical index on Customers table called IX_Country_Name FDTable1.TableName := 'Customers'; FDTable1.IndexFieldNames := 'Country;Name'; FDTable1.Open;
To create a logical index for a FireDAC dataset, you need to use the Indexes property of the dataset component to define the index in memory. Then you need to set the IndexName property of the dataset component to specify which index is used for sorting. For example:
// Assume that there is no physical index on Customers table FDTable1.TableName := 'Customers'; FDTable1.Indexes.Add.Name := 'ByCountryName'; FDTable1.Indexes.Add.Fields := 'Country;Name'; FDTable1.IndexName := 'ByCountryName'; FDTable1.Open;
Searching Data
Searching data is another way of accessing data quickly and easily. FireDAC supports two types of searches: record-based searches and text-based searches. A record-based search is a search that locates a record in a dataset based on one or more field values. A text-based search is a search that locates a record in a dataset based on a text pattern.
cursor to the first record that matches the specified field values. The Lookup method returns the field values of the first record that matches the specified field values without moving the cursor. For example:
// Locate the first customer from USA with name starting with 'A' if FDTable1.Locate('Country;Name', VarArrayOf(['USA', 'A']), [loPartialKey]) then ShowMessage('Found: ' + FDTable1.FieldByName('Name').AsString) else ShowMessage('Not found'); // Lookup the phone number of the first customer from UK Phone := FDTable1.Lookup('Country', 'UK', 'Phone'); if Phone null then ShowMessage('Phone: ' + Phone) else ShowMessage('Not found');
To perform a text-based search with FireDAC datasets, you can use the FindKey or FindNearest methods of the dataset component. The FindKey method moves the cursor to the first record that matches the specified text pattern exactly. The FindNearest method moves the cursor to the first record that matches the specified text pattern partially. For example:
// Find the first customer whose name is exactly 'Alice' if FDTable1.FindKey(['Alice']) then ShowMessage('Found: ' + FDTable1.FieldByName('Country').AsString) else ShowMessage('Not found'); // Find the nearest customer whose name starts with 'Bob' FDTable1.FindNearest(['Bob']); ShowMessage('Found: ' + FDTable1.FieldByName('Name').AsString);
Filtering Data
Filtering data is another way of accessing data selectively and efficiently. FireDAC supports two types of filters: server-side filters and client-side filters. A server-side filter is a filter that is applied on the database server and returns only the records that match the filter condition. A client-side filter is a filter that is applied on FireDAC in memory and hides the records that do not match the filter condition.
To apply a server-side filter for a FireDAC dataset, you need to use SQL statements or parameters to specify the filter condition on the database server. Then you need to set the Filtered property of the dataset component to True to activate the filter. For example:
// Use SQL statement to filter customers from USA FDQuery1.SQL.Text := 'SELECT * FROM Customers WHERE Country = ''USA'''; FDQuery1.Filtered := True; FDQuery1.Open; // Use parameter to filter customers from a given country FDQuery1.SQL.Text := 'SELECT * FROM Customers WHERE Country = :Country'; FDQuery1.ParamByName('Country').AsString := 'UK'; FDQuery1.Filtered := True; FDQuery1.Open;
To apply a client-side filter for a FireDAC dataset, you need to use the Filter property of the dataset component to specify the filter condition in memory. Then you need to set the Filtered property of the dataset component to True to activate the filter. For example:
// Use Filter property to filter customers whose name contains 'a' FDTable1.Filter := 'Name LIKE ''%a%'''; FDTable1.Filtered := True; FDTable1.Open; // Use Filter property to filter customers whose phone number is not empty FDTable1.Filter := 'Phone '''''; FDTable1.Filtered := True; FDTable1.Open;
Creating and Using Virtual Fields
A virtual field is a field that is not stored in the database but calculated or generated by FireDAC in memory. FireDAC supports two types of virtual fields: internal calculated fields and internal stored fields. An internal calculated field is a field that is calculated on demand by FireDAC using an expression or an event handler. An internal stored field is a field that is generated once by FireDAC using an expression or an event handler and stored in memory.
To create a virtual field for a FireDAC dataset, you need to use the Fields Editor dialog box that appears when you double-click on the dataset component. Then you need to add a new field and set its properties, such as FieldName, DataType, FieldKind, and ExpressionSource. You can also use the OnCalcFields event handler of the dataset component to provide custom logic for calculating or generating virtual fields.
For example, suppose you have a Customers table with fields Name, Country, and Phone. You can create a virtual field called FullName that concatenates the Name and Country fields. To do this, you can follow these steps:
Drop a TFDTable component and set its Connection and TableName properties to access the Customers table.
Double-click on the TFDTable component to open the Fields Editor dialog box.
Click on the New Field button to add a new field.
Set the FieldName property to FullName, the DataType property to ftString, the FieldKind property to fkInternalCalc, and the ExpressionSource property to 'Name + '' ('' + Country + '')'''.
Click OK to close the dialog box.
Open the TFDTable component and see the FullName field in action.
Persisting Data
Persisting data is a feature that allows you to save and load data from a FireDAC dataset to a file or a stream. FireDAC supports two formats for persisting data: binary and XML. A binary format is a compact and efficient format that preserves all the data types and values of the dataset. An XML format is a human-readable and portable format that can be exchanged with other applications or platforms.
To persist data from a FireDAC dataset, you can use the SaveToFile or SaveToStream methods of the dataset component. You can specify the format and the options for persisting data using the AFormat and AOptions parameters of these methods. For example:
// Save data from FDTable1 to a binary file FDTable1.SaveToFile('Customers.dat', sfBinary); // Save data from FDQuery1 to an XML file with UTF-8 encoding FDQuery1.SaveToFile('Customers.xml', sfXML, [poUseUTF8]);
To load data into a FireDAC dataset, you can use the LoadFromFile or LoadFromStream methods of the dataset component. You can specify the format and the options for loading data using the AFormat and AOptions parameters of these methods. For example:
// Load data into FDTable1 from a binary file FDTable1.LoadFromFile('Customers.dat', sfBinary); // Load data into FDQuery1 from an XML file with UTF-8 encoding FDQuery1.LoadFromFile('Customers.xml', sfXML, [poUseUTF8]);
Flexible Queries with FireDAC
FireDAC also provides many features and options that allow you to create and execute flexible queries with different databases. For example, you can use macros and FireDAC scalar functions to create dynamic queries that can adapt to different database dialects or conditions. You can also use Array DML to execute multiple queries with different parameter values in a single batch. Moreover, you can use cached updates and apply changes to the database in a transactional way.
Using Macros
A macro is a placeholder that is replaced by FireDAC with a literal value or an expression before executing a query. FireDAC supports two types of macros: system macros and user macros. A system macro is a macro that is predefined by FireDAC and represents some information about the database or the connection. A user macro is a macro that is defined by you and represents some value or expression that you want to use in your query.
To use a system macro in your query, you need to enclose it with curly braces in your SQL statement. For example:
// Use system macro ID to get the auto-increment field name FDQuery1.SQL.Text := 'INSERT INTO Customers (ID, Name, Country) VALUES (NULL, :Name, :Country)'; FDQuery1.ParamByName('Name').AsString := 'Alice'; FDQuery1.ParamByName('Country').AsString := 'USA'; FDQuery1.ExecSQL;
To use a user macro in your query, you need to enclose it with square brackets [] in your SQL statement. Then you need to use the Macros property of the dataset component to define the value or expression for the macro. For example:
// Use user macro [WhereClause] to specify a dynamic filter condition FDQuery1.SQL.Text := 'SELECT * FROM Customers [WhereClause]'; FDQuery1.Macros.ParamByName('WhereClause').AsRaw := 'WHERE Country = ''USA'''; FDQuery1.Open;
Using FireDAC Scalar Functions
conversion functions, and so on. FireDAC scalar functions are portable and can be used with different databases without changing your query.
To use a FireDAC scalar function in your query, you need to prefix it with the FD_ prefix in your SQL statement. For example:
// Use FireDAC scalar function FD_Upper to convert names to upper case FDQuery1.SQL.Text := 'SELECT FD_Upper(Name) AS Name, Country FROM Customers'; FDQuery1.Open; // Use FireDAC scalar function FD_Week to get the week number of the order date FDQuery1.SQL.Text := 'SELECT OrderID, FD_Week(OrderDate) AS Week FROM Orders'; FDQuery1.Open;
Using Array DML
Array DML is a feature that allows you to execute multiple queries with different parameter values in a single batch. This can improve the performance and efficiency of your database operations, especially when you need to insert, update, or delete many records at once. FireDAC supports Array DML for most databases that support batch execution or array binding.
To use Array DML with FireDAC datasets, you need to use the ArraySize property of the dataset component to specify the number of queries to execute in a batch. Then you need to use the ArrayDMLSize property of the dataset component to specify the number of parameter values to bind for each query. Finally, you need to use the SetArrayValue method of the Params property of the dataset component to assign the parameter values for each query. For example:
// Use Array DML to insert 10 customers with different names and countries FDQuery1.SQL.Text := 'INSERT INTO Customers (Name, Country) VALUES (:Name, :Country)'; FDQuery1.ArraySize := 10; FDQuery1.ArrayDMLSize := 2; FDQuery1.Params.ArrayType := atTable; FDQuery1.Params[0].SetArrayValue(0, 'Alice'); FDQuery1.Params[0].SetArrayValue(1, 'Bob'); FDQuery1.Params[0].SetArrayValue(2, 'Charlie'); FDQuery1.Params[0].SetArrayValue(3, 'David'); FDQuery1.Params[0].SetArrayValue(4, 'Eve'); FDQuery1.Params[0].SetArrayValue(5, 'Frank'); FDQuery1.Params[0].SetArrayValue(6, 'Grace'); FDQuery1.Params[0].SetArrayValue(7, 'Harry'); FDQuery1.Params[0].SetArrayValue(8, 'Iris'); FDQuery1.Params[0].SetArrayValue(9, 'Jack'); FDQuery1.Params[1].SetArrayValue(0, 'USA'); FDQuery1.Params[1].SetArrayValue(1, 'UK'); FDQuery1.Params[1].SetArrayValue(2, 'Canada'); FDQuery1.Params[1].SetArrayValue(3, 'Australia'); FDQuery1.Params[1].SetArrayValue(4, 'France'); FDQuery1.Params[1].SetArrayValue(5, 'Germany'); FDQuery1.Params[1].SetArrayValue(6, 'Italy'); FDQuery1.Params[1].SetArrayValue(7, 'Spain'); FDQuery1.Params[1].SetArrayValue(8, 'Japan'); FDQuery1.Params[1].SetArrayValue(9, 'China'); FDQuery1.ExecSQL;
Sophisticated Features with FireDAC
FireDAC also provides many features and options that allow you to add sophisticated features to your database applications. For example, you can use Local SQL to perform SQL operations on any dataset, regardless of its source or format. You can also use mapping rules and expressions to customize data presentation and validation. Furthermore, you can use TFDBatchMove to copy or migrate data between different sources.
Using Local SQL
Local SQL is a feature that allows you to perform SQL operations on any dataset that is compatible with FireDAC. This includes FireDAC datasets that are connected to different databases or sources, as well as third-party datasets that are supported by FireDAC via adapters. Local SQL enables you to join, filter, aggregate, and manipulate data from different sources using a single SQL query.
To use Local SQL with FireDAC datasets, you need to use the TFDLocalSQL component and the TFDConnection component. The TFDLocalSQL component acts as a local SQL engine that executes SQL queries on FireDAC datasets. The TFDConnection component acts as a bridge that connects the TFDLocalSQL component to other FireDAC datasets or components. For example:
// Use Local SQL to join data from FDTable1 and FDQuery1 FDLocalSQL1.Active := True; FDConnection1.Params.DriverID := 'SQLite'; FDConnection1.Connected := True; FDLocalSQL1.Connection := FDConnection1; FDLocalSQL1.DataSets.Add(FDTable1); FDLocalSQL1.DataSets.Add(FDQuery1); FDQuery2.Connection := FDConnection1; FDQuery2.SQL.Text := 'SELECT T.Name, T.Country, Q.OrderID, Q.OrderDate FROM FDTable1 T INNER JOIN FDQuery1 Q ON T.Name = Q.CustomerName'; FDQuery2.Open;
Using Mapping Rules and Expressions
Mapping rules and expressions are features that allow you to customize data presentation and validation for FireDAC datasets. Mapping rules are rules that define how FireDAC maps data types, formats, and values between the database and the dataset. Expressions are formulas that define how FireDAC calculates or validates data values for the d