How to Develop Android Database Applications in RAD Studio XE5

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.

Debug

We won’t focus on the debug process in this article, since it was described in details in the previous article: Remote Debug of Android Applications in RAD Studio XE5 via Wi-Fi

Conclusion

For the time being, with the RAD Studio XE5 release, Devart Data Access Components allow to connect to SQLite, Oracle, MySQL, PostgreSQL and InterBase from both mobile platforms: Android and iOS. But we are not going to stop. Many users ask us to support connection to SQL Server from Android and iOS – and we will make every effort to give them such a capability.

Update

We are glad to inform, that we have supported connection to SQL Server and SQL Azure from iOS and Android.