UltraLite Database User's Guide
Tutorial: Working with UltraLite Databases
You can use the UltraLite Schema Painter to generate a SQL command file that defines tables and synchronization scripts for an Adaptive Server Anywhere consolidated database. This feature is useful if you are extending an UltraLite application to include synchronization.
In this lesson, you create a consolidated database that manages timestamp-based synchronization with your UltraLite database. The task involves the following procedures:
Create a publication that holds the Customer table.
Define synchronization settings for the table.
Generate the table definitions and synchronization scripts.
Create the consolidated Adaptive Server Anywhere database.
This lesson assumes that you have the UltraLite Schema Painter open with the tutCustomer schema opened, as at the end of Lesson 1: Create an UltraLite database schema.
To create a publication holding the Customer table
Create the publication:
In the left pane, select the Publications folder. In the right pane, double click Add Publication. The Publication dialog appears.
In the Publication Name field, enter CustomerPublication.
Select the Customer table and click >> to add it to the list of tables in the publication. Click OK.
The next step defines synchronization settings.
To define synchronization settings
Add the table to the list of tables with custom settings:
In the left pane, open the MobiLink Synchronization folder. In the right pane, double click Add Table-specific Settings.
The MobiLink Table Settings dialog appears.
Select the Customer table and click >> to add it to the list of tables with MobiLink settings. Click OK.
Define the settings.
In the left pane, select the MobiLink Synchronization folder.
Right click the Customer table and choose Properties. The MobiLink Synchronization Property sheet appears. The settings here are chosen for simplicity in this demonstration: in a production environment the settings depend on your business rules.
On the Direction tab, leave the setting at Full Synchronization.
On the Row Increment tab, choose Timestamp. Leave the Use Shadow Table option.
An additional column will be created in the consolidated database that holds the timestamp values for this table.
On the Row Partition tab, leave the setting at Same Data on All Remote Databases.
On the Deletions tab, choose Download Deletions and select the first of the two options. A table named Customer_deletes will be created in the consolidated database that holds identifying values for the deleted rows.
On the Conflict tab and the Resolution tab, leave the values at their default settings.
Click OK to save the settings.
Optionally, preview the table definitions and synchronization scripts.
You can preview the table definitions and synchronization scripts by right-clicking the table and choosing Preview Consolidated Tables and Scripts from the popup menu. The previewed definitions and scripts define the tables and synchronization scripts needed for an Adaptive Server Anywhere consolidated database that can synchronize with this UltraLite database. Comment lines are prefixed by --.
The next step is to generate the SQL command file that holds the table definitions and synchronization scripts.
To generate the consolidated database table and script definitions
From the Tools menu, choose Generate Consolidated Tables and Scripts.
The Generate Consolidated Tables and MobiLink Scripts dialog appears.
In the Settings group, set the MobiLink script version is to Tutorial. Leave the other two checkboxes at their default values.
In the Generated SQL group, ensure the Consolidated Tables, Triggers, MobiLink Scripts and Procedures checkbox is selected.
During the development of real applications, you may regenerate synchronization scripts and table definitions several times as you modify your application. For this reason, the dialog provides you with the option to generate only some of the database objects.
Leave the Generated SQL file as the default setting (tutCustomer.sql) and click OK to generate the scripts.
The final step is to use the generated SQL command file to create an Adaptive Server Anywhere consolidated database.
To create a consolidated database
Create the consolidated database file.
Open a command prompt, and change to the tutorial directory. Enter the following command to create a database file named consol.db:
dbinit consol.db
Define an ODBC data source for the database.
Open the ODBC Administrator.
From the Start menu, choose Programs > SQL Anywhere 9 > Adaptive Server Anywhere > ODBC Administrator.
On the User DSN table, click Add. The Create New Data Source dialog appears.
From the list, choose Adaptive Server Anywhere 9.0 and click Finish. The Adaptive Server Anywhere ODBC Configuration dialog appears.
Enter the following settings in the dialog:
| Field | Value |
|---|---|
| Data Source Name (ODBC tab) | Consolidated |
| User ID (Login tab) | DBA |
| Password (Login tab) | SQL |
| Server name (Database tab) | consol |
| Database file (Database tab) | c:\tutorial\consol.db |
On the ODBC tab, click Test Connection to test the settings.
Once the Test Connection succeeds, click OK to save your definition and close the ODBC Administrator. If it fails, review the settings.
Connect to the consolidated database using Interactive SQL.
From the Start menu, choose Programs > SQL Anywhere 9 > Adaptive Server Anywhere > Interactive SQL.
In the Connection dialog, specify an ODBC data source of Consolidated, and click OK to connect.
Open the generated SQL command file.
From the File Menu, choose Open. Open the file c:\tutorial\tutCustomer.sql.
Run the SQL command file.
Choose SQL > Execute to execute the SQL statements and create the tables and synchronization scripts in the consolidated database.
Your consolidated database is now created. It does not, of course, contain any data.
SQL Anywhere Studio 9.0.2
Copyright © 1989–2004 Sybase, Inc. Portions copyright © 2001–2004 iAnywhere Solutions, Inc. All rights reserved.