PowerBuilder Tips, Tricks, and Techniques

Berndt Hamboeck

Subscribe to Berndt Hamboeck: eMailAlertsEmail Alerts
Get Berndt Hamboeck: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


PowerBuilder: Article

Keep Your Mobile Data Synchronized

Two solutions

During the past few weeks when I was doing some Pocket PowerBuilder presentations and classes here in Europe, I came across the same problem several times: How can users keep their data synchronized between their mobile devices and the rest of the enterprise? As we shall see, there are several ways to do this, depending on what we have to keep synchronized.

Database Synchronization
This is a powerful feature that comes with the Sybase SQL Anywhere database. With MobiLink you define one "consolidated database" to be the "server" within your company that holds the current valid data; the subset of this data will be copied into a (usually newly created) "remote database," which might hold only the data that needs to be used by a particular person with a mobile device. The next step will be to configure the remote database to define what data will be synchronized into the consolidated database. To do this, define a synchronization user, subscriptions, and publications.

Pocket PowerBuilder 1.5.1 offers two new wizards (see Figure 1) that create PowerBuilder source code so you'll be able to reconfigure this later during runtime (think of a new table that needs to be synchronized, so you will have to redefine your subscriptions and publications so that this new table(s) will be included in your synchronization).

Notice that you have to solve some riddles before you'll be fully happy with your mobile database synchronization.

MobiLink uses cursor scripts to upload records from the remote database to the consolidated database, and to download data that has changed since the last synchronization to the remote database (the last_download date is stored) for the used synchronization user - this clarifies why this user has to be unique for every remote database.

If you run into an issue where your remote database is lost somehow (if you ever forget to reload the batteries of your Pocket PC for about two or three weeks, you'll know what I mean), you'll have to re-create a remote database, but keep in mind that you'll either have to use a new synchronization user (because your user has already synchronized) or reset the last known progress in the consolidated database for this user. The easiest way to do this is to use:

dbmluser -d -u -c "dsn= "

Also do this when you drop and re-create the subscription in your remote database, since that will also wipe the user out. This way you can read the same user again, if necessary.

Define a way to make your MobiLink synchronization user unique for every remote database. One solution would be to use a Universally Unique Identifier (UUID) when you create the synchronization user. A UUID is guaranteed to be unique, so you can be certain that no other remote database will generate the same value (to see this in action, visit CodeXchange, at the MobiLink section, "Sharing the same MobiLink user in different databases").

If you have one remote database that has rows removed from a table and this database synchronizes into the consolidated database, these rows will be removed from the consolidated database, resulting in two synchronized databases. However, you'll have to define a way for the consolidated database's deleted rows to be removed from all your other remote databases. As these rows are already gone in the consolidated database, MobiLink does not distribute this information into the remote databases. You must keep track (somehow) of the primary keys of the deleted rows and send them via the download_delete_cursor script. A popular approach is to use a so-called shadow table. (CodeXchange has an example of such an implementation in the MobiLink section, "Shadow Deletes".)

Depending on what data the users change in the remote databases, you'll have to resolve conflicts between one or more of the remote databases and consolidated database. For a sample that demonstrates conflict detection and resolution see "MobiLink and Conflict Resolution Without Temporary Tables" on CodeXchange.

Your administrator's username and password for the remote database is stored in clear text within your application so that the synchronization user, publications, and subscriptions could be reconfigured by your application. You might consider a rewrite to store these values in encrypted text.

For a more in-depth look at MobiLink synchronization (I strongly recommend this as it's a very powerful feature), check out the SalesDB sample that comes with your Pocket PowerBuilder installation.

File-Based Synchronization
Let's look at the following scenario: we have a mobile application for the desktop that was intentionally written in PowerBuilder. There are some DataWindows in which the users enter data, but this data is not written into a database; it's just written to a file via the export function of the DataWindow. Now these files should be transferred somehow to the desktop.

Solution 1: ActiveSync
One solution is to use ActiveSync, which is the synchronization software for Windows-based Pocket PCs and Smartphones provided by Microsoft. Microsoft has built into ActiveSync the ability to select categories of calendars, contacts, tasks, and what we need files, so you can control what is synchronized with your Pocket PC. To be able to synchronize files (or even to synchronize something with our desktop PC) we need ActiveSync installed. The next step is to put your Pocket PC into the cradle or cable and connect it with the desktop PC. The first time you do this, ActiveSync will ask if you'd like to create a partnership with this PC. Take care since Microsoft designed ActiveSync to synchronize with only one Pocket PC at a time. There is no option to write an application to support more than one connected device at a time with ActiveSync, but you can connect one after another to the same PC. ActiveSync creates a registry for every device:

HKEY_CURRENT_USER SoftwareMicrosoftWindows CE ServicesPartners

where the is always different for each partnership. As we want to synchronize files be sure you enabled the file synchronization in ActiveSync (Open ActiveSync and click on Extras->Options, [see Figure 2]). If this is enabled, you'll find two interesting registry keys under your partnerID:

  • ServicesSynchronizationBriefcase Path
  • ServicesSynchronizationDeviceSyncFolder
The value of the DeviceSyncFolder registry key tells us where the files have to be on the Pocket PC so they can be transferred during ActiveSync synchronization to the directory defined in the Briefcase Path registry key. Feel free to modify this value if you prefer other locations (it might be quite a good idea to store documents on an additional storage card on the Pocket PC and not in the provided RAM and the defaultMy Documents folder, as you might lose all your data when the battery goes out).

Now we have one solution for our problem: we would use the DataWindow function SaveAs on the Pocket PC to create the files in the DeviceSyncFolder folder. When the synchronization takes place, the files will be copied by ActiveSync to the Briefcase Path, from where they could be easily imported into our desktop application, but take care as these files might still be in Unicode. To figure out which file types are automatically converted by ActiveSync, open ActiveSync, go to tools options, then click on the rules tab and look at the conversion settings for the desktop to device and vice versa. It might be necessary to read the file on the client side and convert it using the built-in FromUnicode function.

You can easily figure out if a file is a Unicode file by checking the first character of the BLOB you were reading from the file. If you compare it to Blob(Char(255)) and it's equal, you have a Unicode file that needs to be converted. I had a customer who is still using PowerBuilder 6.5.1, which doesn't offer the FromUnicode and ToUnicode functions. We had to use the MultiByteToWideChar and WideCharToMultiByte WinApi functions.

Solution 2: RAPI Calls
A more elegant way is to figure out when a device is connected to the cradle and get the files directly to the desktop and the Pocket PC from the desktop using a PowerBuilder application. Good idea but is this possible? Yes, it is; however, there are some problems that may have to be solved.

  1. It might not be possible that every Pocket PC that wants to synchronize with the desktop has a partnership.
  2. We need to be informed when there is a new device put into the cradle.
  3. We have to find the files from the desktop on the Pocket PC.

More Stories By Berndt Hamboeck

Berndt Hamboeck is a senior consultant for BHITCON (www.bhitcon.net). He's a CSI, SCAPC8, EASAC, SCJP2, and started his Sybase development using PB5. You can reach him under [email protected]

Comments (1)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.