Application Techniques
Data Access Techniques

Before you use the wizard in a production application, you need to complete the following tasks:

The wizard generates two sets of objects.

Objects that initiate and monitor synchronization

The first set of objects lets the end user initiate and monitor synchronization:

In the wizard, you can choose whether the application uses the status window. The generated status window includes an OK button that lets the user view the status before dismissing the window, and a Cancel button that lets the user cancel synchronization before it completes. You can also customize the window to fit your application's needs.

Objects that modify synchronization options

The second set of objects is generated only if you select Prompt User for Password and Runtime Changes in the wizard. It lets the end user change synchronization options before initiating synchronization:

Most applications that use the options window provide two menu items or command buttons to launch synchronization: one to open the options window so that users can set up or modify dbmlsync options before requesting a synchronization, and one to request a synchronization with the preset options.

You do not have to use the MobiLink Synchronization Wizard to create a nonvisual object that launches Dbmlsync.exe. You can include an MLSync system object in your applications:

Adding an MLSync object programatically

The code fragment below creates an instance of an MLSync object and programmatically populates all of the necessary properties as well as some optional properties using an instance of the system SyncParm structure. Then it calls the Synchronize function to start the database synchronization.

SyncParm         Parms
MLSync         mySync
Long         rc
 
mySync = CREATE MLSync
mySync.MLServerVersion = 11      // required property
mySync.Publication = 'salesapi   '// required property
mySync.UseLogFile = TRUE      // optional
mySync.LogFileName = "C:\temp\sync.log   "// optional
mySync.Datasource = 'salesdb_remote   '// required
Parms.MLUser = '50         '// required
Parms.MLPass = 'xyz123         '// required
//The following values are required if they are not 
//set by the DSN
Parms.DBUser = 'dba      '
Parms.DBPass = 'sql      '
 
//  Apply the property values to the sync object
mySync.SetParm(Parms)   
//  Launch the synchronization process
rc = mySync.Synchronize()   
destroy mySync

Adding an MLSync object from the New dialog box

You can add an MLSync object to a target PBL using the New dialog box: from the PowerBuilder menu, choose File>New, go to the PB Object tab, select Standard Class, then MLSync. This opens a new MLSync object in the User Object painter, where you can initialize all or some of the properties. When you are finished, you can save it as a new object in your target PBL.

Since all of the properties are already initialized, including userids and passwords, it is ready for immediate use. To launch a synchronization requires very little coding, as this example for an MLsync object that you save as "nvo_my_mlsync" illustrates:

nvo_my_mlsync       mySync
Long      rc
mySync = CREATE nvo_my_mlsync
mySync.Synchronize()
destroy mySync

You would typically add the above code to the Clicked event for a menu item or a command button on one of the application windows.

For more information

For more information on system objects related to synchronization, and their functions, events, and properties, see MLSynchronization, MLSync, and SyncParm in the section called “MLSynchronization object” in Objects and Controls, the section called “MLSync object” in Objects and Controls, and the section called “SyncParm object” in Objects and Controls.

If you create an instance of MLSync by PowerScript code or from the New dialog box, you should also consider using auxiliary objects that are generated automatically by the wizard that you can customize in the PowerBuilder Window painter.

Using an existing synchronization progress window

After you instantiate an MLSync object and call SetParm to enable an end user to set authentication properties at runtime, you can call a Response! type window to document the progress of a database synchronization. You open the progress window with an OpenWithParm call, using the window name and the MLSync object name as arguments. By default, the wizard generates a progress window named w_appname_syncprogress and adds the OpenWithParm call for you.

In the Properties view for an MLSync object, you can select a customized progress window to document the progress of a synchronization call. If you customize a wizard-generated progress window -- typically to hide some of the fields on its tab pages, or even to hide one or two of the tab pages -- you can select the customized progress window for all of your MobiLink applications.

Changing the connection arguments at runtime

To allow a user to override authentication parameters at runtime, you can call a customized options window or the synchronization options window generated by the wizard. The options window can, in turn, call an instance of the SyncParm object that can be initialized with authentication values from a highly secure persistent store, such as a remote database table. You can choose to make some or all of the authentication values writable, allowing the end user to override them at runtime.

The default synchronization options window, w_appname_sync_options, returns a SyncParm structure to its caller through the PowerObjectParm property of the Message object. This allows the caller to save the highly sensitive authentication property values in a secure location. It also sets the SyncParm ReturnCode property with an integer value that indicates whether to proceed with the actual synchronization.

Default tab pages of the options window

The default synchronization options window has four tab pages: Subscriptions, SQL Anywhere, MobiLink Server, and Settings.

Subscriptions page

When you used the MobiLink wizard, you selected one or more publications from the list of available publications. The selected publications display on the Subscriptions page, but cannot be edited at runtime.

Each remote user can supply a MobiLink synchronization user name on this page. The name must be associated in a subscription with the publications displayed on the page. If the application is always used by the same MobiLink user, this information never needs to be supplied again. The name is saved in the registry and used by default every time synchronization is launched from the application on this device.

The MobiLink password and authentication parameters are never saved to the user's registry. They can either be entered each time by the user or provided from a secure database.

SQL Anywhere page

Remote users can supply a DSN file name on this page to pass all the arguments needed to connect to a remote database.

If a DSN file is not used, or if the DSN file does not include a user name and password, each remote user can supply a remote database user name. The name is saved in the registry and used by default every time synchronization is launched from the application on this device.

The following figure displays the options window SQL Anywhere tab page with DSN, DBUser, DBPass, and Encryption Key fields. The database password and encryption key are never saved in the registry.

Figure: Synchronization options window

MobiLink Server page

When you create a subscription, you specify a protocol, host, port, and other connection options. For ease of testing, the default protocol is TCP/IP and the default host is localhost. The default port is 2439 for TCP/IP, 80 for HTTP, and 443 for HTTPS.

You might need to change these defaults when you are testing, and your users might need to change them when your application is in use if the server is moved to another host or the port changes. If you did not enter values for the host and port at design time, and the user does not make any changes on this page, dbmlsync uses the values in the subscription.

For more information about subscriptions, see Adding subscriptions.

Settings page

The Settings page displays logging options, and any other dbmlsync options you specified at design time and lets the user change any of these options at runtime. It also gives the user a choice of displaying or not displaying a synchronization progress window.

Before you use the generated objects, you should examine them in the PowerBuilder painters to understand how they interact. Many of the function and event scripts contain comments that describe their purpose.

All the source code is provided so that you have total control over how your application manages synchronization. You can use the objects as they are, modify them, or use them as templates for your own objects.

Properties of the user object

The nvo_appname_mlsync user object contains properties that represent specific dbmlsync arguments, including the publication name, the MobiLink server host name and port, and the user name and password for a connection to the remote database.

When you run the wizard, the values that you specify for these properties are set as default values in the script for the constructor event of the user object. They are also set in the Windows registry on the development computer in HKEY_CURRENT_USER\Software\Sybase\PowerBuilder\19.0\appname\MobiLink, where appname is the name of your application.

At runtime, the constructor event script gets the values of the properties from the registry on the remote machine. If they cannot be obtained from the registry, or if you override the registry settings, the default value supplied in the script is used instead and is written to the registry.

You can change the default values in the event script, and you can let the user change the registry values at runtime by providing a menu item that opens the w_appname_sync_options window.

Launching dbmlsync

To enable the user to launch a synchronization process, code a button or menu event script to call the gf_appname_sync global function. This function creates an instance of the nvo_appname_mlsync user object, and the user object's constructor event script sets the appname\MobiLink key in the registry of the remote machine.

If you specified in the wizard that the progress window should display, the global function opens the progress window, whose ue_postopen event calls the nvo_appname_mlsync user object's synchronize function; otherwise, the global function calls the synchronize function. The synchronize function launches dbmlsync as an external process.

Supplying a MobiLink user name and password

The global function takes a structure for its only argument. You can pass a system SyncParm structure that you instantiate. The structure includes six variables with string datatypes (one each for MobiLink and remote database user names and passwords, as well as variables for the authentication parameters and the encryption key) and another variable that takes a long datatype for a return code.

If you assign valid values to the structure that you pass as an argument, the global function passes these values to the user object to enable MobiLink server and remote database connections.

The options window (described in Default tab pages of the options window) provides a mechanism to store certain of these values in the registry the first time a user starts a synchronization. (Sensitive password and encryption information is never saved to the registry.) Subsequent synchronizations can be started without the user having to reenter the information, however, the options window can still be used to override and reset the registry values.

Retrieving data after synchronization

After synchronizing, you would typically test for synchronization errors, then retrieve data from the newly synchronized database. For example:

if gf_myapp_sync(s_opt) <> 0 then
   MessageBox("Error", "MobiLink error")
else
   dw_1.Retrieve()
end if

Capturing dbmlsync messages

The PowerBuilder VM traps messages from the dbmlsync process and triggers events in the user object as the synchronization process runs.

These events are triggered before synchronization begins as the upload stream is prepared:

ue_begin_logscan ( long rescan_log )
ue_progress_info ( long progress_index, long progress_max )
ue_end_logscan ( )

These events correspond to events on the synchronization server, as described in Connection events:

ue_begin_sync ( string user_name, string pub_names)
ue_connect_MobiLink ( )
ue_begin_upload ( )
ue_end_upload ( )
ue_begin_download ( )
ue_end_download ( long upsert_rows, long delete_rows )
ue_disconnect_MobiLink( )
ue_end_sync ( long status_code )

These events are triggered after ue_end_upload and before ue_begin_download:

ue_wait_for_upload_ack ( )
ue_upload_ack ( long upload_status )

These events are triggered when various messages are sent by the server:

ue_error_msg ( string error_msg )
ue_warning_msg ( string warning_msg )
ue_file_msg ( string file_msg )
ue_display_msg ( string display_msg )

The default event scripts created by the wizard trigger corresponding events in the optional progress window, if it exists. The window events write the progress to the multiline edit control in the progress window. Some window events also update a static text control that displays the phase of the synchronization operation that is currently running (log scan, upload, or download) and control a horizontal progress bar showing what percentage of the operation has completed.

You can also add code to the user object or window events that will execute at the point in the synchronization process when the corresponding MobiLink events are triggered. The dbmlsync process sends the event messages to the controlling PowerBuilder application and waits until PowerBuilder event processing is completed before continuing.

Cancelling synchronization

The Cancel button on the progress window calls the cancelsync user object function to cancel the synchronization process. If your application does not use the progress window, you can call this function in an event script elsewhere in your application.

Support files required on remote machine

If you do not install PowerBuilder or SQL Anywhere on remote machines, you must copy the files listed in the following table to use MobiLink synchronization with a PowerBuilder application. These files must be copied to the system path on the remote machine or the directory where you copy your PowerBuilder applications.

Registry requirements for a remote machine

If you install SQL Anywhere on all remote machines that you use with MobiLink synchronization, the required registry entries are assigned automatically. If you copy SQL Anywhere and MobiLink files to a remote machine, you must create the HKEY_CURRENT_USER\SOFTWARE\Sybase\SQL Anywhere\16.0 registry key and add a "Location" string value that points to the parent directory of the bin32 or bin64 subdirectory where you copied SQL Anywhere and MobiLink files. (The code in the uf_runsync function of the nvo_appname_sync user object appends "\bin32\dbmlsync.exe" to the path that you assign to this registry value.)

Objects generated by the MobiLink Synchronization wizard also require registry entries to define the ODBC data source for a remote SQL Anywhere connection. The following table lists the required registry entries. You can create a REG file that installs these registry entries.

Using a file DSN instead of a registry DSN

You can use a file DSN or a registry DSN for your remote database connections. To avoid having to specify a fully qualified path, you can copy file DSNs to a path specified by the ODBC registry key (typically c:\program files\common files\ODBC\data sources).

The following is an example of the contents of a valid file DSN:

[ODBC]
DRIVER=SQL Anywhere 16.0
UID=dba
Compress=NO
AutoStop=YES
Start=dbeng16 -c 8M -zl -ti 0
EngineName=SalesDB_Remote
DBN=SalesDB_Remote
DatabaseFile=C:\work\salesdb\salesdb_remote.db
DatabaseName=SalesDB_remote

The Datasource property of the MLSync object distinguishes a file DSN from a registry DSN using these rules:

At the connection level, the sequence of major events is as follows:

begin_connection
   begin_synchronization
      begin_upload
      end_upload
      prepare_for_download
      begin_download
      end_download
   end_synchronization
end_connection

When a synchronization request occurs, the begin_connection event is fired. When all synchronization requests for the current script version have been completed, the end_connection event is fired. Typically you place initialization and cleanup code in the scripts for these events, such as variable declaration and database cleanup.

Apart from begin_connection and end_connection, all of these events take the MobiLink user name stored in the ml_user table in the consolidated database as a parameter. You can use parameters in your scripts by placing question marks where the parameter value should be substituted.

To make scripts in SQL Anywhere databases easier to read, you might declare a variable in the begin_connection script, then set it to the value of ml_username in the begin_synchronization script.

For example, in begin_connection:

CREATE VARIABLE @sync_user VARCHAR(128);

In begin_synchronization:

SET @sync_user = ?

The begin_synchronization and end_synchronization events are fired before and after changes are applied to the remote and consolidated databases.

The begin_upload event marks the beginning of the upload transaction. Applicable inserts and updates to the consolidated database are performed for all remote tables, then rows are deleted as applicable for all remote tables. After end_upload, upload changes are committed.

If you do not want to delete rows from the consolidated database, do not write scripts for the upload_delete event, or use the STOP SYNCHRONIZATION DELETE statement in your PowerScript code. For more information, see Deleting rows from the remote database only.

The begin_download event marks the beginning of the download transaction. Applicable deletes are performed for all remote tables, and then rows are added as applicable for all remote tables in the download_cursor. After end_download, download changes are committed. These events have the date of the last download as a parameter.

Other connection-level events can also occur, such as handle_error, report_error, and synchronization_statistics. For a complete list of events and examples of their use, see the chapter on synchronization events in the MobiLink Administration Guide.

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 SQL 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, 12, 16, or 17, 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.

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

The following table shows sample default scripts generated by the MobiLink plug-in in SQL 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.

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.

The following table 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.

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.

You can view and modify existing scripts and write new ones in the MobiLink Synchronization plug-in in SQL Central (formerly known as Sybase Central). These procedures describe how to connect to the plug-in and write scripts, and how to add a user to the consolidated database.

When you expand the node for a consolidated database in the MobiLink Synchronization plug-in, you see folders with the following labels: Tables, Connection Scripts, Synchronized Tables, Users, Versions, and Notifications. All the procedures in this section begin by opening one of these folders.

Script versions

Scripts are organized into groups called script versions. By specifying a particular version, MobiLink clients can select which set of synchronization scripts is used to process the upload stream and prepare the download stream. If you want to define different versions for scripts, you must add a script version to the consolidated database before you add scripts for it.

If you create two different versions, make sure that you have scripts for all required events in both versions.

Adding synchronized tables and scripts

Scripts added for connection events are executed for every synchronization. Scripts added for table events are executed when a specific table has been modified. You must specify that a table is synchronized before you can add scripts for it.

Adding users

You can add users directly to the ml_user table in the consolidated database, then provide the user names and optional passwords to your users. To add a user, open the Users folder, select File>New>User, and complete the Create User wizard.

You also have to add at least one user name to each remote database, as described in Creating MobiLink users.

You create publications using SQL Central or the SQL CREATE PUBLICATION statement. In SQL Central, all publications and articles appear in the Publications folder. This section describes how to create publications in SQL Central. For information about creating and modifying publications using SQL, see the online MobiLink - Client Administration book.

Connecting to the database in SQL Central

You use the SQL Anywhere plug-in in SQL Central, not the MobiLink Synchronization plug-in, to work with MobiLink clients and remote databases. For information on starting SQL Central from the PowerBuilder design time environment, see the Users Guide.

You must have DBA authority to create or modify publications, MobiLink users, and subscriptions.

Publishing all the rows and columns in a table

The simplest publication you can create is a single article that consists of all rows and columns of one or more tables. The tables must already exist.

Publishing only some columns in a table

You can create a publication that contains all the rows but only some of the columns of a table.

Publishing only some rows in a table

You can create a publication that contains some or all of the columns in a table, but only some of the rows. You do so by writing a search condition that matches only the rows you want to publish.

In MobiLink, you can use the WHERE clause to exclude the same set of rows from all subscriptions to a publication. All subscribers to the publication upload any changes to the rows that satisfy the search condition.

Adding articles

You can add articles to existing publications.

Modifying and removing publications and articles

You can modify or drop existing publications in SQL Central by navigating to the location of the publication and selecting Properties or Delete from its pop-up menu. You can modify and remove articles in the same way.

Publications can be modified only by the DBA or the publication's owner. You must have DBA authority to drop a publication. If you drop a publication, all subscriptions to that publication are automatically deleted as well.

MobiLink users are not the same as database users. Each type of user resides in a different namespace. MobiLink user IDs can match the names of database users, but there is no requirement that they match.

Adding MobiLink users to the consolidated database

The consolidated database contains a table called ml_user that is used to authenticate the names of MobiLink users when a synchronization is requested. When you add a user to a remote database, you need to be sure that the user is also added to the ml_user table.

You can add users automatically by selecting the Automatic Addition of Users check box in the MobiLink Synchronization Server Options dialog box and then starting the server. You open this dialog box from the Utilities folder in the Database painter or Database Profiles dialog box. You can also start the server from a command prompt, passing it the -zu+ switch.

Any users defined in the remote database are added to the ml_user table in the consolidated database, as long as the script for the authenticate_user connection event is undefined. Typically the -zu+ switch is not used in a production environment. Names are usually added to the ml_user table in the consolidated database, then added to each of the remote databases. Each user is given a unique name and optional password.

A synchronization subscription links a particular MobiLink user with a publication. You must have at least one publication and one user to create a subscription.

A subscription can also carry other information needed for synchronization. For example, you can specify the address of the MobiLink server and other connection options. Values for a specific subscription override those set for individual MobiLink users.

Synchronization subscriptions are required in MobiLink SQL Anywhere remote databases. Server logic is implemented through synchronization scripts, stored in the MobiLink system tables in the consolidated database.

A single SQL Anywhere database can synchronize with more than one MobiLink synchronization server. To allow synchronization with multiple servers, create different subscriptions for each server.