Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Mimer SQL/MIDP-programming on Symbian
Categories: JDBC, Mobile, Programming Examples
Introduction

MIDP (Mobile Information Device Profile) is a variant of Java targeted at telephone devices. As such, MIDP, is a limited platform offering only a tiny set of features which is thought to be useful when programming for mobile telephones.

Mimer SQL is available on MIDP-platforms. Programmers may write MIDP-applications which access both local Mimer SQL Mobile databases as well as remote Mimer SQL Engine databases. The project described in this document develops a small application which accesses telephone numbers in a local Symbian database. That is, the database is located on the telephone itself. There is, however, some work involved in getting the application to work with a remote database, more on that later.

Indeed, most telephones today can store a number of telephone numbers in their internal data structures. This little project is not intended to be inventive, its purpose is merely to show how a simple MIDP-application is created which is doing something well known.


MIDP development process

As can be read in the Mimer SQL Mobile manual, the recommended development process for MIDP applications is to follow these steps:
  1. Design and create your database schema on a desktop computer using a the Mimer SQL Mobile development environment.

  2. Design and create the client application. Use the Sun J2ME Wireless Toolkit (or similar tool) to compile and test the application. At this stage, use the desktop Mimer SQL Mobile database for faster testing. The desktop Mimer SQL Mobile database may be run in a mode exposing the identical set of features as is available on the telephone.

  3. The application may also be tested using the Symbian SDK emulator. During this phase, use the same desktop Mimer SQL Mobile database for testing as in step 2 above.

  4. As a final stage, install both the database and the application on the telephone. Test the application against the local database.

  5. Repeat any step as appropriate.


Designing and creating the database schema

Designing database schemas can be either a complicated or a more relaxed task. In this case, we just want a database which can store names of persons, and their telephone number.

Using these requirements, we simply create a table with two columns, one for each of these two attributes. After having logged into my desktop Mimer SQL Mobile I issued the following SQL statements:

create databank PHONEDB;
create table PHONE (NAM char(30),PHN char(20));


After having decided that I am happy with the database schema, I import my telephone numbers from a flat file using the Mimer Load/Unload tool, called mimload. The numbers supplied with this demo contains about 10000 persons, with random data so don’t pay too much attention on names and numbers here.

Now, I need to design a query to find my telephone numbers. After having spent some time thinking about my requirements I came up with the following query.
select * from PHONE
where upper(trim(NAM)) like ?
and upper(trim(PHN)) like ?;

In other words, I decided that I want to be able to do a free-text search within the columns of the table. When I felt satisfied with my query, I stored it into the database with the statement below. Since Mimer SQL 9 Mobile databases cannot compile SQL on the fly you are required to define and store statements in advance. In return, the full expressiveness of SQL is available. The compilation process requires significant amount of memory which may not be available on a mobile device, but also the databanks can be better optimized for size if the statements are known in advance.
create statement PHNQRY 
select * from PHONE
where upper(trim(NAM)) like ?
and upper(trim(PHN)) like ?;

The stored statement may be tested using:

execute statement PHNQRY;

If you have unpacked the source code if this project, you may use the Mimer SQL Load command line tool to import the database schema. An example command is shown below:

c:> mimload –d <database> -u <username> -p <password> "load from '\wtk20\apps\mimerdemo\src\schema.sql' log stderr"


Designing and creating the client application

After having finalized and tested the database schema, we turn our attention to the client application. First and foremost we want our application to have the user enter names, either complete names or parts of names, to be searched for in the database. We also allow the user to enter a group code, and telephone numbers to do reverse searches.

After a whole lot of testing we came up with this source. It is a small MIDlet (only about 200 lines of code) which accepts user input on a form and displays a list of phone numbers.

In this case the database operations are isolated in a separate subclass which also runs in its own thread. Keeping database operations in a separate thread is very important when it comes to accessing remote databases, since the network delay may be significant. In this case, when we expect to deploy the database on the device it is not as important but is anyway a recommended practice.

This is the interesting piece of code, which is the implementation of the run()-method of the thread object.

/**
* Each ResultSet object runs the run-method once.
* The run()-method connects to the database, queries it, and
* populates the List with what the query returned.
*/
public void run()
{
Connection con;

/* Specifies the maximum number of telephone numbers to display. */
int entriesToDisplay=20;

/* If this application is to be run against a mobile server,
* such as a Symbian OS server or a development server running in
* Mobile mode, the EXECUTE STATEMENT facility _must_ be used.
* Otherwise, the latter SQL-statement may be used directly.
*/
String sql = "execute statement PHNQRY";

try {
con = mds.getConnection("PHONEUSER","PHONEUSER");
try {
PreparedStatement ps = con.prepareStatement("execute statement PHNQRY");
try {
/* By setting the fetch size we instruct the database server
* to send us result set rows (=telephone numbers) in batches.
* Since we have decided to display only <entriesToDisplay>
* telephone numbers we request only that rows from the server.
* This is essential for query performance and also to avoid
* transferring more data than necessary from the server.
*/
ps.setFetchSize(entriesToDisplay);
ps.setString(1,nameSearch);
ps.setString(2,phonSearch);
ps.execute();
ResultSet rs = ps.getResultSet();
try {
while (rs.next() && entriesToDisplay-->0) {
append(rs.getString(1)+" "+rs.getString(2),null);
}
if (entriesToDisplay==0)
append("More was found...",null);
} finally {
rs.close();
// Make sure the ResultSet object can be GC:ed asap.
rs = null;
}
} finally {
ps.close();
ps = null;
}
} finally {
con.close();
con = null;
}
} catch (SQLException se) {
append(se.getMessage(),null);
}
}

After compiling and running the application in the Sun J2ME Wireless Toolkit emulator, you will hopefully see the following. I will try to search for all Roberts in the database.

image01

After pressing "Browse" (the button on the upper right) the result set is fetched from the server and displayed. Note that the data now comes from the desktop database.

image02

Note that you must change these lines in the source code to accomodate for the specific database, user and password of your choice. These lines are found early on in the class definition.


mds = new MimerDataSource();
mds.setServerName("theserver.mimer.se");
mds.setDatabaseName("mimer92");
mds.setUser("PHONEUSER");
mds.setPassword("PHONEUSER");


Testing for database deployment on handsets
The difference between the PC-based development environment and the deployed database on the handset is basically that the development environment is a fullblown Mimer SQL server. It allows you to change database schema, compile SQL statements dynamically and so forth.

If you are developing an application which will connect to remote servers over the Internet, this is of no relevance to you, but if you want to deploy the entire database on handsets you may wish to test the application with the development database in mobile mode.

When you are testing your application, there is an option to have the development database server execute in Mobile mode. This means that it exhibits exactly the same set of features as the final deployed database will do. The below dialog, found in the Mimer Administrator, shows how to change the server type to Mobile. Right-click on your database name and select the "Properties" menu choice, then click the "Server" tab to get there.

image03


Testing the application on the Symbian emulator

Some regard this step as unnecessary, since the Sun J2ME Wireless Toolkit provide a pretty good emulator for MIDP applications. If you want to test the applications on the Symbian emulator there is however one thing to point out.

The Java TCP/IP-stack on the Symbian emulator uses the Windows TCP/IP stack directly (when this article was written the Symbian emulator was only available on Windows). This is different from running native (C++) applications since those by default uses an internal Symbian TCP/IP-stack that can only access the host network through a nullmodem cable.

However, having our MIDlets use the Windows TCP/IP stack is perfect for us. At this stage we only want to test the application against the regular Mimer SQL Mobile development environment. It could be possible to test the MIDlet against a database running on the emulator, but since the database server and the MIDlet is not sharing TCP/IP stacks it would be a great undertaking involving nullmodem cables and network routing. I don’t feel that is necessary.

Note!There is an emulator plugin to make it use the Windows TCP/IP stack but we haven’t yet been able to get it working properly for servers serving requests via TCP/IP.

After installing the JAR-archive to the emulator (I copied the JAR to the root of the Symbian emulator file system before I installed it), I can start the application by pressing the application icon. The below is an example of how it looks with a Sony Ericsson P800 skin when I have entered the search name "robert".

image04

After selecting "Browse" I get a list of all Roberts in the phone dictionary.

image05

As before, this query uses the desktop Mimer SQL Mobile development environment.


Installing and testing on the handset

The procedure here can varies a little depending on if you want to install only the MIDlet on the handset, or if you wish to deploy the MIDlet and the database on the devices. In either case, your application may connect remotely if it wishes to do so, but the local database will always be available.

The first subsection just below, MIDlet only installation, will cover installing and testing the MIDlet against a remote database server. The second subsection, Packaging the database for deployment on the handset, will be about preparing and installing the entire database on the handset.

MIDlet only installation
Installing only the MIDlet requires nothing more than placing the compiled and packaged JAR-archive on the telephone and install it. This can be done in several ways. I just place my telephone within infrared range and transfer it. Another way is to download the JAR from a website.

When packaging the MIDlet for distribution it is recommended that you obfuscate the package. This will significantly reduce the size of the archive. It is also the case that the obfuscation process will remove classes and definitions from the Mimer MIDP library that your MIDlet does not use. For instance, some applications does not use the CallableStatement class (it is for dealing with output parameters mostly used in procedure calls). If there are no references to that class within the MIDlet, it may be dropped from inclusion in the final library by the obfuscator.

If you are using the Sun J2ME Wireless Toolkit you may select to obfuscate the MIDlet as seen below. Note that an obfuscator is not included by default in the toolkit. The toolkit relies on third party tools to provide the obfuscator feature. One such tool is ProGuard.

image06

Packaging the database for deployment on the handset
To deploy a Mimer SQL database on the handset, we need two components. First, the database binary, and secondly the database itself. This binary is located in the Mimer SQL install directory. It can be installed onto the handset at any time.

The database itself may not contain any actual data, but it must contain the schema to use. All CREATE TABLE statements must be issued on the development PC before creating a deployment package. When the database is ready for packaging, use the "Export to handheld/emulation" command, right-click mouse on the database in the Mimer Administrator. The entire process is described in the Mimer SQL Mobile manual.

During the packaging process, the database files are internally changed to better suit the Symbian device. This includes, for instance, removing metadata information that is not required on the handset, compressing data, and also recompiling stored statements. Once the database has been packaged for mobile deployment, its schema (the structure of the database) cannot be changed.

image07

During the packaging process, you are instructed to specify a target directory, where packaged files will be placed, and the SYSADM password.

image08

After the packaging process was completed, the following files appeared in the target directory:

image09

These files are:
  • MimerSQLSymbianArm.SIS (or s60mimer.SIS) -- The Mimer SQL runtime binary for UIQ or for Series 60. UIQ or Series 60 depends upon options selected at Export.

  • sysdb92.dbf, transdb.dbf, sqldb.dbf, logdb.dbf -- The system databanks. These are the same databanks as located in the regular desktop database directory, but modified for mobile use.

  • db.dbf -- The user databank. There may be one or more user databanks.

  • mimer_db.SIS -- The SIS file created during the packaging process.

  • mimer_db.pkg -- A Symbian installation file definition. This file is used to create the SIS-file for the system and user databanks.

At the end of the packaging process, Mimer simply uses the Symbian MAKESIS tool to create a SIS file containing the Mimer databanks exported from the development database. Since the package file, mimer_db.pkg, is left in the target directory, this process may be repeated manually if desired.


Adjusting the application to access handset databases

The sample application has no functionality for selecting the target database server. Currently only one database server can reside simultanously on the handset and this database server is attached by default. The database on the Symbian device gets the same name as the development database on the desktop. As you may remember, we earlier configured the DataSource object for use with a remote server - here we will use a subset of that piece of code, as seen below:

...
mds = new MimerDataSource();
mds.setUser("PHONEUSER");
mds.setPassword("PHONEUSER");
...


Packaging the entire application
Unfortunately, it is impossible to create one single package containing MIDP and Symbian components. A MIDP application therefore has to be distributed in two parts, first the MIDP component and then the database server SIS-file.

The MIDP component is easily distributed in the JAR-archive created using the Sun J2ME Wireless Toolkit as described earlier.

The database server components can be packaged in one SIS-file by using the package file BrowsePhoneNumbers.pkg distributed with this article.


The project files

The archive attached to this article, PhoneNumbers.zip, contains the project files for this application. The archive is supposed to be unzipped in a Sun J2ME Wireless Toolkit installation folder, for instance c:\wtk20.

The actual project directory contains the Java and SQL source code in the src-directory. You may use the MAKE_DB.BAT script to load the database.
Links

ProGuard Obfuscator.

Mimer MIDP Javadoc.

Mimer SQL Mobile Manual.

J2ME Mobile Information Device Profile.

Symbian.

Sun Wireless Toolkit.



Last updated: 2007-03-23

 

Powered by Mimer SQL

Powered by Mimer SQL