Enterprise J2ME Developing Mobile Java Applications [Electronic resources] نسخه متنی

11.7 iAnywhere Solutions SQL Anywhere Studio

The iAnywhere Solutions is a subsidiary of Sybase. According to a 2002 Gartner Group survey, iAnywhere Solutions has a market share of 73 percent in the mobile database sector. Much of the market share comes from databases that run on laptop computers. But iAnywhere Solutions also has a formidable presence in the emerging market of databases for devices. The iAnywhere Adaptive Server Anywhere (ASA, v8.0) is a fully featured database product. ASA runs on Windows CE devices all the way up to multiple CPU boxes with 64 GB memory. It supports advanced features such as Java stored procedures, transactions, multiple indices, and encryption. However, the ASA database requires at least 3MB of memory footprint. That is too heavy for many mobile devices.

In reality, almost no mobile client requires all the advanced features provided by the ASA. Each application uses a small but different set of features. The ASA is a generic database that tries to satisfy all users. The iAnywhere UltraLite Deployment Option is a highly innovative solution to compromise between features and footprint. The core idea behind the UltraLite option is to generate a custom database for each application. The custom database contains only the exact features the application requires. The UltraLite deployment option has a footprint ranging from 200KB to 500KB depending on the selected features. iAnywhere SQL Anywhere Studio provides excellent tools (Sybase Central administration panel) for custom database generation.

The following brief tutorial is based on the JavaTutorial sample that comes with the SQL Anywhere Studio software. The overall process is illustrated in Figure 11.2.

11.7.1 Use an UltraLite Custom Database

The following step-by-step instructions illustrate how to build and use an UltraLite custom database for our mobile applications.

Build a reference database (from a SQL script or through the administration panel GUI) on the server side. This database has the same schema as our mobile deployment database.

Add all required SQL statements into the reference database through the GUI. Those SQL statements should cover all the database operations that we use in the application. We should also assign each of those statements a name. For example, we can assign a name INSERT_PRODUCT to the following statement.

INSERT INTO Product (prod_id, price, prod_name) VALUES (?, ?, ?)

Run the UltraLite Analyzer command-line tool to generate the custom database in a supported implementation platform. We can generate C/C++ native implementations for Palm OS, Windows CE, Symbian OS, and VxWorks devices. Or, we can generate a crossplatform Java implementation (PersonalJava). In this chapter, we focus on Java databases.

The UltraLite Analyzer generates two Java source code files. One file (SampleDB.java) contains the custom database implementation. The other file (ISampleSQL.java) contains interfaces to the predefined SQL statements in step 2.

We can obtain a JDBC connection to this custom database using the following code snippet. By default, an iAnywhere UltraLite database is transient: it is erased when the application closes it. The top two lines of code specify that the SampleDB database needs to persist as a file.

java.util.Properties p = new java.util.Properties();
p.put("persist", "file");
SampleDB db = new SampleDB(p);
Connection conn = db.connect();

We use common JDBC APIs to access the database. We should only use SQL commands predefined in the interface file (ISampleSQL.java) to ensure that the application does not reach any functionalities that are not built into this custom database. For example, the following code snippet can be used to add a product entry.

PreparedStatement pstmt = conn.prepareStatement (INSERT_PRODUCT);
pstmt.setInt(1, 1);
pstmt.setInt(2, 400);
pstmt.setString(3, "Drywall");