(Post 25/11/2005) Sybase SQL Anywhere Studio product was chosen as the basis for this tutorial because it is the market-leading mobile database product. Support for multiple platforms, a powerful multiple-RDBMS synchronization solution, JDBC and ODBC connectivity, and flexible deployment options — combined with a 68% share of the mobile database market — make this an excellent candidate for your next mobile development project…

Getting Started

A quick aside before we get down to business. Sybase SQL Anywhere Studio product was chosen as the basis for this tutorial because it is the market-leading mobile database product. Support for multiple platforms (including Linux, Win32, WinCE, Palm OS, and Symbian), a powerful multiple-RDBMS synchronization solution, JDBC and ODBC connectivity, and flexible deployment options — combined with a 68% share of the mobile database market — make this an excellent candidate for your next mobile development project. You first need to install SQL Anywhere Studio 8.0, available via a free 60-day trial download from Sybase at http://www.sybase.com. When installing, be sure to "max out" the options until you're sure about what you will and won't need. At a minimum, this includes installing the ASA and Ultralite database options, all MobiLink synchronization options, Sybase Central, and jConnect.

Creating the Database

Once installed, the Sybase Central tool will act as the administration console for nearly all tasks that you want to perform. Once nice thing about the SQL Anywhere Studio toolset is that most functionality is also accessible via the command-line as well, an important requirement for anyone running automated build processes, using a tool such as Ant . Figure 1 shows the variety of utilities available from within Sybase Central, including the tools needed to create and maintain Adaptive Server Anywhere (ASA) databases.

Figure 1 Sybase Central utilities

Figures 2 through 7 illustrate the process of creating a new ASA database using Sybase Central. I stepped through this process to create a new mobile_sales.db database file (and its corresponding log file: mobile_sales.log).

Figure 2 The Create Database Wizard

Figure 3 Specifying the log file in the Create Database Wizard

Figure 4 Specifying Java support in the Create Database Wizard

Figure 5 Configuring the database options in the Create Database Wizard

Figure 6 Specifying the page size in the Create Database Wizard

Figure 7 Specifying the collation sequence in the Create Database Wizard

The wizard will create and run the database for you -- note the little SQL icon running in your Windows toolbar when you're done. Note that an ASA database is a "mini-server" database, running in its own process and monitoring a port for incoming requests. This is in contrast to the file-based "Ultralite" deployment option that we'll examine later in this article. Developers who build production-quality applications need to handle the deployment of all ASA runtime files (described in detail in the product documentation…InstallAnywhere templates and scripts are even included!) and the ASA database process need to be started each time the system or application starts up. The easiest way to do this (for Windows 2000/XP users) is to create a Windows service that will auto-start the database when the OS starts up. Sybase includes a command-line tool (dbsvc) that will handle this for you. (More on creating Windows services can be found at http://www.sybase.com/detail?id=1009886). If you're not interested in running the database as a service, you can start any database manually by using the dbeng8 command.

Building the Tables

The simple sales force application we build will enable a user to access information on all products sold by our company as well as all customers of our company. The user can create/modify new sales orders and when all is complete, can sync modified data back to our organization's central server RDBMS. Our "starter" database schema is very simple (four tables), with the goal being to highlight SQL Anywhere Studio capabilities and mobile development techniques.

To complete the goals of the application, we need four tables in order to store sales and product information: CUSTOMER, PRODUCTS, OPPORTUNITIES, and SALES. Figures 8 through 11 show the formats used in these tables.

Figure 8 Creating the CUSTOMER table

Figure 9 Creating the PRODUCTS table

Figure 10 Creating the OPPORTUNITIES table

Figure 11 Creating the SALES table

The ASA SQL statements used to create these tables is as follows:

As a final step, we need to prepare our database for "real-world" (or something approximating the real world) usage by creating a user account in the database. This, too, can be accomplished via the Sybase Central interface. For starters, I created a user named "salesperson" with a password of "sales" in our starter database. This user can log into the database (via Sybase's included dbisql query tool or via our own app) using this username/password combination.

Deploying Via Ultralite

Earlier in the article, I briefly mentioned SQL Anywhere's Ultralite deployment option. For those not requiring the services of a full-fledged SQL database server (that is, integrated security, stored procedures, triggers, views, and so on), the Ultralite option offers a file-based deployment method that can be accessed by using either C or Java APIs. This approach offers much greater simplicity, including no installation requirements at the expense of advanced capabilities and application flexibility. Ultralite applications require the definition of an Ultralite project at design time and the predefinition of all SQL statements to be used. These SQL statements are then gathered during a "preprocessing" procedure (ulgen) to generate required Java, C, or C++ source code. This code can then be compiled into the parent application; when the resultant app is run for the first time, a new Ultralite database file will be created on-the-fly by the embedded runtime. Ultralite applications can be synchronized via MobiLink synchronization just like ASA databases. As mentioned earlier, they don't offer any data processing logic or security in the database, which places the burden on the application developer.

Conclusion

In Part 2 of this tutorial, we'll take a look at developing a Java mobile application Adaptive Server Anywhere and Ultralite. For further information on SQL Anywhere, the following newsgroups are maintained by Sybase for discussion of the SQL Anywhere Studio product. These newsgroups are valuable first stopping points for developers just getting started with SQL Anywhere and for those in the throes of a difficult development problem.

sybase.public.sqlanywhere.general
sybase.public.sqlanywhere.replication
sybase.public.sqlanywhere.ultralite
sybase.public.sqlanywhere.mobilink
sybase.public.sqlanywhere.product_futures_discussion
sybase.public.sqlanywhere.linux
sybase.public.ianywhere.wireless

Bryan Morgan
(theo www.informit.com/articles)