1 / 51

PWB513: Building Database and Synchronization Applications with Pocket PowerBuilder

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

remedy
Download Presentation

PWB513: Building Database and Synchronization Applications with Pocket PowerBuilder

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related