Unified API for access to cross-database features
AnyDAC provides a range of features that help abstract the differences between database
types, making it easy to write code that does not have to be concerned about different
database system dialects or other subtle differences between database types.
Data Type Unification
AnyDAC may return a different data type for a table column depending on the data
type returned by DBMS being accessed. For example, the data type for field type
NUMBER(10,0) in Oracle is ftFMTBcd, and for field type INT in MSSQL it is ftInteger.
So, when a single application supports databases with different DBMS’s, it
must be aware of this issue.
AnyDAC allows you to set up 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 set up 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;
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}
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.
Efficient update command generation
When the database application calls dataset methods like Insert / Edit / Post /
Delete, then the data access components must generate SQL commands updating data.
This process must be aware of database identity fields, sequences, triggers, special
data types (Oracle BLOB / CLOB / BFILE), pessimistic locking commands, etc.
The AnyDAC SQL commands generator is aware of these DBMS features and generates
the most effective SQL command, depending on the current connection DBMS. This reduces
the number of cases where the developer must use hand made 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
Unified error reporting
In general, different DBMS’s will return different errors for logically the
same error. But the database application must recognize certain types of backend
errors, like an unique key constraint violation. It is best if this recognition
is not dependent on the DBMS kind.
AnyDAC combines unification and "personalization" of 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 software
API returns. Unification means that all driver exception classes are inherited from
a single base class that contains driver independent information.
For example, code for handling a unique key violation may look like this:
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;
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 sample uses object name escape sequences to write
an SQL command, independent of the DBMS SQL dialect's quotation rules:
SELECT * FROM {id My Table}
Unified Transaction Support
Many DBMS’s have different transaction options, behavior and control API.
For example, the Interbase DBMS has a very powerful transaction control API, but
other DBMS’s, like Oracle or Microsoft SQL Server have a much more compact
control API. While for Interbase a transaction must be explicitly started to perform
any SQL command, for MySQL it is optional.
AnyDAC offers a unified API for transaction control, allowing you to employ the
full power of 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 Interbase database application development.
