iAnywhere.com - Lesson 1: Create an UltraLite database schema

Tutorial: Working with UltraLite Databases

Lesson 1: Create an UltraLite database schema

In this lesson, you build a single-table UltraLite database schema for Windows CE, Windows XP, and Palm OS devices.

This lesson is the first in a complete tutorial on working with UltraLite databases. You may also have reached this lesson from one of several UltraLite component tutorials, in which case you should return to your main tutorial after creating a schema file.

For more information on UltraLite schemas, see Creating UltraLite databases and schemas.

To start this tutorial, create a directory to hold the schema and other files. This directory is assumed to be C:\tutorial\. If you create your tutorial directory elsewhere, supply the path to your location instead of c:\tutorial\ throughout.

To create a schema file

Start the UltraLite Schema Painter:

Choose Start > Programs > SQL Anywhere 9 > UltraLite > UltraLite Schema Painter.
Create a new schema file called tutCustomer.
- From the File menus, select New > UltraLite Schema.
- For a filename, enter c:\tutorial\tutCustomer.usm or Browse to the folder and enter tutCustomer.usm.
- Leave the other settings at their default values and click OK to create the schema.

Create a table called customer.

In the left pane of the UltraLite Schema Painter, expand the tutCustomer item and select the Tables folder.
In the right pane, double-click Add Table.

The New Table dialog appears.
Enter the name Customer.

Click Add to add the following columns:

Column name	Data type (Size)	Column Allows NULL values?	Default value
ID	integer	No	autoincrement
FName	char (15)	No	None
LName	char (20)	No	None
City	char (20)	Yes	None
Phone	char (12)	Yes	555-1234

Tutorial use only

The use of autoincrement as a primary key is not recommended in a database that is going to be synchronized. Instead, use a global autoincrement or UUID value. For the purposes of this tutorial, an autoincrement is sufficient.

For more information on maintaining unique primary keys in a synchronization setup, see Maintaining unique primary keys.

Set ID as the primary key: click Primary Key and add ID to the index, marking it as ascending.
Check your work and click OK to complete the table definition and dismiss the New Table dialog.

Click File > Save to save the tutcustomer.usm file.

Optionally, export a Palm schema file.

If you intend to work with the Palm OS as a target platform, you may want to export a schema definition for the Palm OS.

From the File menu, choose Export Schema for Palm.

Enter a Palm Creator ID. For tutorial purposes you could use Syb3, but do not use this for deployed applications.

A note on Palm Creator IDs

A Palm creator ID is assigned to you by Palm. You can use Syb3 as your creator ID when you make sample applications. However, when you create production applications, you should obtain and use your own creator ID.

Leave the filename at its default setting to save the PDB file in your tutorial directory. Click OK.

You have now defined the schema of an UltraLite database. Although this database contains only a single table, you can use many tables in UltraLite databases.

UltraLite component tutorial readers

You may be carrying out this lesson as part of an UltraLite component tutorial or you may be working through the complete tutorial in this chapter. If you reached this lesson from an UltraLite component tutorial, you only need the schema file and so you can return to your main tutorial now. Otherwise, continue.

To create an UltraLite database file

From the Tools menu of the UltraLite Schema Painter, choose Create UltraLite Database.

The Create UltraLite Database dialog appears.
Choose Multibyte Character Database (Desktop) and leave the other settings at their default values.
Click OK to create the database.