PWB513: Building Database and Synchronization Applications with Pocket PowerBuilder

1. PWB513: Building Database and Synchronization Applications with Pocket PowerBuilder Ian Thain Technical Evangelist / Software Engineer ian.thain@sybase.com August 15-19, 2004

2. Ian Thain - PocketBuilder EvangelistITSG Evangelist Team, Sybase Inc.ian.thain@sybase.com

3. The Enterprise. Unwired.

4. The Enterprise. Unwired. Industry and Cross Platform Solutions Manage Information Unwire Information Unwire People • Adaptive Server Enterprise • Adaptive Server Anywhere • Sybase IQ • Dynamic Archive • Dynamic ODS • Replication Server • OpenSwitch • Mirror Activator • PowerDesigner • Connectivity Options • EAServer • Industry Warehouse Studio • Unwired Accelerator • Unwired Orchestrator • Unwired Toolkit • Enterprise Portal • Real Time Data Services • SQL Anywhere Studio • M-Business Anywhere • Pylon Family (Mobile Email) • Mobile Sales • XcelleNet Frontline Solutions • PocketBuilder • PowerBuilder Family • AvantGo Sybase Workspace

5. Agenda • Introduction • Unwired Enterprise • MobiLink • Developing An Application • Demonstrations

6. Introduction • Over the last 11 years we have seen PowerBuilder grow and develop into the tool it is today. Originally for Client Server, then incorporating Component Development & RAD Thin Client Development. • Also over the last 11 years we have seen increasing developments in DataWindow technology that has seen it appear (or not) as a non-visual DataStore, Java DataWindow and HTML DataWindow • DataWindows EVERYWHERE? Well nearly!

7. Introduction • "Don't think big…Think Small!!" • Pocket PowerBuilder is now coming to aid corporate development of Pocket PC applications, brining all the benefits • RAD 4 GL and even the DataWindow technology. • So we really can now say, DataWindows EVERYWHERE!!!!

8. JOLT • Recognizing the emergence of Mobile Development Tools in today’s wireless business environment, CMP Media's Software Development magazine has awarded a 14th Annual Productivity Award to Sybase Pocket PowerBuilder software. Sybase Pocket PowerBuilder was recognized as one of the best products or technologies in the award's Mobile Development Tools. • The awards were announced March 17 at the Santa Clara Convention Center in Santa Clara, California.

9. Unwired Enterprise • Liquidity + Mobility = Unwired Enterprise • Liquidity - Free flowing information with economic value • Mobility - Freedom to work anytime, anywhere

10. Enterprise Mobile Applications • Key areas for a mobile application • Usability (including intuitiveness, efficiency, ease of use & reliability) • Innovation (including New Functionality) – maybe from using mobile technologies • BPI (including extending existing systems) – it should fit well into existing business processes • ROI – As always a return on investment is a major factor

11. Enterprise Mobile Applications • Potential Systems for Mobile Applications • Data Collection • Inventory Management • Customer Relationship Management • Event Tracking • Sales Force Automation • Healthcare • Field Force Automation • Sales Force Automation • Mobile Healthcare

12. Mobile Smart Client • Mobile Smart Client • Application where client side business logic is executed on device • Persistent data storage on the device • Application can be executed at any time, even without a wireless connection • Integration to the enterprise is typically provided via synchronization • Client application communicates data to the back end via a synchronization server

13. Different Functionality • Synchronization • Pocket PowerBuilder also surpasses PowerBuilder with the introduction of a MobiLink wizard • Generates PowerScript code (NVO’s) & Windows for within your application, to interact with a MobiLink Server.

14. Different Functionality • Adaptive Server Anywhere mobile database • Uses full data storage capabilities of Adaptive Server Anywhere on Windows CE client • Database access via ODBC • MobiLink data synchronization • MobiLink bi-directional data synchronization used to integrate with other enterprise databases (Sybase ASE and ASA, Oracle, MS SQL Server, IBM DB2) • New component to manage synchronization properties & maintenance of synchronization

15. Database Objects • A Database Object in the Remote Database data to be synchronized • Tables can be synchronized • Entirely • Subset of Columns • Subset of Rows • Subset of Columns & Rows

16. Publication • Define a Publication • The publications determine what information is shared among which database • Keep the number of publications small. • In particular, try not to reference the same table in many different publications. • The work the database server needs to do is proportional to the number of publications. • Keeping the number low and making effective use of subscriptions lightens the load on the database server. • Group publications logically • If there is a table that every remote user requires, such as a price list table, make a separate publication for that table. Make one publication for each table where the data can be partitioned by a column value.

17. Synchronization User • Define a Synchronization User • MobiLink user name and password to connect to a MobiLink synchronization server

18. Synchronization Subscription • Define a Synchronization Subscription • The subscriptions determine what information each user receives • A synchronization subscription links a particular MobiLink user with a publication. • It can also carry other information needed for synchronization. • For example, you can specify the address of the MobiLink server and any desired options for a synchronization subscription. • Values for a specific synchronization subscription override those set for MobiLink users.

19. Remote Database • Located on the mobile device • Can be built with • Sybase ASA • UltraLite • Must contain at least one Publication to define which tables will be synchronized • Must contain at least one Synchronization Subscription to link a Synchronization user with a set of Publications • Typically a subset of a consolidated database • Data specific or that mobile user

20. Consolidated Database • Central / Master DataStore with which the remote synchronizes with • Can be built with • Sybase ASA • Sybase ASE • Oracle • MS SQL Server • IBM DB2 • Typically an existing Enterprise Database

21. Consolidated Database • MobiLink uses system tables in the Consolidated database to store • Synchronization scripts • Synchronization progress & status information • Synchronization User information • Automatically created in ASA Database • MobiLink Setup scripts for other Consolidated Databases • MobiLink uses ODBC to connect to the Consolidated DB

22. Synchronization Scripts • During synchronization MobiLink progresses through a series of events • Each event can be associated with once synchronization script • Synchronization scripts • Direct the data flow during the process • Allow tasks to be performed at various points • Optimize performance • Written in the native SQL of the Consolidated Database • Java • .NET • Can use versioning • Remote must specify the version of the scripts to use

23. Synchronization Scripts • download_cursor • SELECT ASSESSOR_ID, ASSESSOR_FNAME, ASSESSOR_LNAME, ASSESSOR_EMAIL, ASSESSOR_TEL FROM ASSESSOR WHERE LAST_MODIFIED >= ? AND ASSESSOR_ID = ? • Event parameters are optional only if no subsequent parameters are specified. You must use parameter 1 if you want to use parameter 2. Item Parameter Description 1 last_download TIMESTAMP 2 ml_username VARCHAR(128) • The last_download timestamp is the value obtained from the consolidated database during the last successful synchronization immediately prior to the download phase. • If the current user has never synchronized successfully, this value is set to 1900-01-01.

24. Synchronization Scripts • upload_Insert • INSERT INTO ASSESSOR ( ASSESSOR_ID, ASSESSOR_FNAME, ASSESSOR_LNAME, ASSESSOR_EMAIL, ASSESSOR_TEL ) VALUES ( ?, ?, ?, ?, ? ) • upload_update • UPDATE ASSESSOR SET ASSESSOR_FNAME = ?, ASSESSOR_LNAME = ?, ASSESSOR_EMAIL = ?, ASSESSOR_TEL = ? WHERE ASSESSOR_ID = ? • upload_delete • etc

25. MobiLink Server • Connects to the Consolidated Database • Waits for Synchronization requests from Remote DB • Processes data uploaded from the Remote DB • Selects data from the Consolidated Database for download to the remote

26. dbmlsync • Synchronization is initiated by a client application via the dbmlsync utility from ASA Remote databases • TCP/IP, SSL • HTTP/HTTPS • ActiveSync • Read Database engine transaction log • Builds an upstream with the a summary of the changes to the specific publication since the last synchronization • Applies the downstream from the MobiLink server to the Remote Database

27. MobiLink – ASA Synchronization Upload dbmlsync Download MobiLink Acknowledge Download ODBC Consolidated Database Remote ASA Database

28. MobiLink – ASA Synchronization Upload dbmlsync Download MobiLink Acknowledge Download Upload • ASA or UltraLite keep track of all changes since previous synchronization • All changes are sent in a single upload stream • MobiLink applies changes in a single transaction ODBC Consolidated Database Remote ASA Database

29. MobiLink – ASA Synchronization Upload dbmlsync Download MobiLink Acknowledge Download ODBC • Download • Scripts in the consolidated database determine changes that need to be download • All changes are sent in a single download stream Consolidated Database Remote ASA Database

30. MobiLink – ASA Synchronization Upload dbmlsync Download MobiLink Acknowledge Download ODBC Acknowledge Download • Optional Consolidated Database Remote ASA Database

31. MobiLink - Quick Start • Files located in \Samples\MobiLink\Autoscripting • Create and setup consolidated and remote databases • Use Sybase database creation wizard • Create ODBC datasources • Populate databases with ISQL • Run setup script appropriate for consolidated DB

32. MobiLink - Quick Start • Identify tables and columns for synchronization • Add Publications, ML User and Subscriptions on Remote DB – DB Specific for that ML User • Add Tables and Users for Synchronization on Consolidated DB , (Synchronization Scripts – see next slide)

33. MobiLink - Quick Start • Write scripts to upload and download data • Use –za switch on the MobiLink Server • and ‘SendColumnNames=on’ on the MobiLink client • to automatically generate scripts for you • Or Use…ulinit (Ultralite Analyzer) • ulinit -c "dsn=IA;uid=DBA;pwd=SQL" -f deleteme.usm -n IA_Pub -s IA_Pub -m default • Write Scripts and hold in creation SQL Batch files for Consolidated DB • You are now synchronizing!

34. MobiLink - Quick Start @echo off SETLOCAL set DSN=IA set ASA_ANY=%ASANY9% set ASA_CMD=dbmlsrv9.exe dbmlstop -w %DSN% if exist dbmlsrv.mle del dbmlsrv.mle set ML_parms=-v -zs %DSN% -notifier -c dsn=%DSN% -fr -q -x tcpip(port=2460) start %ASA_CMD% %ML_parms% ENDLOCAL

35. Developing an Application • When developing a Mobile Application always work against the Remote Database • Best to deploy an empty Remote Database and fill with data on the first Sync. • Use PPB to generate Synchronization logic in NVO’s

36. Developing an Application • DSN on mobile device specifies which ASA Database the app uses [ODBC] uid=dba pwd=sql enginename=PPBDemos databasename=IA_remote databasefile=\Program Files\PPBDemos\IA\IA_remote.db start=\Program Files\Sybase\ASA\dbsrv9.exe -q driver=dbodbc9.dll • Resides on root of the PPC Device

37. New Features - SIS • Server Initiated Synchronization / Message Push • Server-initiated synchronization allows you to initiate MobiLink synchronization from the consolidated database. • This means you can push data updates to remote databases, as well as cause remote databases to upload data to the consolidated database. • Includes the new ASA 9.0 • Build #1271 includes ‘Server Initiated Sync’ message post to a PPB app. This is an EBF to ASA 9.0.0

38. New Features - SIS

39. New Features - SIS • Notifier on Server • -notifier flag on dbmlsrv9.exe • Uses a config file

40. New Features - SIS #------------------------------------ # config.notifier #------------------------------------ # This setup file configure and enables two objects: # 1. A UDP gateway called Gate2 # 2. A Notifier called "Simple" # # This file does not contain full descriptions of properties. # Full descriptions are available in %asany%\MobiLink\template.notifier. # # Common settings # verbosity = 2 #

41. New Features - SIS # Notifier configuration -- Simple # Notifier(Simple).enable = yes Notifier(Simple).isolation = Notifier(Simple).connect_string = Notifier(Simple).poll_every = 3 Notifier(Simple).gui = off Notifier(Simple).begin_connection = Notifier(Simple).begin_poll = Notifier(Simple).shutdown_query = Notifier(Simple).request_cursor = select req_id, gateway, subject, content, \ address, resend_minute, minute_to_live from PushRequest Notifier(Simple).request_delete = delete from PushRequest where req_id = ? Notifier(Simple).end_poll = Notifier(Simple).end_connection =

42. New Features - SIS # # SMTP gateway configuration -- Gate1 (not enabled by default in this sample) # SMTP(Gate1).enable = no SMTP(Gate1).server = SMTP(Gate1).user = SMTP(Gate1).password = SMTP(Gate1).sender = # # UDP gateway configuration -- Gate2 # UDP(Gate2).enable = yes UDP(Gate2).sender = UDP(Gate2).sender_port = UDP(Gate2).listener_port =

43. New Features - SIS • Listener Utility on PPC 55#"\Program Files\Sybase\ASA\dblsn.exe" @IA_dblsn.txt

44. New Features - SIS • Listener Configuration file on PPC #---- SIS_dblsn.txt -------------------------------------------- # This is the default argument file for dblsn.exe #------------------------------------------------------------------------------ # Verbosity level 2 -v2 #------------------------------------------------------------------------------ # Show notification messages in console and log -m #------------------------------------------------------------------------------ # Polling interval of 6 seconds -i 6 #------------------------------------------------------------------------------ # Truncate, then write output to dblsn.log -ot dblsn.log • Resides on root folder of PPC device

45. New Features - SIS • Message Handler for PPB App • 1024 – 1098 / PBM_Custom01 – PBM_Custom75 # First message handler # - Only applies to messages starting with [shutdown] # - The action is to shut down the MobiLink listener -l " message_start=[shutdown]; action='DBLSN FULL SHUTDOWN' " #------------------------------------------------------------------------------ # Message handler -l " message_start=[1024]; action='post 1024 to IA App'; continue=yes; " #------------------------------------------------------------------------------

46. New Features - SIS • Example PPB Code

47. New Features in PPB • Enhancements to MobiLink features

48. Demonstration Insurance Assessor App (Field Force Automation) Currently a manual procedure Paper Notebooks Camera Input into back office system at EOD Dev Team PB Experience PD Experience PPB App is nearly complete.

49. Conclusions • Pocket PowerBuilder used along with ASA for CE and MobiLink can create very powerful Smart Client Applications • Data can be synchronized back to the Enterprise Database

50. Pocket PowerBuilder Code Examples • Pocket PowerBuilder code examples can be found on the CodeXchange, http://pocketpb.codexchange.sybase.com