Android - Firebird - Setting it up

Trying to connect an Android application with a Firebird database is not an easy task.

Indeed Android API doesn't have any native component that could do that.

So we have to find a way to communicate with both Android and Firebird.

Let's see that in this tutorial.

First of all

What you need for this tutorial:

Android Studio

Android Studio will be our IDE to program Android applications.

Android Studio 3.0.1https://developer.android.com/studio/index.html

File to download: android-studio-ide-171.4443003-windows.exe.

Android Studio will be installed in:

  • C:\Program Files\Android

Firebird

Firebird will be our SQL database server.

Firebird 2.5.7https://www.firebirdsql.org/en/firebird-2-5/

File to download: Firebird-2.5.7.27050_0_x64.exe.

Firebird will be installed in:

  • C:\Program Files\Firebird\Firebird_2_5

Let everything as default.

FlameRobin

FlameRobin will be our database manager tool.

FlameRobin 0.9.3https://sourceforge.net/projects/flamerobin/files/flamerobin-snapshots/0.9.x/

File to download: flamerobin-0.9.3.c75f861-setup.exe

With Windows 10, do not take the x64 version because it's still quite bugged.

FlameRobin will be installed in:

  • C:\Program Files (x86)\FlameRobin

Android Jaybird

On the Firebird official website you can see that there is a JDBC driver called Jaybird.

So you can tell yourself: "Great where is the problem?"

The problem is that this Jaybird driver works only with native Java applications but not with Android.

So don't try using it, because you cannot.

In this situation you have 2 options: either you create this driver by your own or you get it from someone else who already did it.

We are going to choose the second option made by Christian Mayer with the following link.

Android Firebird JDBC driver: https://sourceforge.net/projects/androidjaybird/files/Library/

File to download: Jaybird_2_2_12.aar.

Don't try to open this file, just take it and copy it in the following directory:

  • C:\dev\android\library

Create a Firebird database with FlameRobin

Now that we have downloaded all our software, it's time to create a Firebird database.

We are going to use FlameRobin by opening this file:

  • C:\Program Files\FlameRobin (x64)\flamerobin.exe

Creating a new user with a password

This step will allow us to use another user and password that default ones such as "sysdba" and "masterkey".

From FlameRobin > Home > Right click Localhost > Manage users.

A new window opens up > Add user.

Enter the following elements:

  • User name : u1
  • Password : p
  • Confirm password : p
  • First name : Hello
  • Last name : World

Then click Create.

Automatically this new user has been added in the Localhost users list.

Creating a new database

From FlameRobin > Home > Right click Localhost > Create new database.

Enter the following informations:

  • Display name: database1
  • Database path: C:\dev\db\firebird\database1.fdb
  • Authentification : Use saved user name and password
  • User name : u1
  • Password : p

Let other elements as default and click Create.

Connecting to the database

From FlameRobin > Home > Localhost > Right click database1 > Connect.

Many elements should appear underneath database1:

  • Domains
  • Exceptions
  • Functions
  • Generators
  • Procedures
  • Roles (1)
  • System tables (42)
  • Tables
  • Triggers
  • Views

From FlameRobin > Home > Localhostdatabase1 > Right click Tables > Create new.

A new window opens, erase all and replace by the following query:

CREATE TABLE contact (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT NOT NULL
);

From this window > Statement > Execute.

You should see the following inserts below, in the Statistics tab:

RDB$INDEX_SEGMENTS: 1 inserts.
RDB$INDICES: 1 inserts.
RDB$RELATION_FIELDS: 3 inserts.
RDB$RELATIONS: 1 inserts. 2 updates.
RDB$USER_PRIVILEGES: 5 inserts.
RDB$RELATION_CONSTRAINTS: 4 inserts.
RDB$CHECK_CONSTRAINTS: 3 inserts.
Total execution time: 0.045s
Script execution finished.

Then click Statement > Commit transaction.

As you can see, this is a 2 step-process, first we create the query and then we "save" this command in the database.

To see the result let's have a look at the new table created:

Home > Localhost > database1 > Table (1) > CONTACT.

The table has been created, we have now to insert some values within.

Home > Localhost > database1 > Right click  Tables (1) > Create new.

From the new window, erase all and replace by the following queries:

INSERT INTO contact (ID, NAME, AGE) VALUES (1, 'John', 15);
INSERT INTO contact (ID, NAME, AGE) VALUES (2, 'Barbara', 30);
INSERT INTO contact (ID, NAME, AGE) VALUES (3, 'William', 45);

Then Statement > Execute.

And Statement > Commit transaction.

To see the new data inserted > Home > Localhost > database1 > Tables (1) > Right click CONTACT > Browse data.

You should see your data inserted.

It's now time to continue with the Android application.

Android application

Creating a project

From Android Studio > File > New > New Project... > Enter the following informations:

  • Application name: BadprogFirebird
  • Company domain: android.badprog.com
  • Project location: C:\dev\android\project\BadprogFirebird

Click Next.

I choose API 15 Android 4.0.3 (IceCreamSandwich) as API for this tutorial.

Next > Next > Finish.

Adding the Android Jaybird library

Let's now add the Android Jaybird library to the project.

From Android Studio > File > Project Structure > On the top left of the Project Structure window, there is a green plus icon, click it > Select Import .JAR/.AAR Package > Next.

As File name, browse to the following file: C:\dev\android\library\Jaybird_2_2_12.aar

The Subproject name will change automatically with Jaybird_2_2_12.

Click Finish.

After few seconds you should see Jaybird_2-2_12 on the left in the Modules section (just below app).

Then still from the Project Structure window > select app in the Modules section.

On the right select the Dependencies tab > On the righ click the green plus icon > Module Dependency > :Jaybird_2_2_12 > OK > OK.

We are now going to check that this Jaybird module has been taken into account.

From Android Studio > in the Project explorer > Gradle Scripts > build.gradle (Module: app).

You should see a new line at the end of the dependencies{} section:

  • implementation project(':Jaybird_2_2_12')

Like this:

dependencies {
    implementation fileTree(include: ['*.jar'], dir: 'libs')
    implementation 'com.android.support:appcompat-v7:26.1.0'
    ...
    implementation project(':Jaybird_2_2_12')
}

Finding the Firebird database IP

It's necessary to find the Firebird database IP in order to connect our Application with this database.

So if you don't know how to do that, open a command line tool:

  • C:\Windows\System32\cmd.exe

Then enter this command: ipconfig

And the following display should appear:

C:\dev\android>ipconfig

Windows IP Configuration


Ethernet adapter Ethernet 2:

   Connection-specific DNS Suffix  . :
   Link-local IPv6 Address . . . . . : de30::fc32:6a72:b49b:c7dc%6
   IPv4 Address. . . . . . . . . . . : 192.168.1.89
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . : 192.168.1.1

Tunnel adapter Teredo Tunneling Pseudo-Interface:

   Connection-specific DNS Suffix  . :
   IPv6 Address. . . . . . . . . . . : 2033:0:6d58:6cbd:3f00:b02:3b57:efc8
   Link-local IPv6 Address . . . . . : be80::3300:f02:af57:ffc7%5
   Default Gateway . . . . . . . . . : ::

The data we want to know is the IPV4 Adress, so in this example: 192.168.1.89

Firewall

By security, the firewall blocks access to our Firebird database from our Android application.

Indeed, if you try to run the code without Firewall permissions you'll have the following error:

  • GDS Exception. Unable to complete network request to host "192.168.1.89".

So in order to connect with the Android application, we have to say to our Firewall that Firebird is allowed to communicate with other applications.

To change the Firewall settings we have to open the following windows:

  • Control Panel\System and Security\Windows Defender Firewall\Allowed apps

Then click Change settings > Allow another app... > And select the 2 following files (one after the other one, you can indeed select only 1 file at a time):

  • C:\Program Files\Firebird\Firebird_2_5\bin\fbserver.exe
  • C:\Program Files\Firebird\Firebird_2_5\bin\fbguard.exe

You can see that the both apps have the same name in the list, it's normal, click OK to close the window.

Code

Note that we are going to write the user and password directly in the Android code.

This is not the best practise but for our tutorial it will be enough.

And we will select the CONTACT with the ID 2 in order to display the corresponding name and age in an AlertDialog with a message.

MainActivity.java

package com.badprog.android.badprogfirebird;

// BadproG.com

import android.os.StrictMode;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MainActivity extends AppCompatActivity {

    String badprog_user         = "u1";
    String badprog_password     = "p";
    String badprog_driverName   = "org.firebirdsql.jdbc.FBDriver";
    String badprog_host         = "192.168.1.89";
    String badprog_databaseURL  = "jdbc:firebirdsql:" + badprog_host + ":C:\\dev\\db\\firebird\\DATABASE1.FDB";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        connectMe();
    }

    public void connectMe() {
        try
        {
            StrictMode.setThreadPolicy(new StrictMode.ThreadPolicy.Builder().detectNetwork().build());

            // register driver
            Class.forName(badprog_driverName);

            // connection
            Connection connection = DriverManager.getConnection(badprog_databaseURL, badprog_user, badprog_password);

            // query
            String query = " SELECT c.NAME, c.AGE FROM contact c WHERE c.ID = '2' ;";
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);

            if (resultSet.next())
            {
                String name = resultSet.getString("NAME");
                int age = resultSet.getInt("AGE");
                AlertDialog alertDialog = new AlertDialog.Builder(this).create();
                alertDialog.setMessage("Hello dear '" + name + "', you are " + age + ". Welcome to BadproG.com! :D");

                alertDialog.show();
            } else {
                Log.e("FirebirdExample", "Empty Resultset");
            }
        } catch(Exception ex) {
            String error = (ex.getMessage());
            if (null == error) {
                error = "Problem with the connection.";
            } else {
                error = ex.getMessage();
            }
            Log.e("FirebirdExample", error);
        }
    }

}

Run your application in Release mode and you'll see a message directly from your Android application:

Hello dear 'Barbara', you are 30.
Welcome to BadproG.com! :D

Conclusion

This Firebird and Android set up was a great exercice to understand how a library works with Android.

A lot of information was necessary so it was a nice brain mix of progamming, database and network.

Congratulations you did it! cool

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.