Build High-Speed Direct Access Apps In Delphi And C++Builder To Most Modern Databases Using FireDAC

FireDAC is a unique set of Universal Data Access Components for developing multi-device database applications for Delphi and C++Builder. With its powerful common architecture, FireDAC enables native high-speed direct access from Delphi to InterBase, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, IBM DB2, SQL Anywhere, Access, Firebird, Informix and more. Large collection of advanced capabilities. Key things to know about FireDAC listed in this post.

Configuration Options: Highly configurable, using this you can fine tune your data access and it is important how your Software access with databases. FireDAC provides a wide range of options organized into a hierarchical options system. Most of the options may be left with their default values.The FireDAC options are organized into five groups:

  • FetchOptions – Fetch options that control how the components and Phys layer commands fetch data from a DBMS. For example, it is possible to fetch all records at once or fetch records on demand.
  • FormatOptions – Format options that control how the DBMS data types are mapped to the FireDAC data types and backward. For example, a programmer may set up a mapping for Oracle NUMBER (38) onto dtBCD or onto dtInt64. For more details, read Data Type Mapping.
  • UpdateOptions – Update options that control how FireDAC posts updates to DBMS. For example, during an update, FireDAC can update all fields in a table or only the changed ones.
  • ResourceOptions – Resource options that control how system resources are used, dataset persistence and other. For example, a FireDAC Phys layer command can be performed asynchronously or blocked.
  • TxOptions – Transaction options that control how transactions are performed. For example, perform them in a ReadCommitted isolation mode. Note that TxOptions does not use option value inheritance.

SQL Command Preprocessor: The SQL command preprocessor can alter your SQL before sending it to the server When ResourceOptions.EscapeExpand and ResourceOptions.MacroExpand are true. It supports macros substitution and escape sequences.FireDAC supports three types of macro instructions:

  • Substitution variables. A substitution variable starts with the ‘!’ or ‘&’ symbol and is followed by the macro variable name.They allow placing substitution parameters in a command text in order to extend the use of parameters. For example, to parameterize a table name in the FROM clause or the column names in the SELECT clause, you can use substitution variables but not parameters.
  • Escape sequences. They allow writing DBMS-independent SQL commands.
  • Conditional substitutions. They allow expanding the SQL command conditionally, depending on application-defined attributes or on the currently attached DBMS.

FireDAC has 4 types of escape sequences: It is enclosed in curly braces ({}).

  • Allowing constant substitution: Allow writing constants in command text, independent of the DBMS syntax and regional settings.
  • Allowing identifier substitution. Alows abstracting from DBMS-specific identifier quoting rules. 
  • Conditional substitution: Allow to substitute text into command, depending on either a DBMS the application is connected to, or on a macro variable value. Beside different syntaxes, these constructions handle parameters and macros in different ways.
  • Scalar functions.Allows abstracting from a DBMS-specific set of a built-in functions and their syntax.  For examples check here

Array DML : The Array DML execution technique submits a single DBMS command with an array of parameters. Each command parameter has an array of values, and all parameters have arrays of the same length. Then FireDAC requests the DBMS to execute a command once for each row in arrays. This technique reduces the amount of communication between DBMS and client, enables DBMS to stream command execution, and speeds up execution time.For examples check here

Cached updates: Allows to defer posting updates to a database to a later time, rather than calling the Post / Delete methods. This allows to post multiple updates in a single batch, optionally enclosed into a transaction.FireDAC supports the Decentralized and Centralized Cached Updates modes:

  • Decentralized Cached Updates mode–when each dataset tracks the changes independently of others. This is the classical and default mode.
  • Centralized Cached Updates mode–when several datasets share a single change log and the changes are in historical order.

Watch this below video for demonstration.

To know more about FireDAC check the documentation and reference to samples here.