BooKeeper

1
BooKeeper

• Replication in Pocket PC Environment using
  Mobilink

Sridevi Srinivasan
CS 8628, Summer 2003
2
Introduction

• BooKeeper is an application developed for Pocket
  PC environments using MobileVB.
• This application demonstrates the capabilities of
  the synchronizing capabilities of Mobilink
• For this application Adaptive Server Anywhere
  database is used as the Consol database (Sync
  Server) and Ultralite database is used as the
  Remote database(Sync Client

• Sql Anywhere is chosen since it has all
  synchronization setups built into it.
• Ultralite is chosen for client since the API
  supports synchronization from within code.

3
Abstract

• Bookeeper schema consists of three tables.
  (Author, Publisher and Books).
• The schema is identical between the consol and
  the remote databases.
• Synchronization has been implemented only for the
  Author and Publisher tables.
• A step by step setup document has been created to
  illustrate the synchronization.(BooKeeper
  Setup.doc)

• The Setup document describes how to create and
  populate the Consol database.
• The ultralite schema file already has the table
  and publication definitions built into it.

4
Sequence of Tasks

• In a nutshell, the steps are
• Create a Consolidated Database
• Create Tables and Insert Data in Consolidated
  Database
• Create an ODBC Data Source for Consolidated
  Database
• Create Synchronization Scripts
• Create the Remote Database using Schema Painter
• Create Tables and Publication for the remote
  database.
• Create Syncrhonization Subscription
• Develop the App using MobileVB to read write and
  sync from these tables.
• Deploy the app to the Pocket PC.
• Start Mobilink Synchronization Server
• Start the application and Sync the databases from
  the Pocket PC

• The steps involving creating the remote
  database,the tables and the publication is all
  done using the schema painter.

5
Project Description

• The BooKeeper App consists of three forms.
• frmDetail This form has the ability to read
  insert Sync data for all the three entities of
  the schema.
• form_sync Default Sync template form provided
  by ultralite to indicate the progress of syncing.
• frmMain This is a launcher pad
• The idea is to be able to add a new author from
  the pocket pc and sync it to the Sync Server
  database and vice versa.
• The synchronization scripts have been written
  upload_insert and download_cursor events only
  for the tables Author and Publisher.

• Since the sync scripts have been written for only
  upload_insert and download_cursor only, the
  databases are in kept in sync only for
  Insertions.

6
E-R Diagram

• The actual database does not have all the columns
  depicted in here for simplicity.

7
Logical Schema

• Assumption A book has only one Author and one
  Publisher.

8
Physical Schema (DDL)

• create table Author (
• AuID char(12) primary key,
• AuName varchar( 50 )
• )
• go
• create table Publisher (
• PubID char(12) primary key,
• PubName varchar( 50 )
• )
• go
• create table Books (
• BkID int primary key,
• Title char(64),
• AuID char(12) references Author(AuID ),
• PubID char(12) references Publisher(PubID)
• )
• go

• All the columns mentioned in the ER diagram are
  not implemented in the DDL.

9
Matrix Forms vs. Tables

• frmDetail is the only form that reads and writes
  to the database
• modConst is a bas module that has not been
  displayed here. It just has some global const
  declarations.
• The code behind Form_sync is auto generated.

10
Publication Script 1

• The publication scripts for this project were
  created using the ultralite schema painter.
• The Name of the publication is pubMain and it
  includes all the three tables in the schema
  (Author, Publisher Books)

11
Synchronization Script

• call ml_add_table_script( 'default','Author','uplo
  ad_insert','insert into Author(AuID,AuName)
  values(?,?)')
• call ml_add_table_script( 'default','Author','down
  load_cursor','select AuID,AuName from Author')
• call ml_add_table_script( 'default','Publisher','u
  pload_insert','insert into Publisher(PubID,
  PubName) values (?, ?)')
• call ml_add_table_script( 'default','Publisher','d
  ownload_cursor','select PubID, PubName from
  Publisher')

• Prior to running this script the actual tables
  the Script version default needs to be created
  in the Consol database. The step by step
  instructions in the setup guide walks you thro
  this.
• As you can see the synchronization is implemented
  for only the upload_insert and
  download_cursor events only. The idea is to
  depict that synchronization can be done.
• This script needs to be run on the consol
  database from interactive SQL.

12
Difficulties Encountered

• Appforge Licence Key Problem. (Apps can be
  created and run on the pkt pc for only two days
  with the evaluation license. You need to get a
  new one after that for every two days)
• SQLE_UNABLE_TO_START error while connecting to
  the database from the pocket pc. (This was due to
  a path problem. Once I fixed the connect string
  to have the right paths, the problem went away)
• When I deploy a database created on the desktop
  to the pocket PC, for some reason the database is
  not opened correctly. Therefore, I had to ship
  only the ultralite schema file and create a new
  database off of that from the PocketPC.

13
Screen Snapshot 1
14
Code Sample 1

• To connect to the database
• If appforge Then
• sAppPath "\BooKeeper"
• Else
• sAppPath App.Path
• End If
• conn_parms "uidDBApwdSQL"
• open_parms conn_parms "FILE_NAME" sAppPath
  "\" dbName ".udbce_file" sAppPath "\"
  dbName ".udb"
• schema_parms open_parms "schema_file"
  sAppPath "\" dbName ".usmce_schema"
  sAppPath "\" dbName ".usm
• Set Connection DatabaseMgr.OpenConnection(open_p
  arms)
• To insert to the Author table
• desTable.InsertBegin
• desTable.Column("AuID").StringValue
  txtID.Text
• desTable.Column("AuName").StringValue
  txtName.Text
• desTable.Insert
• ' Perform this transaction
• Connection.Commit

• The sAppPath variable the forms the connection
  parameters needs to bet according to whether the
  app is running on the IDE or on the pocket pc.
• While navigating between records, the code
  detects eof and bof conditions and informs the
  user whether it is the last or first record.

15
Conclusion

• The lifecycle for developing to implementing this
  project was very similar to a normal three tier
  application development.
• Since the mobileVB IDE was easier to use and it
  integrated well with the ultralite databases,
  there were no surprises.
• This has been a good learning experience and
  thank you very much Prof. Mario Guimaraes.

• It would have been really cool to have performed
  some wireless application development along with
  this.
• .NET Compact framework would have been my choice
  of product for that.