Additional
Overview
Features
Try
Discuss
News
Docu
FAQ
Testimonials
Requests
Partners
Firebird Associate
 
Unified API PDF Print E-mail
Mar 12, 2010

AnyDAC for Delphi provides a range of features that help abstract the differences between DBMS, making it easy to write code that does not have to be concerned about different DBMS dialects or other subtle differences between DBMS.

The Content 

Live Data Window mode

TADTable and Live Data Window mode enables bi-directional navigation through large datasets with minimal memory usage and with minimal delays at dataset Open call, sorting, filtering and other operations. This improves the end-user experience, when working with large DB tables in GUI applications. The classic approach:

 

ADQuery1.SQL.Text := 'select * from MyLargeTab';
ADQuery1.Open;
ADQuery1.IndexFieldNames := 'name';
ADQuery1.Filter := 'name like ''J%''';
ADQuery1.Filtered := True;

 

The TADTable and Live Data Window mode equivalent:

 

ADTable1.TableName := 'MyLargeTab';
ADTable1.Open;
ADTable1.IndexFieldNames := 'name';
ADTable1.Filter := 'name like ''J%''';
ADTable1.Filtered := True;

 

Depending on the number of records in a DB table, DBMS, etc the difference may be 10 seconds for first case and 0.2 seconds for Live Data Window case.

Array DML Command execution

Array DML Command execution provides the possibility to insert, update, delete many records or call a stored procedure many times via a single Execute call. This minimizes network traffic and server workload, and is primarily useful in batch applications. For example, insert 1000 of records into DB. The classic approach:

 

var
  i: Integer;
begin
  ADQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2)';
  for i := 1 to 1000 do begin
    ADQuery1.Params[0].AsInteger := i;
    ADQuery1.Params[1].AsString := 'Str' + IntToStr(i);
    ADQuery1.ExecSQL;
  end;
end;

 

The Array DML equivalent:

 

var
  i: Integer;
begin
  ADQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2)';
  ADQuery1.Params.ArraySize := 1000;
  for i := 1 to 1000 do begin
    ADQuery1.Params[0].AsIntegers[i - 1] := i;
    ADQuery1.Params[1].AsStrings[i - 1] := 'Str' + IntToStr(i);
  end;
  ADQuery1.Execute(1000, 0);
end;

 

Depending on the DBMS, this will provide great performance gain, even though the coding is almost similar to the classic approach.

Rowset Fetching

AnyDAC Rowset Fetching allows you to specify the number of records that will be fetched in one network round trip. You can optimize this separately for each select statement that you execute, thereby minimizing the number of network round trips by specifying the RowsetSize option. For example, Zeos DBO does not support Rowset Fetching, so AnyDAC is a few times faster than Zeos DBO for fetching.

Command Batches

A Command Batch is a group of SQL statements sent at one time from an application to the DBMS for execution. DBMS compiles the statements into a single execution plan and executes as a single unit. This minimizes network traffic and server workload. After execution the DBMS returns result sets produced by the commands, which may be processed one by one, using the NextRecordSet method. For example:

 

ADQuery1.SQL.Add('select * from [Orders]');
ADQuery1.SQL.Add('select * from [Order Details]');
ADQuery1.Open; // [Orders] table rows are accessable here
ADQuery1.NextRecordSet; // [Order Details] table rows available here 

 

Or store each result set into separate dataset, using Data property. For example:

 

ADQuery1.SQL.Text := 'select * from orders; select * from customers';
 
ADQuery1.Open;
ADQuery1.FetchAll;
// assign orders records to ADMemTable1
ADMemTable1.Data := ADQuery1.Data;
 
ADQuery1.NextRecordSet;
ADQuery1.FetchAll;
// assign customers records to ADMemTable2
ADMemTable2.Data := ADQuery1.Data;

 

AnyDAC automatically skips empty result sets. Note, that AnyDAC supports command batches only for the DBMS which implement them. If the DBMS does not support batches, then you can use SQL scripting.

 

Also, AnyDAC fully supports commands, returning multiple result sets. Some examples:

  • Oracle stored procedures with REF CURSOR's;
  • Oracle result sets with nested cursors;
  • PostgreSQL varieties of cursor returning statements.

Asynchronous Command Execution

In addition to being ready for use in multi-threaded applications, AnyDAC makes it easy for the developer to not have to worry about multi-threading in the first place by allowing you to control the timing aspects of the data access operation execution. This includes Execute, Open and Fetch operations.

 

A programmer can specify the timeout (ResourceOptions.CmdExecTimeout) for data access operation execution and choose between four modes (CmdExecMode).

 

With help of TADGUIxFormAsyncDlg component, in the Cancel Dialog mode a user may be notified about long running operations:

 

AnyDAC Asynchronous Execution Dialog

 

Also AnyDAC offers AbortJob method, allowing to cancel the long running tasks, like a havy queries.

Efficient update command generation

When an application calls dataset Insert / Edit / Post / Delete methods, then corresponding SQL commands must be generated. The generator must be aware of database identity fields, sequences, triggers, special data types (Oracle BLOB / CLOB / BFILE), pessimistic locking, etc.

 

The AnyDAC SQL commands generator is aware of these DBMS features and generates most efficient SQL commands for the current DBMS. This reduces the number of cases where a developer must use the hand made SQL commands. AnyDAC does not force a developer to use TADUpdateSQL, which may be used to override the update SQL commands.

 

For example, when posting a new record to an Oracle table, where one field is filled by a trigger from a sequence and another one is of BLOB type, AnyDAC will generate the SQL command:

 

INSERT INTO OracleTab (NAME, DT, IMAGE)
VALUES (:NEW_NAME, :NEW_DT, EMPTY_BLOB())
RETURNING :NEW_ID, :NEW_IMAGE

Update Commands Caching

Update Commands Caching allows AnyDAC to reuse the data updating commands, which are sent to the DBMS as result of Edit / Append / Delete / Post dataset method calls. This reduces the workstation workload and speeds up batch data editing algorithms. Update Commands Caching is transparent for the application programmer and may be started by setting CachedUpdates property to True.

Auto-incrementing fields

The main usage of the auto-incrementing fields is a synthetic primary key implementation. AnyDAC is using primary key columns to uniquely identify a record at updates posting and refreshing. Depending on a DBMS, the auto-incrementing fields may be implemented using a special IDENTITY (or similar) data type or using a generator (or a sequence) and a table trigger.

 

AnyDAC automatically recognizes a field of an auto-incrementing data type and setup it, so the column will be automatically refreshed after posting of a new record. The support work as for immediate updates, as for cached updates.

SQL dialect abstraction

If the application needs to support multiple DBMS’s, then it must be aware that their SQL dialects may be different.

 

AnyDAC escape sequences allow you to write SQL dialect independent SQL commands.

 

For example, the function to convert string to upper case is different in MySQL, Oracle and MSSQL. But the following command will work on any DBMS:

 

SELECT {ucase(Name)} FROM MyTable

 

In more complex cases, parts of the command or even the full command must be written differently. Then, the AnyDAC conditional escape sequence will help:

 

{IF Oracle} SELECT * FROM OracleTab {fi}
{IF MSSQL} SELECT * FROM MSSQLTab {fi}

Data Type Unification

AnyDAC may return a different data type for a table column depending on the SQL data type of the DBMS being accessed. For example, for Oracle NUMBER(10,0) returns ftFMTBcd, and for MSSQL INT returns ftInteger. When an application supports different DBMS’s, it must be aware of this issue.

 

AnyDAC allows setup a data mapping schema for each connection, to unify data types for the application. As a side benefit, this helps when migrating the application from other DAC's to AnyDAC.

 

For example, the setup for an Oracle connection to recognize all NUMBER(10,0) as ftInteger:

 

with ADConnection1.FormatOptions do begin
  OwnMapRules := True;
  with MapRules.Add do begin
    SourceDataType := dtFmtBCD;
    PrecMin := 10;
    PrecMax := 10;
    ScaleMin := 0;
    ScaleMax := 0;
    TargetDataType := dtInt32;
  end;
end;

Unified error reporting

Different DBMS’s will return different errors for logically the same error. But a database application must recognize certain types of backend errors, like an unique key constraint violation. This recognition should not depend on a DBMS.

 

AnyDAC combines unification and "personalization" of EADDBEngineException exception objects that drivers raise for errors. Personalization means that each driver has its own exception class, which contains all the information that the DBMS client returns. Unification means that all driver exception classes are inherited from a single base class that contains DBMS independent information.

 

For example, code for handling a unique key violation:

 

try
  ADQuery1.ExecSQL('insert into MyTab(code, name) values (:code, :name)', 
    [100, 'Berlin']);
except 
  on E: EADDBEngineException do begin
    if E.Kind = ekUKViolated then
      ShowMessage('Please enter unique value !');
    raise;
  end;
end;

 

With help of TADGUIxFormErrorDialog component, a user may be notified about errors returned by the database:

 

AnyDAC Error Dialog

Unified Transaction Support

Many DBMS’s have different transaction options, behavior and API. For example, the Firebird DBMS has powerful transaction control API, but other DBMS’s, like Oracle or Microsoft SQL Server have a more compact control API. While for Firebird a transaction must be explicitly started to perform any SQL command, for MySQL it is optional.

 

AnyDAC offers a unified API for transaction control, including the TADTransaction component, allowing you to employ the full power of Firebird / Interbase to execute SQL commands together with minimal explicit transaction control for other DBMS’s.

 

For example, the TADQuery component has Transaction and UpdateTransaction properties. The developer can set the Transaction property pointing to a read-only transaction and the UpdateTransaction property to a read-write transaction, which is the approach needed for Firebird application development.   

International applications support

Most enterprise-class database applications must be able to work with character data encoded in different character sets or provided as Unicode.

 

AnyDAC provides seamless support for:

  • different single byte client character sets and code pages, including standard ANSI;
  • as well multi-byte strings such as Unicode, including UTF8, UTF16 and UCS2 encodings.

Note, that the complete Unicode support may be achieved only with the Unicode Delphi version usage - Delphi 2009 and higher.

 

And application is free to use the Delphi native ANSI or Unicode strings. AnyDAC will perform transparent character set conversion between Delphi application and DBMS character set.

Login and password changing

AnyDAC offers the wide range of connection parameters and their management options. The application may use persistent connection definitions stored in external ADConnectionDefs.ini file:

 
[MSSQL_Demo]
DriverID=MSSQL
User_Name=sa
Server=127.0.0.1
Database=Northwind
MetaDefSchema=dbo
MetaDefCatalog=Northwind
MonitorBy=Indy

ADConnection1.ConnectionDefName := 'MSSQL_Demo';
ADConnection1.Connected := True;

Or create them on fly. Or use ADO-like connection strings:

ADConnection1.Open('DriverID=SQLite;Database=c:\test.sdb;Password=12345');

The end user Login Dialog enables users as to provide their login credential, as to change their expired password. The set of parameters visible to the end user and their labels may be adjusted using VisibleItems property.

 

Login Dialog

Automatic Connection Recovery

Some database applications may work within a non stable environment, where the network may be physically disconnected or the DBMS server may be restarted. Applications need to seamlessly recover from such unexpected interruptions and continue to communicate with DBMS.

 

The AnyDAC automatic connection recovery feature allows you to detect when a DBMS connection has been lost, and to respond and recover from this situation. The response may be to re-establish the connection, go into a special offline mode, or simply close the connection.

 

The application has to enable automatic connection recovery (ResourceOptions.AutoReconnect), optionally define event handlers (OnLosted, OnRestored, OnRecover) and it will be in safety. An applications may call Ping method to verify connection and/or keep it alive. For example:

procedure TForm1.ADConnection1Recover(ASender: TObject;
  const AInitiator: IADStanObject; AException: Exception;
  var AAction: TADPhysConnectionRecoverAction);
var
  iRes: Integer;
begin
  iRes := MessageDlg('Connection is lost. Offline - yes, Retry - ok, Fail - Cancel',
                     mtConfirmation, [mbYes, mbOK, mbCancel], 0);
  case iRes of
  mrYes:    AAction := faOfflineAbort;
  mrOk:     AAction := faRetry;
  mrCancel: AAction := faFail;
  end;
  Log('  Connection is recovering');
end;

Unified DB Events Support

Many DBMS’s have different options for notifying the client applications about events happened at the database side. For example, the Firebird DBMS has POST_EVENT procedure and the events listening API. The Oracle Server offers DBMS_PIPE and DBMS_ALERT packages. From the other side the SQLite database allows to extend it by the custom functions.

 

The database events may be used for a wide range of the tasks, including:

  • data change notifications;
  • general event notifications;
  • user-to-user notifications.

 

AnyDAC offers a unified API for DB events, allowing you to listen for the DB events in a DBMS independent fashion. It is represented by the TADEventAlerter component. For example:

ADEventAlerter1.Names.Clear;
ADEventAlerter1.Names.Add('Customers');
case ADConnection1.RDBMSKind of
  mkOracle:    ADEventAlerter1.Options.Kind := 'DBMS_ALERT';
  mkInterbase: ADEventAlerter1.Options.Kind := 'Events';
end;
ADEventAlerter1.Options.Synchronize := True;
ADEventAlerter1.OnAlter := DoAlert;
ADEventAlerter1.Active := True;
........
 
procedure TForm1.DoAlert(ASender: TADCustomEventAlerter;
  const AEventName: String; const AArgument: Variant);
begin
  if CompareText(AEventName, 'Customers') = 0 then
    qryCustomers.Refresh;
end;

Support for multiple SQL script dialects

Most database applications have backend administration utilities, which must execute SQL script. These scripts are written using lines appropriate to the DBMS SQL script syntax.

 

TADScript is the AnyDAC SQL script processor. It has many advantages over standard utilities, like the ability to be completely integrated into the AnyDAC application and the ability to extend the set of commands by custom script commands. The TADScript component is aware of several industry standard SQL script syntaxes, including:

 

  • Oracle SQL*Plus
  • Microsoft ISQL
  • MySQL mysql.exe / mysqldump.exe
  • Interbase ISQL

 

So, AnyDAC allows the developer to implement flexible backend administration solutions using the appropriate SQL script syntax.

 

For example, the following is a Firebird script, creating a database:

SET SQL DIALECT 3;
SET NAMES UTF8;
SET CLIENTLIB 'C:\fb25\bin\fbclient.dll';
CREATE DATABASE 'E:\Test2.ib'
  USER 'sysdba' PASSWORD 'masterkey'
  PAGE_SIZE 16384
  DEFAULT CHARACTER SET NONE;
 
SET TERM ^ ;
 
CREATE PROCEDURE MY_PROC RETURNS (aParam INTEGER) AS
BEGIN
  aParam = 10;
END^

Quoted Identifiers Awareness

Some database applications use national and/or special characters in the object names. Also, some object names may be case sensitive. Depending on the DBMS SQL dialect in use, such names must be quoted using special quotation characters.

 

AnyDAC provides complete support for quoted names, including meta information retrieval, object name parsing and object name escape sequences.

 

For example, the following code uses object name escape sequence to write a SQL command, independent of the DBMS SQL dialect quotation rules:

 

SELECT * FROM {id My TABLE}

Flexible Options System

The more complex a database application gets, the more demands comes to the data access library. Some of these demands may change dynamically. This means, that a data access library must offer options to control all aspects of the library's functionality.

 

AnyDAC has more than 60 options, which control almost all aspects of data access functionality: from fetching rows to applying updates to resources usage and data types handling.

 

The option values are inheritable from manager (the top level component) to connections and datasets, allowing the developer to control behavior on different levels. For example, the FetchOptions.Mode may be set for TADConnection. Then this value will be inherited by all TADQuery's, linked to this TADConnection. And still the applicaton may override the value for any specifc TADQuery.