After a long time (some years...) an new post ?! Yes, I'm still alive and this post is not about Nevrona Rave .
Important:
I’ve written the following article in 2009 in german and translated it this week with google-translate and hope the result is ok!
For over 20 years, the topic of "application development with databases"have a very high priority for Embarcadero (formerly Borland). About the Borland Database Engine, to dbExpress (currently DBX4) Delphi is inherently database interface for many databases.
On the 3rd party market, there are many alternatives that are either dedicated to a specific database, or can work on more or less generic components with different databases.
This year Embarcadero add the well-known product „AnyDAC“ to the portfolio for Delphi with the new name "FireDAC". Thanks Marco & team for this great decision!!
And with the relation to AnyDAC: you’ll find some “old names”.
I work with AnyDAC / FireDAC since v2 and on my biggest app here in Germany every day more then 10.000 users (!) works every day with this app (Delphi and AnyDAC, and RAVE but the status of Rave in 2013 is another story (perhaps a post from me this year, but not promised)) and connect to Oracle v9 – v11 and MS-SQL v2005 – 2008R2 under XP-Win7, TerminalServer, Citrix and so on.
More then 4 years without any problems in this area of the application.
For over 20 years, the topic of "application development with databases" has a very high priority for Embarcadero (formerly Borland). Many of you will probably immediately think of the Borland Database Engine (BDE). Strictly speaking, was previously with the so-called interface IDAPI least a qualitatively equivalent alternative to Microsoft's ODBC interface in Scotts Valley, along with IBM, Novell and WordPerfect, was published in 92/93. Unlike ODBC, they should be appropriate for a number of platforms such as Microsoft DOS, IBM OS / 2, Novell Netware and MS-Windows, Microsoft saw only MS-Windows as the target platform. The history showed that Windows ODBC and thus prevailed and the now called BDE interface became less important. The SQLDB engine links this was extended to the then existing C / S databases but in the late nineties was the BDE and SQL-Links changed into depraceted by Borland. Both technical and legal licensing reasons were decisive. The successor was introduced in Delphi 6 and Kylix and was named "dbExpress". Acceptance was just in the first time very low, partly because of the complete other architectural approach which has been placed only on C / S - application development, which had the consequence that neither dBase or Paradox databases were supported by dbExpress and the other was the first version is not quite flawless. Many developers are looking for the discontinuation of the BDE, and initial experiences with dbExpress often an alternative that met both a resemblance to the working practices of BDE supported as different databases and there existing features. Here dbExpress was also a long time through the primary-down on performance aspect and less on the support of DB-specific options somewhat sidelined, for example here is only to support different types of databases from Oracle as well as the long term lack of support for MySQL transactions mentioned. With Delphi 2007 Borland/CodeGear publishes dbx4, complete new with a great architecture. But they made the decision to rewrite DataSnap instead of finishing the db-area (dbx4 and the vcl-components, CDS and so on). But this is another story/post, too...
But back to AnyDAC/FireDAC...
It supports many databases “native” without any special library inside the application, only the db-client from the db-vendor…
Of course, there are other databases that are not supported directly. There are then the ODBC and dbExpress bridges that open the door to other databases. In ODBC area level 2 & 3 as well as in the dbExpress driver to have the old architecture (up to Delphi 2005) dbExpress as support the new architecture of DBX4 completely. And new to the support of the current MS-database technology "Azure", meaning that the Delphi developer also in Win32 area ready for the cloud. There are all Delphi versions since version 5, except Delphi 8, supports. For the old versions, AnyDAC a Unicode support which is dependent of the used version of Delphi. If the DB client is Unicode-capable, it is in the database, InterBase and Firebird, Oracle and PostgreSQL on the corresponding parameter passed (eg Oracle CharacterSet = utf8 or the NLS_LANG section on Oracle’s side). However, this support is in the Delphi 2007 and not older versions consistently (feasible), so the access to the metadata fails here with Unicode.
In order to score points against the system's internal database framework DBX4 from Embarcadero, you need next to the same extent some other features are implemented. And in particular some of this features should be discussed in more detail in the article. Compared to other 3rd party components is not only a database, but in my opinion the most widely used C / S databases, both commercial and freeware-based support. After EMBT have bought these components, I think that I was right some years ago to switch to AnyDAC.
AnyDAC Architecture
(I haven’t made the switch to FireDAC)
I want to begin immediately, with the first layer in the graphic you know from the FireDAC-page on EMBT.
Here an older graphic (based on AnyDAC v3):
Invisible components
This layer or component is at first glance very similar to both dbExpress like the BDE. There is in particular the component TADConnection that establishes the connection to the corresponding database of the individual unit. The units in this area start with the prefix "uADComp".
Visible Components
In this area, or layer in the usual database applications such as dialogues Login form and offered DB error dialogs for the developer. The units in this area start with the prefix "uADGui".
Local data storage layer
This area is a little more interesting. Here an implementation of the data storage in the local memory is performed. The chosen approach is closer to the DataSet the ADO.net-frameworks as on TClientDataset of Delphi. This memory is responsible for reserving the fetched data and also the metadata. The units in this area start with the prefix "uADDatS".
DataAdapter layer
This layer is particularly in demand for more complex query results against the C / S database and should read here as Master/Detail- and nested-table operations. The units in this area start with the prefix "uADDapt".
The monitor and performance layer is explained below in more detail.
Finally AnyDAC has the so-called database driver layer. Through the appropriate interfaces, the different database API's to be integrated. At this point, of course, the bridges were located both on ODBC as well as dbx.
Earlier I had been briefly noted in particular that the special features mentioned here. Here's a first point of call is to: Support for DB-specific features. For each supported database, the DB from their manufacturer with specialties is well supported. The so-called DriverID made the distinction and Oracle are the AuthMode which normal, SYSDBA and SYSOPER provides, in MS SQL Server parameter OSAuthent for ADS- authentication. Depending on this, the corresponding DriverID TADPhysXXXXDriverLink drawn and publish the valid parameters. AnyDAC has to offer here a complete and sophisticated two-layer, which is characterized by the prefix "uADPhys".
After installing the components in Delphi will be the following VCL components:
In a first comparison to other DB components is nothing unusual on. On closer inspection, however, the reader will recognize adapter components and also an event alerter, which has been added in version 3.0.
The second tab contains the aforementioned graphic dialogue options and the third area in the Component Palette is responsible for access to that database.
The bridge from the application to C / S database does TADManager either component or the component TADConnection, first is with the many known BDE TSession component and the latter with the TDatabase component. The TADManager component accesses the information "ADConnectionDefs.ini" to file and is a many areas compared with dbExpress. The TADConnection can either be with the configuration manager (via the pull-down menu available) or through the VCL properties for design as well as run-time configured to connect to each database without using TADManager and activated. Of interest in this area is especially the fetch and format options - range. Here, application-wide settings for the SQL components are defined, but are overwritten again on individual SQL components (could) s. The property "FetchOptions.Mode" provides example fmAll and fmOnDemand on. The behavior is defined as the result is transmitted to the client. Especially with the high-performance scanning with fmALL definition, a performance gain defined as the data is to be read completely. Many other products is always fmOnDemand which often leads to a certain stagnation in the data flow. The next area is particularly important for the use of the different databases, such as Oracle and MS SQL is very powerful: The so-called format option allows the connection component at a central point, the definition of the application's internal data format, regardless of the database. As an example here, both Oracle and MS-SQL are called. Known, Oracle stores the particular floating-point numbers in the so-called BCD. This format is given in the MS SQL Server in the same form and in the use of an application with two databases often arise at the interface with the VCL components problems with program internally intercepted or the values have to be converted. Here, the developer, in the following source code easily recognizable, with several so-called MapRules define a conversion in one place and thus in the course of development "forgotten".
with MapRules.Add do begin
PrecMax: = 19;
PrecMin: = 4;
Source Data Type: = dtFmtBCD;
Target Data Type: = dtCurrency;
end
At this level, then the subject transaction be meaningfully addressed. First, there are the transaction property and other property of the update transaction. Here, AnyDAC available provides the following modes:
• xiUnspecified
• xiDirtyRead
• xiReadCommitted
• xiRepeatableRead
• xiSnapshot
• xiSerializible
TADQuery & more
After this level is now the next step. Here the TADCommand is a powerful and easy ways to communicate with the database. TDataset a connection is not implemented, it is TADQuery responsible. Here of the development team created a solution that is reminiscent of many of the good old PDC time and a great distinction in the architecture has been compared with dbExpress. The result is not passed over the unidirectional component to a TDataSetProvider TClientDataset but may remain (!) In ADQuery and from here it will also be changes in data written back to the database. Many components from other manufacturers follow a similar path, and also the use of it is very easy to use. By the word "may" was immediately obvious that it is not mandatory, but other options are available. A combination of the internal TADMemTable, and a few of the TADTableAdapter TADCommand objects created this ADQuery1. Here, the developer can choose between simplicity and flexibility. Finally in this section only briefly to the TADMemTable be addressed. It is a more flexible and performant alternative to the now somewhat dated TClientDataset component of Embarcadero. In the current 2010 version of the Delphi C source code was finally released, a review has taken place as yet. Andreas Hausladen has released a long time ago called MidasSpeedFix, the performance was noticeably improved and every user of TClientDataset test in your own application.
Component TADMemTable tuned optimally to the course of the other components and can be performed as follows AnyDAC multiple queries, which, when the same structure is not only from a table, and even from different databases data obtained may be supplemented with data (!)
ADQuery1Oracle.Open ();
ADMemTable1.Data = ADQuery1;
ADQuery2InterBase.Open ();
ADMemTable1.AppendData (ADQuery2);
Here you get the MapRules feature a very important role! In this area are still with TADDataSet.CopyDataSet opportunities expanded.
But back to ADQuery. The creation of the SQL statement is facilitated via a query builder, which indicates the tables and fields nice. In the following is a further characteristic of AnyDAC is presented in more detail in this plane.
SQL preprocessing
The macro function is found only in the second moment and deserves a careful look. DA Soft calls this "preprocessing CommandText" also. The SQL text before the Execute / Open only as explained in detail below, edited and then sent to the server.
"Select * from table &" is ADQuery1.MacroByName ('table'). AsRaw of each table name is passed. This is in practice for interesting patterns and features, different table names in different databases. Brings us to the next possibility: distinction or consideration of database features.
A SQL statement can be constructed so as follows:
Select * from EMP where
{ORACLE} {if Field1 =: ParameterXZ {fi}
{MSSQL} = {if Felda :ParameterTT {fi}
Because of DriverID in TADManager TADConnection or the corresponding region is automatically selected and sent the SQL statement is syntactically correct to the database.
In addition you can also freely selectable macro parameters are used in the Delphi out with MacroByName (,MakrParam').AsRaw =’’ (Empty) or ‘1'is (by value) is activated or not activated, ie here a distinction is content, regardless of the database.
Furthermore, there are many part AnyDAC already implemented functions such as the castes of the different data types. In Oracle, the command "to_char" and MS-SQL "convert". The following SQL command will automatically convert into the correct syntax:
From
SELECT EMPNO FROM EMPLOYEES WHERE {fn CONVERT (EMPNO, CHAR)} LIKE% '1 '
shell
SELECT EMPNO FROM EMPLOYEES WHERE to_char (empno) LIKE '1% 'in Oracle
and
SELECT EMPNO FROM EMPLOYEES WHERE convert (char, EMPNO) LIKE '1% 'in MS SQL Server.
These options are diverse and allow you to create extremely flexible SQL statements both within a database, as well as through various databases across. The flexible design of SQL queries, depending on the user's selection is therefore more than slightly possible.
But this flexibility requires troubleshooting a way of checking. At this point tracing is the logical solution.
Tracing
AnyDAC features similar to other components, a tracing function. Currently, both the file option as well as the so-called remote option is offered in two versions. Write to all traffic in a file, the component "TADMoniFlatFileClientLink" is responsible. Here is the file name and is defined in the TADConnection component during application startup parameter "MonitorBy" defined with flat file. Then the tracing done in the specified file for troubleshooting.
The second option is the Remote option and this is the component "TADMoniRemoteClientLink" responsible. The parameter "MonitorBy" is remote 'is defined and with the standalone application admonitor, traffic can be watched very nice. Furthermore, even the TADMoniCustClientLink implemented a custom solution for call recording of communication.
Pooling
Components collection supports effective pooling analogous to and is controlled by the dbExpress/dbx4 TADConnection component. The physical connection to the database is maintained so long as possible and effective. In a TADConnection.Connected: = True is used to connect from this pool and a close put back in the pool. Especially the connection to the authentication and the exchange of basic information (if only the hostname or OS username) is now also for a user with a “tangible” action, ie there exists a visible time. To activate the developer only has the properties of the connection parameters "Pooled = True" will be added, and the rest is done by AnyDAC in the background. With the use of the components for creating a 3-tier application with Delphi DataSnap 2009 and also RemObjects Data Abstract precisely these characteristics are very important. In another episode will be discussed intensively on this point.
Deployment of DB Applications
The last point in this article is the dissemination of the application to the end user. Unlike dbExpress no additional libraries are required. Only when using the corresponding TADManger INI files must be passed. For pure use of TADConnection component there must be passed no file. Only the corresponding DB client of the manufacturer is necessary. For Oracle can e.g. with the Oracle client and the client will OracleInstant worked. The corresponding parameters of the database connection must be either hardwired or, recommendable, via configuration files or through a call to the application with the required parameters.
These database components offer many possibilities, but a description would go beyond the scope of an article. Just the Help PDF includes more than 800 pages meantime.
In practice, this collection of components has proven to be very performing and open to most databases.
Another feature which is really cool in AnyDAC is the "ArrayDML" for high-performance DML, perhaps more about this in one of the next post here.
I hope you've found in this article some new informations about AnyDAC/FireDAC and please remember; it's translated by Google
