How do I connect to a database that supports ODBC from Android?

This tutorial explains how to connect to a remote ODBC database from Android. Although the example ODBC database in the tutorial is an Excel spreadsheet on Windows, the method described here can be used to connect Android to ODBC databases on Linux and UNIX platforms as well.

Prerequisites

We used the following software on a Windows machine:

  1. Eclipse SDK
  2. Android SDK
  3. valid Java package name.

    Click Next to progress through the wizard, and then click Finish.

  4. In Package Explorer, expand the new project. Right-click AndroidManifest.xml and choose Open With > Text Editor.
  5. In AndroidManifest.xml, add the following line after your <manifest> tag:
    <uses-permission android:name="android.permission.INTERNET"/>

    This allows your mobile device to open network sockets. (You need to be able to access the JDBC-ODBC Bridge server on TCP port 8831 from your mobile device.) Save the changes.

  6. Right-click on your project and select Import. Choose File System and browse to the folder that contains your EJOB.jar file.

    This can be located on the machine running the JDBC-ODBC Bridge server.

    The default location on a 32-bit machine for EJOB.jar is:

    drive:\Program Files\Easysoft\Easysoft JDBC-ODBC Bridge\Jars

    The default location on a 64-bit machine for EJOB.jar is:

    drive:\Program Files (x86)\Easysoft\Easysoft JDBC-ODBC Bridge\Jars

    Click to select EJOB.jar in the JAR file list.

    Ensure that the Into Folder value is your project's src folder. For example, ODBCAndroid/src.

  7. Add the EJOB.jar to your Eclipse build path. To do this, right-click on your project, and chose Build Path > Add External Archives. Double-click the EJOB.jar file.
  8. Expand the src tree, and then double-click on the MainActivity.java file.

    In MainActivity.java, replace the boilerplate code with this code:

    package com.easysoft.ODBCAndroid;
    
    import android.app.Activity;
    import android.graphics.Color;
    import android.os.Bundle;
    import android.widget.TextView;
    import easysoft.sql.*;
    import java.sql.*;
    
    public class MainActivity extends Activity {
    
        /* Called when the activity is first created. */
        @Override
        public void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            /* To prove this works, we will save the output to a string and use setContentView to display the information */
            TextView tv = new TextView(this);
            String HWOutput = "";
            try {
                /* Create a JDBC-ODBC Bridge connection. You need to replace myjobmachine with the host name or IP address of your JDBC-ODBC Bridge machine */
                String jobUrl = "jdbc:easysoft://myjobmachine:8831/ExcelJDBC-ODBC Bridge";
                HWOutput += jobUrl + "\n\n";
                /* Connect to the target ODBC driver using the JDBC-ODBC Bridge */
                easysoft.sql.jobDriver driver = (easysoft.sql.jobDriver) Class.forName("easysoft.sql.jobDriver").newInstance();
                java.util.Properties p = new java.util.Properties();
                Connection con = DriverManager.getConnection(jobUrl, p);
                DatabaseMetaData dbMD = null;
                /* Create and execute a query that gets the Excel data */
                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery("select Product, \"Stock Level\" from \"Sheet1$\"");
                String Prod = "";
                String Lev = "";
                /* For each row, place the data into the output string */
                while (rs.next()) {
                    Prod = rs.getString("Product");
                    HWOutput += "Product : " + Prod + " : ";
                    Lev = rs.getString("Stock Level");
                    HWOutput += Lev + "\n";
                }
                con.close();
                HWOutput += "\n";
            } catch (Exception e) {
                /* If there were any errors, store them to the output string */
                HWOutput += e + "\n\n";
                e.printStackTrace();
            }
            /* Display the output */
            tv.setText(HWOutput);
            setContentView(tv);
        }
    }

    Now run the code.