Feature

For several years we've been creating large applications that tend to encompass sizable feature sets required for enterprise applications. The term fat client comes to mind....Over the years we've seen significant changes in application architectures, from client/server to n-tier to Internet/Web applications. But there's a new area that's growing in use.

The mobile computing revolution allows for freedom from the desktop and for information readily available anywhere, anytime. But the designs and architectures of this form of application development deviate from what we're used to doing. This article is intended to help you understand the steps and issues involved in developing a PalmPilot application using the SQL Anywhere UltraLite Deployment option.

For my example I'll use a prototype application I developed for the State of Utah. Its purpose is to provide current data as well as entry forms to social workers during their visits with children alleged to have been abused.

There are many good reasons for using mobile devices, better known as personal digital assistants, or PDAs. The three top problems they solve are:

1. Decisions in the field are often delayed due to incomplete or inaccurate information.
2. Users spend too much time traveling to get corporate information.
3. Customer, market and operational information could be captured in the field but often isn't due to lack of computing assistance.

The requirements for mobile computing are different from those for other computing architectures. The requirements for users and developers include:

• Small form factor (screen size)
• Ease of use (intuitive interface)
• Costs (low cost of the device)
• Ability to synchronize data to a supported database

What the developer needs to consider include:

• Small "fingerprint" deployment
• No hard disk
• Limited memory (the max is usually 4MB)
• Constantly running applications
• Database connections

Tools Needed for UltraLite Application
The tools we'll be using include Sybase SQL Anywhere (ASA) and Metrowerks CodeWarrior. The ASA version for this application is 6.02 (Sybase improved this version over the first beta that came out with the UltraLite Deployment). The ASA pieces consist of Adaptive Server Anywhere, UltraLite Deployment and MobiLink synchronization for database storage and connectivity. CodeWarrior 5.0 from Metrowerks will be used to create the client application. There are only a few tools available for developing applications for the PalmPilot and CodeWarrior rates at the top.

Mobile Application Development Areas
Several components are required for this application to work, ranging from database storage, SQL statements and business logic to user interface and data synchronization. These areas include:

1. UltraLite embedded database: The mobile database that's in the mobile device
2. Consolidated database: The master database for all users
3. Reference database: The subset of the consolidated database that contains the schema needed for your application
4. Embedded SQL: The communication layer between the UltraLite DB and the PalmPilot application
5. Program logic: The application business logic
6. Program interface: The user front end of the application
7. SQL preprocessor: Translates and analyzes the embedded SQL into "c" code for the application
8. Synchronization scripts: Allow for synchronization of data between the UltraLite application and the consolidated data base

I won't go into detail about these areas; if you'd like to know more, please read Kouros Gorgani's PBDJ article "Bring the Power of Enterprise to Your Palm" (Vol. 6, issue 7). I will talk briefly about the new deployment feature of ASA.

UltraLite Embedded Database
The new piece of technology you may not have heard about is the embedded database for mobile devices. This technology is targeted for Win CE and PalmPilot devices. One of the concerns with a relational database on mobile devices is the size. Most database systems, which include data storage and engines, find it hard to exist in under 4M of space. The UltraLite system can exist in as little as 50K! The functionality in this product includes support for static DML statements, referential integrity, indexes, transaction management, smart data synchronization and runtime functions in ASA. Triggers and stored procedures aren't supported in the mobile database with this version, but they're used for synchronization of the mobile database with the consolidated database.

Following are the steps to create a PalmPilot application.

1. Evaluate the target data needed for the application.
2. Configure the consolidated database for synchronization.
3. Create the reference database.
4. Code the embedded SQL for the application.
5. Create the UltraLite database for the PalmPilot.
6. Design and build the user interface.
7. Code the business logic.
8. Configure the synchronization between the consolidated and UltraLite databases.
9. Set up the deployment of the mobile application.

Target Data Needed
One difference between corporate data and mobile data is the amount of data stored (records) and the columns needed. Typically, individual corporate users don't need ready access to all records in the corporate system. Only the records pertinent to the user need to be available and are therefore the only ones downloaded to the mobile device. This also applies to the columns in the database. If a column isn't needed, it isn't included in the schema for the mobile database. Figure 1 shows the corporate data structure of two of the tables we targeted for this application. Figure 2 shows the UltraLite data structure. As you can see, the worker doesn't need access to many of the columns and they are therefore left out of the mobile database. If this changes over time, the columns will need to be added and the application will have to be regenerated. However, because of space constraints, it's wise to design for the minimum and expand as needed. For data, the synchronization definitions limit the records downloaded to the mobile device.

Set Up Consolidated Database
Because the consolidated database is the master for all users, we need to make sure that it's correctly set up to synchronize the master tables with the UltraLite database on the mobile device. This is where the MobiLink service comes in. This service is located on the database server and enables remote users to receive and send data to the consolidated database when they are connected.

To set up the database correctly, you need to run a script to create the synchronization objects and configure the MobiLink service. The good news is that Sybase created these scripts for you. The only databases they're written for are Adaptive Server Enterprise, ASA, Oracle8, SQL Server7 and DB2. If your consolidated database isn't one of these, you'll need to contact Sybase.

When ASA 6.03 is installed, the default directory for the scripts will be "c:\sybase\Adaptive Server Anywhere 6.0\scripts". The first four letters of the script are "sync". The last part of the file depends on your database vendor. If you're using Sybase Adaptive Server Anywhere, it's "asa". For our project we used Adaptive Server Enterprise, so the file name was "syncase.sql".

Create Reference Database
I created the data definition language (DDL) file from Figure 2 and placed it in the target directory where the database is to be held. Open the DOS command window and change the directory to target directory. To create the needed reference database, execute the following command: "dbinit palmsafe.db". See Listing 1 for the actions resulting from that command.

Configure the ODBC using the following:

• ODBC tab: Data Source Name = PalmSafe
• Login tab: User ID: dba
• Login tab: Password: sql
• Database tab: Start line: dbeng6.exe -c 10M
• Database tab: Database file: <target dir>PalmSafe.db
• Database tab: Check to start database if not running

The database is set up and ready to be used. I used PowerBuilder's database administrator painter to execute the DDL file. I then used the pipeline painter to transfer sample data from the consolidated database to the reference database. An average amount of data will need to be inserted in the reference DB so that performance can be calculated.

Create Embedded SQL
Three files are needed for the database to be created: a platform-specific header file (platform.h), an application header file (palmsafe.h) and application-specific SQL (palmsafe.sqc). Because of how the database engine code itself becomes a part of the mobile device application, all embedded SQL must be static.

Following is a sample of the application header file, which contains the constants, public and private constants, and functions, and a sample of the application-specific SQL.

PalmSafe.h File:

#include "platform.h"
#define SAFE_NAME_SIZE 41

class PalmSafeDB {

public:
PalmSafeDB();
long il_CaseID;
TCHAR is_Address1[35];
TCHAR is_Address2[35];
long il_MinCaseID;

bool GetCase( int skip );

private:
void Rollback( void );

PalmSafe.sqc file:

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
//PalmSafe Variables
long il_CaseID;
TCHAR is_CaseID[8];
TCHAR is_Address1[35];
TCHAR is_Address2[35];
long il_MinCaseID;
EXEC SQL END DECLARE SECTION;

void PalmSafeDB::Rollback( void ) { EXEC SQL ROLLBACK;}

bool PalmSafeDB::GetCase(int skip)
{
EXEC SQL BEGIN DECLARE SECTION;
long offset;
EXEC SQL END DECLARE SECTION;

offset = skip;
EXEC SQL FETCH RELATIVE :offset GetCaseCursor
INTO :il_CaseID, :is_CaseID, :is_Address1, :is_Address2, :is_City,
:is_State, :is_Zip;

if( SQLCODE == SQLE_NOTFOUND ) { return false;}
return true;
}

Create UltraLite Database
Certain commands are executed against the reference database and the palmsafe.sqc file to create the application database. The commands are sqlpp and ulgen. The former translates SQL statements from the sqc file into C language. The latter performs a number of tasks:

1. Loads schema information from reference DB
2. Loads SQL statements that sqlpp inserted into the UltraLite system tables
3. Analyzes and accesses plans
4. Generates C code for SQL statements
5. Generates C code for UltraLite schema
6. Saves generated C code in the reference DB
7. Writes generated C code to the output file called PalmSafe.cpp; if no (-p) is used, the sqlpp automatically calls ulgen utility

sqlpp -c "DSN=PalmSafe;UID=dba;PWD=sql" PalmSafe.sqc (-p PalmSafe)

Because there's only one .sqc file, the sqlpp command automatically calls the ulgen process. Listing 2 gives the execution process that's performed using the foregoing command.

User Interface
Next, create the front end for the mobile application. All data items in Palm applications with CodeWarrior are loosely bound. I used the constructor application from Metrowerks to develop the graphical user interface. Each object on the screen has a unique field ID and several other attributes, as shown in Figure 3. You can easily add objects to the screen by going to the catalog window and dragging an object onto the screen. The mobile device screens are smaller, so you need to be careful of object placement.

Business Logic
The business logic is written in C using CodeWarrior. Listing 3 shows what the code looks like. You can also see, in ShowOrder(), that the data is loosely bound.

Consolidated/UltraLite Database Synchronization
This step allows the MobiLink manager to know which data objects and records to synchronize between the UltraLite database and the consolidated database. To get to the synchronization manager you need to follow the path on your start-up menu: Sybase->Adaptive Server Anywhere-> UltraLite-> Manage MobiLink Synchronization. Once you bring up the application, you'll need to connect to the reference database. The two areas that need attention are the connection and synchronized table scripts.

The connection scripts are for initial connection and setting up connection variables. The minimum variables used in an enterprise application normally include last time connected, user ID and password. The three major scripts are begin_connection, begin_synchronization and begin_download. More connection scripts are available, but they're optional.

The table scripts are specifically designed to target the actual table, as illustrated in Figure 4. You may have noticed that all cursor scripts resemble a select statement. During the ulgen process, the UltraLite generator inserts a default_upload_cursor script and a default_download_cursor script into the reference database. The action of the latter is to download rows of a corresponding table in the consolidated database to the table in the remote database. They specify the select list in the correct order. The upload_cursor script includes the correct WHERE declaration to ensure that only distinct records are to be uploaded. To add another script, just click on the Add Table Script event.

You can get more in-depth information on all synchronization scripts from the UltraLite Developer's Guide, which is installed when you load ASA.

Conduit Manager
This allows the developer to distribute the application from the development environment to the mobile device. The application that's needed to set the configuration is CondCfg.exe (see Figure 5).

The information needed for the conduit configuration tool includes:

• Conduit: Needs to have a dbhsync6.dll file
• CreatorID: Syb2, the Creator in the Metro- werks PalmSafe settings
• Directory: PalmSafe - where the prc file will be located under the Pilot\ps directory (PalmPilot applications have ".prc" extension)
• Remote database: UltraLite_Synch_Syb2
• Name: PalmSafe
• Information: MobiLink conduit, the synchronization service used between the consolidated DB and the UltraLite DB

User Registry Configuration
Changing the user's registry is the last piece of the puzzle. Unfortunately, there is no configuration tool to help you accomplish it. You have to go directly to the registry editor to modify the values. You'll notice in Figure 6 that some items are missing: ConnectString, ConnectionType, Verbose and VerboseRowValue. When turned on, the processes provide additional information to help you troubleshoot any problems during the install or synchronization.

Installation
We've gone through all the steps to allow users to place the application on their mobile device. To install a PalmPilot application, you use the PalmPilot Install tool and add the PalmSafe.prc to the install list. Once installed, you need to synchronize the application twice. The first sync loads the application; the second sync loads the data.

Summary
Whether you're at home, on the road or in the office, data can be in the palm of your hand. Let your imagination run wild with the different applications that can be created for mobile devices needing database synchronization. I can imagine several: banking, stock trading, customer service, human resources, real estate and utility work. I'm sure you can think of more. As companies move toward a mobile workforce, mobile devices will be used to ensure that corporate data can be accessed by that workforce. Using a combination of current data, intuitive interface and mobility makes for a winning combination for any application. This toolset has many features that haven't been tapped yet. I believe you'll enjoy it thoroughly.

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...