Working with enterprise or workgroup level DBMS, it is normal, that DBA is carying about database. His (her) duty includes backup, restore, repair, etc database tasks. With desktop or embedded DBMS often your application user is the DBA. AnyDAC for Delphi v 4 introduces API to these tasks.

Introduction

The beta AnyDAC for Delphi v 4.0.1.1453 introduces 14 new components located on the "da-soft AnyDAC Services" component palette page. We are calling them service components. All of them are working directly with AnyDAC drivers and extend the unified drivers API by the non-unified DBMS specific functionality.

Firebird / Interbase services

For those of you, who are comming from IBX, IBO or FIBPlus libraries to AnyDAC, the FB/IB service components are already familar. In general, the FB/IB API offers a set of routines allowing to perform backup, restore, repair, validate database, user management, license management, etc tasks. And those libraries are offering a set of components wrapping these tasks. Now we are filling this gap too.

At first, you can read details in updated AnyDAC Online Documentation. Here we just want to show the example performing a database backup:

 
ADIBBackup1.DriverLink := ADPhysIBDriverLink1;
 
ADIBBackup1.UserName := 'sysdba';
ADIBBackup1.Password := 'masterkey';
ADIBBackup1.Host := 'db_srv_host';
ADIBBackup1.Protocol := ipTCPIP;
 
ADIBBackup1.Database := 'e:\ib\addemo.fdb';
ADIBBackup1.BackupFiles.Add('e:\ib\addemo.backup');
 
ADIBBackup1.Backup;

Not much to add to this code :) Just note, that you can use as classic gbak functionality (see above sample), as the TADIBNBackup component. The last offers the nbackup functionality. There are corresponding restore components and many others.

Microsoft Access services

Who are working with the Microsoft Access database are aware of the fact, that periodically your application should compact the database. And less often but still to repair it. For that now you can use the TADMSAccessService component. And the sample:

 
ADMSAccessService1.DriverLink := ADPhysMSAccDriverLink1;
ADMSAccessService1.Database := 'c:\test.mdb';
ADMSAccessService1.Repair;

Really very simple. It is an office and home database, so every one housewife must be able to repair her iron and MDB file :)

SQLite services

The SQLite database also requires backup / restore operations, and the TADSQLiteBackup component performs these tasks. The documentation describes the SQLite sevices in details.

But SQLite is quite different from the above databases. Actually it is more embedded, than all other DBMS supported by AnyDAC. It allows you to create and register your own Delphi coded functions and call them from SQL. The following example shows how to create a custom function performing just multiplication of her two arguments:

ADSQLiteFunction1.DriverLink := ADPhysSQLiteDriverLink1;
ADSQLiteFunction1.FunctionName := 'MyFunc';
ADSQLiteFunction1.ArgumentsCount := 2;
ADSQLiteFunction1.Active := True;
ADSQLiteFunction1.OnCalculate := ADSQLiteFunction1Calculate;
 
procedure TForm1.ADSQLiteFunction1Calculate(AFunc: TSQLiteFunction;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
  AOutput.AsInteger := AInputs[0].AsInteger * AInputs[1].AsInteger;
end;
 
procedure TForm1.Button1Click(Sender: TObject);
begin
  // demo query
  ADQuery1.Open('select RegionID, MyFunc(RegionID, 10) from "Region"');
end;

SQL Anywhere services

The SQL Anywhere DBTOOLn.DLL offers a set of routines allowing to perform backup, validate, create database, license management, etc tasks. It seems, there are no other ways to use thoose API's as through AnyDAC only. So, we are first here :)

As first step, we implemented Backup and Validate database tools. You can read details in updated AnyDAC Online Documentation. And one more example performing a database backup:

ADASABackup1.DriverLink := ADPhysASADriverLink1;
ADASABackup1.ConnectParams := 'ENG=addemo_asa11;DBN=addemo_asa11;UID=DBA;PWD=sql';
ADASABackup1.OutputDir := 'c:\temp\db';
ADASABackup1.Flags := [bfBackupDB, bfBackupLog];
ADASABackup1.OnProgress := ADASABackup1Progress;
ADASABackup1.Backup;

What next ?

The similar services in different DBMS are very different by the arguments and behaviour. As result, we have not unified the service components. There is no common API, excluding may be just main method names, like a Backup or Restore.

The number of services offered by a typical DBMS may be quite big. We will continue with the Firebird, Interbase and SQL Anywhere services but mostly on the customer requests. Later we will see, how we can help to Oracle, SQL Server and other DBMS users too.