PWB506 - Building Database and Synchronization Applications with Pocket PowerBuilder

1. PWB506 - Building Database and Synchronization Applications with Pocket PowerBuilder Martyn Mallick Unwired Solutions Evangelist mmallick@sybase.com August 6, 2003

2. What You Will Learn How Pocket PowerBuilder simplifies the creation of ‘occasionally connected’ mobile applications that synchronize data with any of the leading enterprise databases. B E G I N N I N G

3. Agenda • Mobile Computing Architectures • Pocket PowerBuilder Key Features • A Look at SQL Anywhere Studio • Developing Database Applications with Pocket PowerBuilder • Creating Remote Databases • The Power of DataWindows • Using the Pocket PowerBuilder Synchronization Wizard • Setting up Synchronization Server • Application Deployment – What goes where? • Summary • Additional Resources

4. Mobile Computing Architectures Three main architectures for Mobile Applications • Wireless Internet (online only) • Online access to Web content • Typically used for consumer applications • Limited user interface and capabilities • Smart Client • Incorporates mobile database technology for persistent data storage • Updates to data are synchronized to enterprise systems • Allows for offline access to important data • Works best in ‘occasionally connected’ environment • Messaging • Store and forward delivery of data • Can be used on its own, or in addition to other architectures

5. Mobile Application Architectures iAnywhere m-Business Platform

6. Mobile Application Architectures Smart Client vs Thin Client • Smart Client • Some business logic resides within the client • Logic updates require client redistribution • Offline processing, can work in any environment • Does not required wireless connectivity • Synchronization required for data integration • Thin Client • All business logic is invoked from a server • Logic updates can occur without client redistribution • Online processing only, if wireless connection is not available, application is rendered useless • Synchronization is not required to ensure fresh data

7. Agenda • Mobile Computing Architectures • Pocket PowerBuilder Key Features • A Look at SQL Anywhere Studio • Developing Database Applications with Pocket PowerBuilder • Creating Remote Databases • The Power of DataWindows • Using the Pocket PowerBuilder Synchronization Wizard • Setting up Synchronization Server • Application Deployment – What goes where? • Summary • Additional Resources

8. Pocket PowerBuilder Key Features Smart Client Development with Pocket PowerBuilder • For occasionally connected application development, Pocket PowerBuilder has three key features: • Productive 4GL IDE • DataWindow Technology • Tight integration with SQL Anywhere Studio

9. Pocket PowerBuilder Key Features Productive 4GL Environment • Supports complete development cycle • Visual development with minimal coding • Painters for database creation and application development • Database Painter • DataWindow Painter • Synchronization … • Complements PowerBuilder for Windows CE deployments • Similar look and feel, but separate IDE • Ability to export PB projects and import them into Pocket PowerBuilder • Rich Set of components • Command Button, Picture Button, CheckBox, Radio Button, Static Text, List View, Tree View, List Box, DropDown List Box, Edit Mask, Single Line Edit, MultiLine Edit, Progress Bar, Scroll Bars, Line, Oval, Rectangle, Tabs, and many, many more!

10. Pocket PowerBuilder Key Features DataWindow Technology • Single component for displaying and manipulating data • Built in SQL for selects, updates, inserts and deletes • Available in many formats • Freeform • Graph • Grid • Group • Tabular • Reuse existing PowerBuilder DataWindows with little effort

11. Pocket PowerBuilder Key Features SQL Anywhere Integration • Adaptive Server Anywhere mobile database • Uses full data storage capabilities of Adaptive Server Anywhere on Windows CE client • Database access via ODBC • Development version comes with Pocket PowerBuilder • 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) • MobiLink Synchronization Wizard for simplified synchronization development

12. Agenda • Mobile Computing Architectures • Pocket PowerBuilder Key Features • A Look at SQL Anywhere Studio • Developing Database Applications with Pocket PowerBuilder • Creating Remote Databases • The Power of DataWindows • Using the Pocket PowerBuilder Synchronization Wizard • Setting up Synchronization Server • Application Deployment – What goes where? • Summary • Additional Resources

13. A Look at SQL Anywhere Studio • Small Footprint, full-featured, easily embeddable DBMS • Adaptive Server Anywhere (ASA) and UltraLite • Enterprise synchronization formobile and remote systems • MobiLink and SQL Remote Market leading mobile, workgroup and embedded data management and enterprise synchronization solution

14. A Look at SQL Anywhere Studio SQL Design Goals • Ease of use • Performance out of the box • Easily Embeddable • Interoperability/Open • Being responsive to our customers

15. Easy for end-users Install in less than 5 minutes DB binary compatible across platforms Easy for developers Tight integration with Pocket PowerBuilder Referential Integrity Row level locking Stored procedure debugger Easy for Administrators Sybase Central Graphical Administration tool Command line tools eases scripting DB System Events enable near-zero administration A Look at SQL Anywhere Studio ASA – Ease of Use

16. A Look at SQL Anywhere Studio ASA – Performance Out of the Box • Cost-based Optimizer automatically finds the most efficient method to access the data requested • Optimization statistics automatically maintained • Auto generation of statistics on database load • Dynamic cache size • Separate storage for long values • LONG BINARY, LONG VARCHAR, TEXT, IMAGE • Automatic support for SMP machines • Sophisticated caching algorithm satisfies needs for OLTP and DSS • Index Consultant to help find locate areas where Index should be present

17. A Look at SQL Anywhere Studio ASA – Easily Embeddable • Small Footprint • ASA was originally designed to allow an application AND the database server to run on a 640k DOS PC • Runs on platforms ranging from Windows CE and up • Simple installation • Can be made transparent for end-user • Under 10 minute process • Can be scripted to be silent • Install Shield templates provided

18. A Look at SQL Anywhere Studio ASA – Responsive to Customers • Customer requirements and requests dictate new features • New innovation to solve customer problems • One of the best forums is our Internet newsgroups • Winner of InfoWorld “Best Support” in 2000 • sybase.public.sqlanywhere.general • sybase.public.sqlanywhere.mobilink • sybase.public.sqlanywhere.product_futures_discussion • sybase.public.sqlanywhere.replication • sybase.public.sqlanywhere.ultralite • sybase.public.sqlanywhere.linux

19. A Look at SQL Anywhere Studio Data Synchronization • MobiLink • A two-way session-based synchronization technology for large scale mobile database deployment • remote database (mobile, embedded, or workgroup database server) • consolidated database (enterprise, workgroup, or desktop database) • Pocket PowerBuilder has a wizard for MobiLink synchronization! • SQL Remote • A message-based synchronization that can use multiple transport mechanisms • MS Mail, Lotus Notes, SMTP, FTP and File

20. A Look at SQL Anywhere Studio MobiLink Architecture

21. A Look at SQL Anywhere Studio MobiLink Remote Databases • Adaptive Server Anywhere (ASA) • Windows 2000/XP/NT/Me/98/95 • Windows CE • Linux and Unix • UltraLite • Palm Computing Platform • Windows CE • Java

22. A Look at SQL Anywhere Studio MobiLink Consolidated Databases • Adaptive Server Anywhere (ASA) • Adaptive Server Enterprise (ASE) • Oracle • Microsoft SQL Server • IBM DB2 UDB • List of Recommended ODBC Drivers • www.sybase.com/detail?id=1011880

23. 1. 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 2. Download • Scripts in the consolidated database determine changes that need to be downloaded • All changes are sent in a single download stream 3. Acknowledge download • Optional A Look at SQL Anywhere Studio MobiLink – ASA Synchronization

24. A Look at SQL Anywhere Studio 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 • Identify tables and columns for synchronization • Add publications and subscriptions • Write scripts to upload and download data • Use –za switch to automatically generate scripts for you • You are now synchronizing!

25. Agenda • Mobile Computing Architectures • Pocket PowerBuilder Key Features • A Look at SQL Anywhere Studio • Developing Database Applications with Pocket PowerBuilder • Creating Remote Databases • The Power of DataWindows • Using the Pocket PowerBuilder Synchronization Wizard • Setting up Synchronization Server • Application Deployment – What goes where? • Summary • Additional Resources

26. Developing Database Applications • Database development in Pocket PowerBuilder is similar to PowerBuilder…with a few important differences • The Pocket PowerBuilder application does not connect directly to the enterprise database • A mobile database is used on the Pocket PC device to store the information pertinent to the mobile user • Updated information is communicated to back end databases using synchronization • The synchronization technology allows for integration with all of the leading enterprise databases

27. ASA ML Developing Database Applications Application Architecture Device Enterprise Consolidated Database (Sybase/Oracle/ Microsoft/IBM) Pocket PowerBuilder Application Pocket PowerBuilder VM TCP/IP HTTP HTTPS ActiveSync (wired or wireless) MobiLink

28. Developing Database Applications • Occasionally Connected Applications have three main benefits: • Guaranteed access to critical data • High performance as wireless connectivity issues does not hinder performance • Extended battery life since wireless connectivity is minimized

29. Developing Database Applications Quick Tips • Good UI is very important • More important than on the desktop? • Handheld != desktop • No keyboard • Small screen • Platform differences are significant • How do you squeeze lots of information onto a small screen? • Tab controls, menus, etc. • Iterative development • Keep application logic separate from GUI!

30. Developing Database Applications Five Main Steps • Creating Remote Databases • The Power of DataWindows • Using the Pocket PowerBuilder Synchronization Wizard • Setting up Synchronization Server • Application Deployment – What goes where? • Pocket PowerBuilder simplifies all of these steps!!!

31. Creating Remote Databases • Sybase Adaptive Server Anywhere is used at the remote database • The DBPainter allows you to create new ASA databases from scratch…

32. Creating Remote Databases • … BUT, in most cases you will want to create your remote database to reflect an existing consolidated database • The remote database is typically a subset of your enterprise database • Smaller schema • Less data (MobiLink synchronization can take care of this) • To accomplish this you have three options • SQL Anywhere Studio has migration assistants for ASE, Oracle, IBM and Microsoft • PowerDesigner can reverse engineer a database and create an ASA database with the same schema • You can use any other database design tool that comes with your enterprise database or otherwise

33. Developing Database Applications • Once there is a remote database, the application can be developed • Application is developed using remote database – not consolidated! • Use typical development procedures to accomplish this • Pocket PowerBuilder has some key features that simplify the process • DataWindows • Synchronization Wizard • Project deployment and debugging

34. The Power of DataWindows Supported DataWindow formats • Pocket PowerBuilder supports five DataWindow Formats

35. The Power of DataWindows DataWindow key concepts • Single control for all data access and manipulation • The most sophisticated data presentation control available! • DataWindows are created in painters, allowing for productive development without extra coding • Data retrieval occurs at design time so you see what you are getting as you develop • Selects, Inserts, Deletes and Updates are handled automatically • And more and more and more…

36. The Power of DataWindows DataWindow Example

37. Pocket PowerBuilder Synchronization Wizard MobiLink Synchronization in a Flash! • Develop a client/server application to start… • …Then add Synchronization using the Synchronization Wizard!

38. Pocket PowerBuilder Synchronization Wizard • The MobiLink Synchronization Wizard handles ALL aspects of enterprise database synchronization • Remote database connection • Publications for synchronization • Synchronization server connection information • Synchronization client options • User interface generation • Everything that the Wizard generates if fully modifiable later on by the application developer!

39. Synchronization Server Setup • MobiLink Server can be configured and started from Database Painter • Use Sybase Central to create synchronization scripts • Scripts can be automatically generated using –za switch on MobiLInk server, and ‘SendColumnNames=on’ on the MobiLink client

40. Application Testing and Deployment Application Testing • Test EVERY part of your application: • Device • Application • Network(s) • Synchronization • Integration with back-end • …

41. Application Testing and Deployment Application Testing • Can occur in three ways: • On the Desktop • Does not use the same VM, therefore not accurate representation of device • Good for quick testing early on • On a device Emulator • Uses Windows CE VM, but not for the same chipset as physical device • Pocket PC 2000 Emulator is not recommended – many bugs • Pocket PC 2002 Emulator is decent • Pocket PC 2003 Emulator is a bit better, but still ‘clunky’ • On the device itself • Best way to determine if application will work correctly at deployment • Can also test network connectivity at this point

42. Application Testing and Deployment Deployment Platforms • Pocket PowerBuilder applications can be run on the mobile device in two ways: • 1) Using the Applist application that is installed into the Pocket PC start menu as Pocket PowerBuilder 1.0 • 2) By clicking directly on the application .exe (which is put into the ..\windows directory by default) • Deployment Platforms • ARM based Pocket PC devices • This covers all devices coming out with Pocket PC 2002 and Windows Mobile 2003 operating systems • MIPS, SH3 chipsets are not supported as device manufacturers have all but stopped making these devices

43. Application Testing and Deployment Application Deployment • Pocket PC applications are often distributed as CAB files • Pocket PC SDK comes with ‘cabwiz’ which is a utility to package applications into self installing CAB files • This technology has been incorporated into Pocket PowerBuilder for easy application packaging

44. Application Testing and Deployment Manage Anywhere Studio • Automated central software distribution • Including file distribution and collection • Complete device-level hardware and software inventory • Support mission-critical mobile environment • Self-healing configurations • Remote control – reach out and help somebody • Security management • Keep virus protection, OS up to date • Guard against unauthorized software, configuration • Optimized for mobile, remote, embedded • Simple for administrator and end-user • Offline operation, suspend & resume, minimal bandwidth use • Manage desktops, laptops, PDAs

45. Agenda • Mobile Computing Architectures • Pocket PowerBuilder Key Features • A Look at SQL Anywhere Studio • Developing Database Applications with Pocket PowerBuilder • Creating Remote Databases • The Power of DataWindows • Using the Pocket PowerBuilder Synchronization Wizard • Application Deployment – What goes where? • Summary • Additional Resources

46. Summary • Pocket PowerBuilder provides the features that are required to develop enterprise smart client applications • PowerBuilder users can use their existing skills and application code to develop Windows CE applications • Windows CE applications are similar, but yet quite different than desktop applications, so design your applications accordingly • Database development is facilitated by Painters and Wizards to increase productivity

47. Additional Resources • Pocket PowerBuilder Product Site • www.sybase.com/pocketpowerbuilder • Pocket PowerBuilder CodeXchange • http://pocketpb.codexchange.sybase.com • SQL Anywhere Studio Product Site • www.ianywhere.com/sqlanywhere • Manage Anywhere Studio • www.ianywhere.com/manageanywherestudio • Mobile and Wireless Design Essentials • www.amazon.com/mallick • Microsoft Pocket PC Info • www.microsoft.com/pocketpc

48. SDN Presents CodeXchange Share Pocket PowerBuilder Code and Tools • New SDN feature enables community collaboration • Download samples created by Sybase • Leverage contributions of others to integrate with native Pocket PC APIs • Contribute your own code or start your own collaborative project with input from other Pocket PowerBuilder experts • Any SDN member can participate • Log in using your MySybase account via SDN • Join the collaboration already underway • http://pocketpb.codexchange.sybase.com or via SDN at www.sybase.com/developer • SDN & CodeXchange at TechWave • Technology Boardwalk • Sybase Booth Theater

49. iAnywhere Solutions at TechWave2003 Activities for iAnywhere Solutions • Ask the iAnywhere Experts on the Technology Boardwalk • Drop in during exhibit hall hours and have all your questions answered by our technical experts! • Appointments outside of exhibit hall hours are also available to speak one-on-one with our Senior Engineers. Ask questions or get your yearly technical review – ask us for details • m-Business Pavilion • Visit the m-Business Pavilion in the exhibit hall to see how companies like Intermec have built m-Business solutions using iAnywhere Solutions technology • Wi-Fi Hotspots – brought to you by Intel & iAnywhere Solutions • You can enjoy wireless internet access via a Wi-Fi hotspot provided by Intel and iAnywhere Solutions. Using either a laptop or PDA that is Wi-Fi 802.11b wirelessly-enabled, visitors can access personal email, the internet ,and "TechWave To Go", a My AvantGo channel providing up-to-date information about TechWave classes, events and more.

50. iAnywhere Solutions at TechWave2003 Activities for iAnywhere Solutions • Developer Community • A one-stop source for technical information! • Access to newsgroups,new betas and code samples • Monthly technical newsletters • Technical whitepapers,tips and online product documentation • Current webcast,class,conference and seminar listings • Excellent resources for commonly asked questions • All available express bug fixes and patches • Network with thousands of industry experts • http://www.ianywhere.com/developer/