Not so long ago, in RAD Studio XE5 there was introduced support for Android application development. On the Internet there can be found a lot of information about Android application development in Delphi XE5, but when it comes to development of database applications, it becomes much more difficult. The point is that the components and drivers included in the RAD Studio XE5 distribution (for example, FireDAC and dbExpress drivers) have severe limitations: on Android and iOS platforms, they support just 2 databases — SQLite and InterBase. And what to do when you develop a serious business-application, that requires remote connection to Oracle, MySQL or PostgreSQL from Android? We have come across such questions on various forums dedicated to database application development for Android and decided to reveal the topic as detailed as possible.
Components
Let’s start with description of the components we will use:
- UniDAC – it is universal data access components that allow to connect to SQLite, Oracle, MySQL, PostgreSQL, and InterBase from Android (you can use UniDAC for connection from Windows to almost all the known databases, but this is beyond the scope of this blog).
If you want to work not with all those databases, but with a particular one, you can use more specific components:
- LiteDAC – SQLite Data Access Components
- ODAC – Oracle Data Access Components
- MyDAC – MySQL Data Access Components
- PgDAC – PostgreSQL Data Access Components
- IBDAC – InterBase Data Access Components
All these components also support Android as a target platform in RAD Studio XE5.
Connection to database
Work with databases on Android in general is no different from the one on Windows, but there are some nuances when setting connection and deploying files to a mobile device, if you work with a local DB. We will consider here how to establish connection to each supported database.
SQLite
If you not deploy the database file to a mobile device, you should set:
ForceCreateDatabase := True
In this case, on the first application launch, a database file will be created automatically.
LiteDAC sample
var
Connection: TLiteConnection;
begin
Connection := TLiteConnection.Create(nil);
try
Connection.Options.ForceCreateDatabase := True;
Connection.Database := IncludeTrailingPathDelimiter(TPath.GetDocumentsPath) + 'db.sqlite3';
Connection.Connect;
finally
Connection.Free;
end;
end;
UniDAC sample
var
Connection: TUniConnection;
begin
Connection := TUniConnection.Create(nil);
try
Connection.ProviderName := 'SQLite';
Connection.SpecificOptions.Values['ForceCreateDatabase'] := 'True';
Connection.Database := IncludeTrailingPathDelimiter(TPath.GetDocumentsPath) + 'db.sqlite3';
Connection.Connect;
finally
Connection.Free;
end;
end;
Oracle
It is impossible to install Oracle client to a mobile device, because Oracle client for Android simply doesn’t exist. Therefore the only way to establish connection to Oracle from a mobile device is to connect directly via TCP/IP. For this, the Direct option should be set to True:
Direct := True;
In addition, the server name must be generated correctly, since, if we have no client, we have no tnsnames.ora file with the server list as well. Therefore, to establish connection from Android, we need to know the server Host and Port, as well as its SID or Service Name.
To connect via the SID, the server should be set in the following way:
Server := 'Host:Port:sid=SID';
or a simplified way:
Server := 'Host:Port:SID';
To connect via the Service Name – as follows:
Server := 'Host:Port:sn=SID';
In other words, the ‘sid=’ prefix of the third parameter indicates that connection is established via the SID, and the ‘sn=’ prefix indicates that connection is established via the Service Name. If no prefix is specified, then, by default, it is considered, that we want to establish connection via the SID.
The majority of Oracle servers have the same SID and Service Name, so you, most likely, won’t have to go into such nuances, since you can learn more about this in the Oracle documentation.
ODAC sample
var
Session: TOraSession;
begin
Session := TOraSession.Create(nil);
try
Session.Options.Direct := True;
Session.Server := 'server:1521:orcl';
Session.Username := 'user_name';
Session.Password := 'password';
Session.Connect;
finally
Session.Free;
end;
end;
UniDAC sample
var
Connection: TUniConnection;
begin
Connection := TUniConnection.Create(nil);
try
Connection.ProviderName := 'Oracle';
Connection.SpecificOptions.Values['Direct'] := 'True';
Connection.Server := 'server:1521:orcl';
Connection.Username := 'user_name';
Connection.Password := 'password';
Connection.Connect;
finally
Connection.Free;
end;
MySQL
MySQL client software for Android also doesn’t exist, therefore connection to MySQL server will also be established directly via TCP/IP. For this, let’s set the corresponding option:
Direct := True;
MyDAC sample
var
Connection: TMyConnection;
begin
Connection := TMyConnection.Create(nil);
try
Connection.Options.Direct := True;
Connection.Server := 'server';
Connection.Port := 3306;
Connection.Database := 'database_name';
Connection.Username := 'user_name';
Connection.Password := 'password';
Connection.Connect;
finally
Connection.Free;
end;
end;
UniDAC sample
var
Connection: TUniConnection;
begin
Connection := TUniConnection.Create(nil);
try
Connection.ProviderName := 'MySQL';
Connection.SpecificOptions.Values['Direct'] := 'True';
Connection.Server := 'server';
Connection.Port := 3306;
Connection.Database := 'database_name';
Connection.Username := 'user_name';
Connection.Password := 'password';
Connection.Connect;
finally
Connection.Free;
end;
end;
PostgreSQL
With PostgreSQL, everything is more simple. Since PgDAC and UniDAC only allow establish direct connection via TCP/IP, it is enough for us to specify the Server and Port and perform Connect.
PgDAC sample
var
Connection: TPgConnection;
begin
Connection := TPgConnection.Create(nil);
try
Connection.Server := 'server';
Connection.Port := 5432;
Connection.Database := 'database_name';
Connection.Schema := 'schema_name';
Connection.Username := 'user_name';
Connection.Password := 'password';
Connection.Connect;
finally
Connection.Free;
end;
end;
UniDAC sample
var
Connection: TUniConnection;
begin
Connection := TUniConnection.Create(nil);
try
Connection.ProviderName := 'PostgreSQL';
Connection.Server := 'server';
Connection.Port := 5432;
Connection.Database := 'database_name';
Connection.SpecificOptions.Values['Schema'] := 'schema_name';
Connection.Username := 'user_name';
Connection.Password := 'password';
Connection.Connect;
finally
Connection.Free;
end;
end;
InterBase
Using InterBase ToGo, you can connect to both local or remote DB.
To connect to a local db, just the path to the local db on the device should be set:
Database := IncludeTrailingPathDelimiter(TPath.GetDocumentsPath) + 'db.gdb';
If you need to establish connection to a remote server, you should specify not only the database, but the server as well:
UniConnection.Server := 'server'; UniConnection.Database := 'C:\db.gdb';
Please note that the IncludeTrailingPathDelimiter(TPath.GetDocumentsPath) prefix should be specified when connecting to a local DB, and it is not needed for connection to a remote database.
IBDAC local database sample
var
Connection: TIBCConnection;
begin
Connection := TIBCConnection.Create(nil);
try
Connection.Database := IncludeTrailingPathDelimiter(TPath.GetDocumentsPath) + 'db.gdb';
Connection.Username := 'user_name';
Connection.Password := 'password';
Connection.Connect;
finally
Connection.Free;
end;
end;
IBDAC remote database sample
var
Connection: TIBCConnection;
begin
Connection := TIBCConnection.Create(nil);
try
Connection.Server := 'server';
Connection.Database := 'C:\db.gdb';
Connection.Username := 'user_name';
Connection.Password := 'password';
Connection.Connect;
finally
Connection.Free;
end;
end;
UniDAC local database sample
var
Connection: TUniConnection;
begin
Connection := TUniConnection.Create(nil);
try
Connection.ProviderName := 'InterBase';
Connection.Database := IncludeTrailingPathDelimiter(TPath.GetDocumentsPath) + 'db.gdb';
Connection.Username := 'user_name';
Connection.Password := 'password';
Connection.Connect;
finally
Connection.Free;
end;
end;
UniDAC remote database sample
var
Connection: TUniConnection;
begin
Connection := TUniConnection.Create(nil);
try
Connection.ProviderName := 'InterBase';
Connection.Server := 'server';
Connection.Database := 'C:\db.gdb';
Connection.Username := 'user_name';
Connection.Password := 'password';
Connection.Connect;
finally
Connection.Free;
end;
end;
Deployment to mobile device
In order for our application to be able to work with local SQLite and InterBase ToGo databases, we should make sure these databases are deployed to an Android device. Nothing difficult at this, since the deployment process is similar in both Delphi XE4 and Delphi XE5. First we should call the Project->Deployment menu:
After this add our databases for SQLite and InterBase to the list of files, that must be deployed to an Android device together with your application:
Please note that the deployment path is different for Android and iOS. If you want to deploy your application to both platforms, then make sure the deployment paths are specified correctly for both of them.
NOTE: Dont forget to change the Remote Path default value . with one of the described above.
| Deployment Path | Destination on Device | |
|---|---|---|
| TPath.GetDocumentsPath | .\assets\internal | /data/data/com.embarcadero.MyProjects/files |
| TPath.GetSharedDocumentsPath | .\assets | /mnt/sdcard/Android/data/com.embarcadero.MyProjects/files |
