Table events

Many of the connection events that occur between the begin_synchronization and end_synchronization events, such as begin_download and end_upload, also have table equivalents. These and other overall table events might be used for tasks such as creating an intermediate table to hold changes or printing information to a log file.

You can also script table events that apply to each row in the table. For row-level events, the order of the columns in your scripts must match the order in which they appear in the CREATE TABLE statement in the remote database, and the column names in the scripts must refer to the column names in the consolidated database.

Generating default scripts

Although there are several row-level events, most tables need scripts for three upload events (for INSERT, UPDATE, and DELETE) and one download event. To speed up the task of creating these four scripts for every table, you can generate scripts for them automatically by running the “create a synchronization model” task from the MobiLink plug-in in Sybase Central.

For information on the MobiLink plug-in, see the online MobiLink Getting Started book.

The MobiLink plug-in allows you to add more functionality to default scripts than default scripts generated in earlier versions of MobiLink. However, if you are using ASA 8 or ASA 9 instead of SQL Anywhere 10, 11 or 12, you can still generate default synchronization scripts by starting the MobiLink synchronization server with the -za switch and setting the SendColumnNames extended option for dbmlsync.

The following procedure describes how to generate ASA 8 or 9 synchronizations scripts from the PowerBuilder UI.

To generate ASA 8 or 9 synchronization scripts automatically from PowerBuilder:

Expand the ODBC Utilities folder in the Database painter and double-click the MobiLink Synchronization Server item.

The MobiLink Synchronize Server Options dialog box displays.
Select Adaptive Server Anywhere 8 or 9 from the MobiLink Version drop-down list.

You enable the Automatic Script Generation check box.
Select the Automatic Script Generation check box in the MobiLink Synchronize Server Options dialog box and click OK to start the server.

You can open this dialog box from the Utilities folder in the Database painter or the Database Profiles dialog box.
In your application, enter SendColumnNames=ON in the Extended text box on the Settings page of the w_appname_sync_options window.

You must have at least one publication, user, and subscription defined in the remote database. If you have more than one publication or user, you must use the -n and/or -u switches to specify which subscription you want to work with.

If there are existing scripts in the consolidated database, MobiLink does nothing. If there are no existing scripts, MobiLink generates them for all tables specified in the publication. The scripts control the upload and download of data to and from your client and consolidated databases.

If the column names on the remote and consolidated database differ, the generated scripts must be modified to match the names on the consolidated database.

You can also generate ASA 8 or 9 synchronization scripts from a command prompt. Start the server using the -za switch, then run dbmlsync and set the SendColumnNames extended option to on. For example:

dbmlsrv9 -c "dsn=masterdb" -za

dbmlsync -c "dsn=remotedb" -e SendColumnNames=ON

Generated scripts

Table 13-3 shows sample default scripts generated by the MobiLink plug-in in Sybase Central. The scripts are generated for a table named emp with the columns emp_id, emp_name, and dept_id. The primary key is emp_id. The generated download scripts use a timestamp based download.

**Table 13-3: Sample default synchronization scripts from MobiLink plug-in**
Script name	Script
upload_insert	`INSERT INTO "GROUP1"."emp" ( "emp_id", "emp_name", "dept_id" )VALUES ( {ml r."emp_id"}, {ml r."emp_name"}, {ml r."dept_id"} )`
upload_update	`UPDATE "GROUP1"."emp" SET "emp_name" = {ml r."emp_name"}, "dept_id" = {ml r."dept_id"} WHERE "emp_id" = {ml r."emp_id"}`
upload_delete	`DELETE FROM "GROUP1"."emp" WHERE "emp_id" = {ml r."emp_id"}`
download_cursor	`SELECT "GROUP1"."emp"."emp_id", "GROUP1"."emp"."emp_name", "GROUP1"."emp"."dept_id" FROM "GROUP1"."emp" WHERE "GROUP1"."emp"."last_modified" >= {ml s.last_table_download}`
download_delete_cursor	`SELECT "emp_del"."emp_id FROM "emp_del" WHERE "emp_del"."last_modified" >= {ml s.last_table_download}`

The scripts that you generate with the MobiLink plug-in constitute a synchronization model. After you create a synchronization model, you must use the “Deploy the synchronization model” task of the plug-in to deploy the scripts to consolidated and remote databases or to SQL files.

Table 13-4 shows the scripts that are generated for the same table using the -za command switch for the ASA 9 MobiLink synchronization server. The scripts generated for downloading data perform “snapshot” synchronization. A complete image of the table is downloaded to the remote database. Typically you need to edit these scripts to limit the data transferred.

For more information, see “Limiting data downloads”.

**Table 13-4: Sample default scripts generated by dbmlsrv9 -za**
Script name	Script
upload_insert	`INSERT INTO emp (emp_id, emp_name, dept_id) VALUES (?,?,?)`
upload_update	`UPDATE emp SET emp_name = ?, dept_id = ? WHERE emp_id=?`
upload_delete	`DELETE FROM emp WHERE emp_id=?`
download_cursor	`SELECT emp_id, emp_name, dept_id FROM emp`

Before modifying any scripts, you should test the synchronization process to make sure that the generated scripts behave as expected. Performing a test after each modification will help you narrow down errors.