MBS904 Building Database and Synchronization Applications with Pocket PowerBuilder

1
MBS904 - Building Database and Synchronization
Applications with Pocket PowerBuilder
Martyn Mallick Unwired Solutions
Evangelist mmallick_at_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
Market leading mobile, workgroup and embedded
data management and enterprise synchronization
solution

• Small Footprint, full-featured, easily embeddable
  DBMS
• Adaptive Server Anywhere (ASA) and UltraLite
• Enterprise synchronization formobile and remote
  systems
• MobiLink and SQL Remote

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
A Look at SQL Anywhere Studio
ASA Ease of Use

• 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

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_discussi
  on
• 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?id1011880

23
A Look at SQL Anywhere Studio
MobiLink ASA Synchronization
24
A Look at SQL Anywhere Studio
MobiLink key features

• Flexible and robust bi-directional
  synchronization
• Support Sybase, Oracle, IBM DB2, Microsoft SQL
  Server
• Synchronization scripts can be written in SQL,
  Java or any .NET language
• Secure
• Built-in authentication
• 128-bit encryption of communication stream
• Support for sub-setting and partitioning of data
• Built-in conflict detection and resolution
• Scalable to thousands of remote users
• Robust error handling

25
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!

26
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

27
Developing Database Applications

• Database development in Pocket PowerBuilder is
  similar to PowerBuilderwith 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

28
Developing Database Applications
Application Architecture
Device
Enterprise
Consolidated Database (Sybase/Oracle/ Microsoft/IB
M)
Pocket PowerBuilder Application
Pocket PowerBuilder VM
TCP/IP HTTP HTTPS ActiveSync (wired or wireless)
MobiLink
29
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

30
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!

31
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!!!

32
Creating Remote Databases

• Sybase Adaptive Server Anywhere is used at the
  remote database
• The DBPainter allows you to create new ASA
  databases from scratch

33
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

34
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

35
The Power of DataWindows
Supported DataWindow formats

• Pocket PowerBuilder supports five DataWindow
  Formats

36
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

37
The Power of DataWindows
DataWindow Example
38
Pocket PowerBuilder Synchronization Wizard
MobiLink Synchronization in a Flash!

• Develop a client/server application to start
• Then add Synchronization using the
  Synchronization Wizard!

39
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!

40
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 SendColumnNameson
  on the MobiLink client

41
Application Testing and Deployment
Application Testing

• Test EVERY part of your application
• Device
• Application
• Network(s)
• Synchronization
• Integration with back-end

42
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

43
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

44
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

45
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

46
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

47
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

48
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

49
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

50
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.

51
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/

52
(No Transcript)