Chapter 3: Developing UltraLite Applications

Ultralite Developer's Guide

Chapter 2: A Sample UltraLite Application

Chapter 2

A Sample UltraLite Application

About this chapter

This chapter illustrates some key features of UltraLite by walking through a sample application. The sample application is a simple sales-status application built around a database named CustDB (Customer Database).

The chapter includes information on how to run the sample application, and a brief description of how the application works.

Before you begin

To get the most from this chapter, you should be able to run the sample application as you read.

This chapter assumes that you have read the chapter About UltraLite . Much of the material in this chapter is explained in a more general manner elsewhere in the book. Cross references to these places are provided.

Introduction

CustDB is a sample application included with UltraLite. It is a simple sales-status application that you can run against any of the supported databases, and on any of the supported target operating systems.

By working with the CustDB sample application, this chapter demonstrates the core features of UltraLite.

UltraLite database applications run on small devices using very limited resources. UltraLite applications include a relational database engine.
UltraLite applications share data with a central, consolidated database in a two-way synchronization scheme. The UltraLite databases are also called remote databases.
Each remote database contains a subset of the data in the consolidated database.
The MobiLink server carries out data synchronization between the consolidated database and each UltraLite installation.
SQL scripts stored in the consolidated database implement the synchronization logic.
You can use the Java edition of Sybase Central to browse and edit the synchronization scripts.

The CustDB database

The following figure shows the tables in the CustDB consolidated database and how they relate to each other.

The tables hold the following information.

ULCustomer
A list of customers.
ULProduct
A list of products.
ULEmployee
A list of sales employees. This table is not present in the UltraLite database.
ULEmpCust
A many-to-many relationship between employees and customers. This table is not present in the UltraLite database.
ULOrder
A list of orders, including details of the customer who placed the order, the employee who took the order, and the product being ordered.
ULCustomerIDPool
A table to maintain unique primary keys on the customer table throughout a deployed UltraLite system.
ULOrderIDPool
A table to maintain unique primary keys on the order table throughout a deployed UltraLite system.
ULIdentifyEmployee
This table holds a single row, and is used in the first download of data.

The CustDB application

The application runs on the supported operating systems. You can also run it on Windows NT and Windows 95/98 for the convenience of having everything on a single machine.

When running this application, you are acting as an order taker or sales manager. The application allows you to approve or deny orders and to add new orders.

You can carry out the following tasks with the sample application.

View lists of customers and products.
Add new customers.
Add or delete orders.
Scroll through the list of outstanding orders.
Accept or deny orders.
Synchronize changes with the consolidated database.

When you run the CustDB UltraLite application, you are working on a single remote database, and synchronizing your changes with a consolidated database.

In a typical UltraLite installation, there will be many remote databases, each running on a handheld device, and each containing a small subset of the data from the consolidated database.

File locations for the CustDB application

Your UltraLite installation includes the files needed to run the sample application, and the source code used to develop it. Studying the sample application source code is a good way to learn more about UltraLite.

When you install Adaptive Server Anywhere, the UltraLite sample files are installed into a directory named UltraLite under your Adaptive Server Anywhere installation directory. This is your Ultralite directory.

Runtime files

An Adaptive Server Anywhere version of the customer database is installed as the file custdb.db in the samples\CustDB subdirectory of your UltraLite directory.

This database serves as a consolidated database. It contains the following information:

MobiLink system tables that hold the synchronization metadata
The CustDB data, stored in rows in base tables.
The synchronization scripts.

During the installation process, an ODBC data source named UltraLite Sample is created for this database.

A different executable is supplied for each operating system. These executables are held in a subdirectory of your UltraLite directory named for the operating system.

Palm
Palm Computing Platform.
vc6
Windows CE, Windows 95/98, and Windows NT platforms.

Database source files

In this chapter we use the Adaptive Server Anywhere CustDB database as the consolidated database. You can also build Sybase Adaptive Server Enterprise, Microsoft SQL Server, or Oracle consolidated databases and run the application against those database management systems.

You can use one of the SQL scripts in the samples\CustDB directory to build a consolidated database on each DBMS.

custase.sql
Sybase Adaptive Server Enterprise.
custdb.sql
Sybase Adaptive Server Anywhere.
custmss.sql
Microsoft SQL Server.
custora.sql
Oracle 8.

Application source code

The source code for the sample application is in two parts.

The code that handles the data is held in an Embedded SQL file named custdb.sqc, in the samples\CustDB subdirectory of your UltraLite directory.
UltraLite uses the Embedded SQL programming interface to Adaptive Server Anywhere for its database features.
or information on the UltraLite development process, see Developing UltraLite Applications .
For information on the embedded SQL programming interface, see The UltraLite Embedded SQL Interface .
The user interface for each platform is held in a separate subdirectory of samples\CustDB, named for each version of the application. The following versions are provided:
- Palm
  Palm Computing Platform.
- vc6\CECustDB
  Windows CE.
- vc6\NTCustDB
  A Windows 95/98 and Windows NT GUI version.

This manual is not intended as a guide to general application programming for the supported operating systems.

For a list of supported development tools, see Platforms . For more information on these topics, see your application development tool documentation.

Building the CustDB application

The sample application is installed, ready to run, in your UltraLite directory.

You can run the sample application as installed, but all source files are provided for you to build the application. You can experiment by modifying the application as you wish.

For information on building the sample application for the Palm Computing Platform, see Building the CustDB sample application .

For information on building the sample application for Windows CE, see Building the CustDB sample application .

Synchronization techniques in the CustDB application

The sample application includes examples of several useful synchronization techniques. This chapter provides a glimpse at synchronization, but in order to understand how to use these techniques in applications, you need to understand in more detail how the synchronization process works.

For more information

For an overview of the synchronization process, see MobiLink Synchronization .

For a description of how to write the synchronization scripts that control synchronization, see Writing Synchronization Scripts .

For information on the techniques used in the CustDB sample application, see The CustDB sample code .

Running the CustDB application

By running the CustDB sample application, you will become familiar with the architecture of an UltraLite deployment and the features of UltraLite applications.

Copy the application to your target platform

If you want to run the Windows NT or 95/98 version of the CustDB application, you do not need to read this section.

If you want to run the CustDB application on a Windows CE or Palm Computing Platform machine, you need to copy the executable files and some other information to your target machine.

Before you begin

This section is not intended as a guide to application deployment on Palm Computing or Windows CE machines. This manual assumes familiarity with the platform for which you are developing.

Copying the application to a Palm Computing device or emulator

Use the Palm Install Tool to deploy applications to a Palm Computing device. You must tell Install Tool the location of the application, and then use HotSync to transfer the executable file to the device.

The Adaptive Server Anywhere installation automatically sets registry entries to enable CustDB synchronization via HotSync. These entries associate the UltraLite conduit on Windows NT with the CustDB application on the Palm Computing device. You must have HotSync Manager 3 installed for HotSync synchronization to work properly.

To copy the sample application to a Palm Computing device:

Install Adaptive Server Anywhere onto a machine that has the Palm Computing Install Tool already installed. The Adaptive Server Anywhere installation then adds the registry entries required for HotSync.

Start the Palm InstallTool on your PC. Locate the Palm Computing executable file for the sample application. This executable is the file custdb.prc, and it is in the palm\68k subdirectory of your UltraLite directory.

HotSync your Palm Computing device. The CustDB application is copied into the Applications view on your Palm device.

To copy the sample application to a Palm Computing Platform emulator:

From the emulator, right click and select Install Application Database from the popup menu.

Locate the application and click OK to install. You may have to refresh the Applications view to see the installed application on the emulator interface.

Copying the application to a Windows CE deviceTo copy the sample application to a CE device

Ensure that Windows CE Services is properly installed. You can do this by checking whether Windows Explorer can view and modify the file system on the CE device.

Open the Windows Explorer. Right click on My Computer and select Explore.

Find the Adaptive Server Anywhere 6.0 install folder. The default location is as follows:

c:\Program Files\Sybase\Adaptive Server Anywhere 6.0

Open the ultralite\vc6\CECustDB folder so that the contents of the folder are visible. Open mips or sh3 depending on the CPU used in the CE device. The executable file CustDB.exe should be visible at this point.

Right click on CustDB.exe and select Copy.

Find the CE device in the Explorer hierarchy. It should be under the Mobile Devices folder. Create a folder on the CE device called Sybase. Open the Sybase folder on the device and paste the executable to copy the CustDB.exe file to the CE device. You may be asked whether you want the file to always be synchronized or converted before copying, both of these actions can be refused.

In the Sybase folder on the device, there should be one executables file, namely, CustDB.exe. Right click on CustDB.exe to create a shortcut and rename the shortcut to CustDB.

On the CE Device, open the root folder Windows followed by Start Menu and Programs. In the Programs folder, add a Sybase folder.

Move the shortcut for CustDB from \Sybase to the \Windows\Start Menu\Programs\Sybase folder by cutting and pasting the shortcut.

The samples are now accessible from the CE device Start button. Select Start-->Programs-->Sybase-->CustDB.

Start the CustDB application

In this step, you start the sample application.

The application carries out an initial synchronization to download an initial copy of the data from the consolidated database. You must have the database server running in order to carry out this initial download. For the Windows and Windows CE versions, you must also have the MobiLink server running.

To start the MobiLink server against the consolidated database:

Start the consolidated database server

The Adaptive Server Anywhere consolidated database server runs on your Windows NT machine. From the Start menu, select Programs-->Sybase-->Adaptive Server Anywhere--> UltraLite--> UltraLite Sample Database.

Starting the consolidated database server also starts the CustDB sample database.

Start the MobiLink server

This step is not required for the Palm application, as the HotSync manager starts the MobiLink server. The MobiLink server connects to the consolidated database server through ODBC. It could run from a separate machine from the database server, but in this example we will run it on the same machine.

From the Start menu, select Programs-->Sybase-->Adaptive Server Anywhere--> MobiLink Synchronization Server Sample.

The command executed by this icon connects the MobiLink server to the consolidated database server.

Starting the application on a Palm Computing device

The sample application for the Palm Computing Platform uses HotSync as the synchronization mechanism.

To start the sample application on the Palm Computing platform:

Ensure the application is copied onto your Palm Computing device

The procedure is described in Copying the application to a Palm Computing device or emulator .

Place your Palm device into its cradle

When you start the sample application for the first time it must be able to synchronize, to download an initial copy of the data. This step is required only the first time you start the application. After that, the downloaded data is stored in the UltraLite database.

Start the sample application

From the Applications view, select the CustDB sample UltraLite application.

Enter an employee ID

When running through this section as a tutorial, enter a value of 50. The application also allows values of 51, 52, or 53.

Synchronize

Use HotSync to obtain an initial copy of the data.

Starting the application on Windows CE

For synchronization to succeed, you must have the consolidated database server and a MobiLink server running when you start the sample application.

To start the sample application on Windows CE:

Ensure the application is copied onto your Windows CE device

The procedure is described in Copying the application to a Windows CE device .

Connect your Windows CE device to your PC

When you start the sample application for the first time, it must be able to connect to the MobiLink server and download an initial copy of the data. This step is required only the first time you start the application. Once you have downloaded the data, it is stored in the UltraLite database.

Start the sample application

On the CE device, select Start--> Programs--> Sybase-->CustDB.

Enter an employee ID

When running through this section as a tutorial, enter a value of 50. The application does also allow values of 51, 52, or 53.

The application synchronizes after you enter the employee ID, and a set of customers, products, and orders are downloaded to your machine.

Starting the application on Windows 95, Windows 98, or Windows NTTo start the sample application on Windows 95/98/NT:

Start the CustDB Database

From the Start menu, select Programs--> Sybase-->Adaptive Server Anywhere-->UltraLite-->UltraLite Sample Database.

Start the MobiLink Server

From the Start menu, select Programs--> Sybase-->Adaptive Server Anywhere-->UltraLite-->MobiLink Synchronization Server Sample.

Start the sample application

From the Start menu, select Programs--> Sybase--> Adaptive Server Anywhere-->UltraLite-->Sample Application.

Enter an employee ID

When running through this section as a tutorial, enter a value of 50 and press ENTER. The application also allows values of 51, 52, or 53.

View the data in the sample application

In this step, you will display the initial data in the sample application.

Here are some actions you can perform with the sample application.

Scroll through the outstanding orders
The application holds information about a set of orders. For each order, this includes the ID number, the customer, the product, the quantity, and other information. Also included is a status column and a notes column, which you can modify from the application.
Only unapproved orders for the customers that you list in the ulEmpCust table are downloaded to the application. The sample application does not receive all the orders listed in the ULOrder table in the consolidated database. You control which information is sent to your application using synchronization scripts.
Display a list of customers
The UltraLite application holds the complete list of customers from the consolidated database. To see this list, choose Order-->New, and open the Customer drop-down list box. In the console application, press C to display a list of customers.
Display a list of products
The UltraLite application holds the complete list of products from the consolidated database. To see this list, choose Order-->New, and open the Product drop-down list box. In the console application, press P to display a list of products.
The consolidated database holds a last_modified column, which is used to guide the synchronization process, but which the application does not reference or require. This column is not present in the UltraLite database.

Now is a good time to experiment with these features until you feel comfortable with the user interface. The following sections guide you through some changes.

Make changes to the UltraLite database

In this step, you make changes to the UltraLite database. The first change is to approve one order and deny another.

Approving or denying orders updates two columns in the local copy of the table, but no data in the consolidated database is changed until you synchronize.

You can run the sample application disconnected from the MobiLink server. Any changes you make are made to the local copy of the data only. This copy is called a remote database, as it is remote from the consolidated database.

Carry out the following steps as user number 50.

Go to the first order in the list. This is an order from Apple Street Builders. Approve the order. You can add a note to your approval if you wish. A mark to show that the order is approved is added to the local copy of the data.
Go to the next order in the list. This is an order from Art's Renovations. Deny this order. Select a note from the drop-down list. A mark to show that the order is denied is added to the local copy of the data.
Go to the next order in the list. This is an order from Awnings R Us. Delete this order by choosing Order-->Delete. It disappears from your local copy of the data.

Source code

The Embedded SQL source code that carries out these operations is contained in custdb.sqc, which is located in the UltraLite\samples\CustDB subdirectory of your Adaptive Server Anywhere installation directory. The function that carries out the approval or denial is the

CDemoDB::ProcessOrder

method.

For more information on Embedded SQL, see The UltraLite Embedded SQL Interface .

Synchronize your changes

In this step, you will synchronize the approval and denial made at the remote database to the consolidated database.

To synchronize your changes on Windows CE or Windows:

Ensure that the MobiLink server is still running, and that your application can connect to the MobiLink server.

If you are running on a Windows CE handheld device, place the device in its cradle, so that it can connect to the machine running the MobiLink server.

Choose File-->Synchronize to synchronize your data.

To synchronize your changes via HotSync:

If you are running on a Palm device, place the device in its cradle.

Press the HotSync button.

For more information, see Adding HotSync synchronization to your Palm application .

Notes

The UltraLite runtime library automatically keeps track of the changes to be uploaded, and sends them to the MobiLink server.
The only feature in the Windows CE and Windows 95/98/NT UltraLite application source code that deals directly with synchronization is the call to the
```
ULSynchronize
```
function. The
```
ULSynchronize
```
function initiates a connection to the MobiLink server.
The Palm Computing Platform application uses the HotSync mechanism. The
```
ULPalmLaunch
```
and
```
ULPalmExit
```
functions prepare data for, and retrieve data from, the HotSync process.
The MobiLink server runs a set of synchronization scripts located in the consolidated database. These scripts apply the uploaded changes to the consolidated database, and download any changes from the consolidated database to the UltraLite database.
The synchronization scripts are held in the consolidated database. You can view them with the Java edition of Sybase Central. The file used to create the consolidated database is custdb.sql, in the UltraLite\samples\CustDB subdirectory of your Adaptive Server Anywhere installation directory. Files for other DBMSs are included in the same directory.

For more information

For an architectural overview of synchronization, see MobiLink Synchronization .

For information on synchronization scripts, see Writing Synchronization Scripts .

For information on synchronization techniques you can use with UltraLite, see Synchronization Techniques .

Make changes to the consolidated database

In this step, you use Interactive SQL to connect to the consolidated database and confirm that the changes made have been synchronized. You will also add a customer to the consolidated database and synchronize the new customer back to the sample application.

To confirm that the changes are synchronized to the consolidated database:

Connect to the consolidated database (use the

UltraLite Sample

ODBC data source ) from Interactive SQL. To confirm that the approval and denial have been synchronized, issue the following statement.

SELECT order_id, status 
FROM ULOrder
WHERE status IS NOT NULL

The results show that order 5100 is approved, and 5101 is denied.

To add a customer to the list and synchronize it to the UltraLite application:

To add a customer to the list, execute the following statement.

INSERT INTO ULCustomer (cust_id, cust_name)
VALUES ( 3000, 'Demolition Depot' );
COMMIT

The INSERT statement adds a customer to the database. The COMMIT statement ensures the data is stored in the database. All changes must be committed in order to be included in the next synchronization.

From your UltraLite application, synchronize again, and list the customers. You will see that Demolition Depot is added to the end of the list. You may need to update the list using the New Order command.

You may notice that the approved order for Apple Street Builders is no longer in the UltraLite remote database. The synchronization scripts are defined to remove approved orders.

Browsing the CustDB database with the Java edition of Sybase Central

Use the Java edition of Sybase Central to manage MobiLink synchronization. The synchronization logic is held in the consolidated database. Run the Java edition of Sybase Central against your consolidated database.

This section describes how to use the Java edition of Sybase Central to browse the scripts in the CustDB consolidated database.

Connect to the CustDB database from Sybase Central

Start the CustDB database
If it is not already running, start the database server from the Start menu.
- Select Programs--> Sybase--> Adaptive Server Anywhere 6.0--> UltraLite--> UltraLite Sample Database.
- An Adaptive Server Anywhere database server starts, running the CustDB UltraLite Sample Database.
Start Sybase Central
From the Start menu, select Programs--> Sybase--> Adaptive Server Anywhere 6.0--> Manage MobiLink Synchronization.
Connect Sybase Central to the sample database
In Sybase Central, select Tools-->Connect. If there is a choice of connection types, select UltraLite. The UltraLite connection dialog is displayed.
Select ODBC, enter
```
UltraLite Sample 
```
in the Data Source box, and click OK to connect.
You are now connected to the CustDB UltraLite Sample database.

Browse the synchronization scripts

From the Java edition of Sybase Central, you can browse through the tables, users, synchronized tables, and synchronization scripts that are stored in the database. The Java edition of Sybase Central is the primary tool for adding these scripts to the database.

Open the Connection Scripts folder. The right hand pane lists a set of synchronization scripts and a set of events that these scripts are associated with. As the MobiLink server carries out the synchronization process, it triggers a sequence of events, and any synchronization script associated with that event is run at that time. By writing synchronization scripts and assigning them to the synchronization events, you can control the actions that are carried out during synchronization.

Open the Synchronized Tables folder, and open the ULCustomer table folder. The right hand pane lists a pair of scripts that are specific to this table, and their corresponding events. These scripts control the way that data in the ULCustomer table is synchronized with the remote databases.

This section does not discuss the content of the synchronization scripts. These are discussed in detail in the chapter Writing Synchronization Scripts .


	Expand Search