Wandering Data

Now that SQL Anywhere has support for Android, this blogger will finally get his act together enough to post some new material. This is the first of a few posts for those new to Android spelling out some techniques for writing database applications on that platform.

UltraLite is the mobile database management system that is included in SQL Anywhere. This post is a Hello World for UltraLite on Android. It assumes that you have what you need to develop Android applications (Eclipse and the Android SDK), and that you have SQL Anywhere installed (perhaps the Developer Edition free download). It covers setting up a project, and carrying out simple database access, but doesn’t cover data synchronization (sharing data between device and a backend system) which is the primary reason for many people to choose SQL Anywhere.

First, create an Android Project. Here are the settings I’ll be using.

Once you have created the project you should be able to run this default application in an emulator (or on a device) and see that it shows a screen with the words “Hello World, Hello UltraLite” in a textView control. Choose Run > Run As > Android Application.

Next, create a folder named libs\ under the HelloUltraLite folder, and copy the UltraLiteJ files into it. You will need to rename the ARM subfolder to armeabi. Here are the instructions from a command line:

workspace> mkdir HelloUltraLite\libs

workspace> xcopy /s “c:\Program Files\SQL Anywhere 12\UltraLite\UltraLiteJ\Android\*.*” HelloUltraLite\libs

workspace> move HelloUltraLite\libs\ARM HelloUltraLite\libs\armeabi

The UltraLite files include the UltraLite JAR file (which holds the API), the UltraLite database management system, which is a C++ shared object, and the Javadoc documentation files.

Back in Eclipse, add the UltraLite libraries to your project:

In the package explorer press F5 or right click the project and choose Refresh so that the project is aware of the UltraLite files.

Add the UltraLite JAR file to your build path, as in the following dialog. You can also specify the Javadoc location as the libs\HTML subfolder of your project so that you get tooltip-style documentation from the edit window:

Here is what the Package Explorer looks like, with the libs folder included.

The next step is to add some code to the com.sybase.com.HelloUltraLite.java file to create and connect to a database. Most of the time you would keep your data access code in a separate class, but for this simple case we are going to keep everything in one file.

Here are the imports that the file needs:

import java.util.Random;
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;
import com.ianywhere.ultralitejni12.*;

When Android starts the application in response to a tap of the icon, the entry point is the HelloUltraLite onCreate() method, so that is where we will put the database access code. Here is the complete onCreate() method:

public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
     try{
        // Define the database properties using a Configuration object
        ConfigFileAndroid config = DatabaseManager.createConfigurationFileAndroid("hello.udb", this);
        // Connect, or if the database does not exist, create it and connect
        try{
            _conn = DatabaseManager.connect(config);
        } catch ( ULjException e) {
            _conn = DatabaseManager.createDatabase(config);   Â
        }
 
        // Create a table T1 in the database if it does not exist
        StringBuffer sb = new StringBuffer();
        sb.append("CREATE TABLE IF NOT EXISTS T1 (C1 integer primary key default autoincrement, C2 integer )");
        PreparedStatement ps = _conn.prepareStatement(sb.toString());
        ps.execute();
        ps.close();
           
        // Insert a row into T1
        sb = new StringBuffer("INSERT INTO T1 (C2) ON EXISTING SKIP VALUES ( ? )");
        ps = _conn.prepareStatement(sb.toString());
        ps.set(1, new Random().nextInt());
        ps.execute();
        ps.close();
        _conn.commit();

        // Select the values from C2 and show them in the user interface
        sb = new StringBuffer("SELECT C2 FROM T1");
        ps = _conn.prepareStatement(sb.toString());
        ResultSet rs = ps.executeQuery();
        StringBuffer c2 = new StringBuffer();
        while(rs.next()){
            c2.append(String.valueOf(rs.getInt(1)));
            c2.append(",");
        }
        TextView tv = (TextView)findViewById(R.id.textview1);
        tv.setText(c2.toString());
    } catch ( ULjException e) {
        Log.e("HelloUltraLite", e.toString());
    }
}

The class R is the Resource class, and includes the user interface and any strings.

When this application is run, it carries out the following tasks:

1. Connects to a database, or creates the database if it does not exist (and returns a connection)
   
2. Creates a table T1 if it does not already exist
   
3. Inserts a row into T1.
   
4. Selects all the rows from T1, concatenates them into a string, and displays that string in the TextView control that is included in the default user interface.
   
5. If there is an error, a message is written out to the LogCat window in the Eclipse user interface.
   

Here is the Android application after running the application a few times, showing the random numbers inserted into the table.

And that completes the tutorial.