Title: Arial 28pt.

1
MES905 Building Mobile Web Applications with
SQL Anywhere
David Loop Principle Consultant daveloop_at_ianywhere
.com August 15-19, 2004
2
The Enterprise. Unwired.
3
The Enterprise. Unwired.
Industry and Cross Platform Solutions
Unwire People
Unwire Information
Manage Information

• 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
4
The Mobile World
5
Mobile Always Available Applications

• Extend enterprise information to mobile workers
  on demand
• Mobile workers need to be productive anywhere,
  anytime
• Wireless access is not 100 (underground,
  countryside, restricted areas)
• Always available model leverages
• Local Data Store
• Accesses important/frequent information offline
• Synchronize when necessary
• Increases batterly life and performance
• Saving on connection costs
• Enterprise Information
• Interface with enterprise systems for data and
  access
• Appy and enforce existing enterprise security
  standards

6
Challenges to Mobile Web Applications

• Mobilize Enterprise Data
• Security
• Performance
• Reliability
• Development Effort
• Deployment Effort
• Cost

But these are not specific to Mobile Web
Applications
7
Real Challenges to Mobile Web Applications

• Mobile application adoption, usability
• Enterprise integration and access
• Enterprise security
• device, transmission and authentication
• Distributed application logic
• Device characteristics
• form follows function
• Device disparity
• Distributed and mobile deployment
• Re-deployment and healing
• Development learning curve

8
Why Mobile Applications with M-Business Anywhere?

• Delivers web content and web applications
• Web developers leverage their existing skill sets
  with open standards
• rapid development
• low cost of ownership
• rich environment
• Dynamic Mobility and Connectivity
• Widest range of mobile devices over any network
• Seamless transition between online and offline
• Centralized User, Content and End-to-End Security
  Management
• Mobile Portal Self-Service Configuration

9
M-Business Anywhere (M-BA)

• M-BA Components
• Multi-platform client browser
• HTTP/Synchronization Server
• Features
• Integrates into existing web
• Synchronizes and caches web pages on client
• Auto-Sensing and On-Demand
• Custom client and server logic
• Integration with UltraLite and MobiLink
• Benefits
• Ease of developing web applications
• Always Available
• Multi-platform

10
M-Business Anywhere Architecture
11
UltraLite for M-Business Anywhere

• UltraLite local data store
• Relational database
• SQL open standard
• Fast, Efficient, Robust, Secure
• Encryption and authentication
• MobiLink
• Synchronize directly to your enterprise systems
• Encrypted stream and authentication to enterprise
• Script based logic through standard SQL
• UltraLite for M-Business Anywhere API
• HTML embeds JavaScript
• JavaScript to call API
• Execute standard SQL
• Optimized access (9.0.2)
• Performance AND all other benefits of an RDBMS

12
UltraLite Development

• UltraLite Database
• Created and opened programmatically through a
  schema file
• Interface through API classes
• Web page embedded JavaScript to select, insert,
  update, delete, synchronize
• UltraLite Schema
• UL Schema Painter to create or modify
• Schema file (.usm) copied down to device through
  channel
• Synchronization
• Initiated through user action or through channel
  sync (9.0.2)
• Connection made to MobiLink
• MobiLink scripts control data movement using
  standardized script names and events
• If a script exists, data can be moved back and
  forth.
• Separate scripts for upload and download

13
UltraLite for M-Business Anywhere Architecture
Web Content
M-Business Anywhere
M-BA Client Browser
Web Apps
Enterprise Systems
MobiLink
Data
ULPod
UltraLite
14
Tasty Baked Goods Solution Sample

• M-Business Anywhere and UltraLite POD Solution
  Sample

15
Tasty Baked Goods - Route Management
Tasty Baked Goods - Route Management

• Route Management
• Manage Bakery Delivery Route
• Route is downloaded to device
• Products that are ordered, delivered and returned
  updated in database
• Store manager signs the invoice
• Historical reports and graphical displays of data
• Two way sync

16
Tasty Baked Goods Solution Sample

• M-Business Anywhere and UltraLite POD Solution
  Sample
• DEMO

17
Getting Technical

• Client side processing accesses the UltraLite
  database
• Manipulating the Client side browser
• Synchronization
• Deployment

18
Getting Technical

• Client side processing accesses the UltraLite
  database
• Access to the UltraLite POD
• Connection to UltraLite database
• Issue SQL commands to UltraLite database
• UltraLite for M-Business Anywhere API
• Manipulating the Client side browser
• Synchronization
• Deployment

19
Access to the UltraLite POD

• M-Business Anywhere Group Channels
• HTML with embedded JavaScript
• JavaScript files access from HTML
• UltraLite schema file (USM)
• JavaScript will attempt to
• Reconnect to the UltraLite database specified
• if that fails, create new connection to the
  UltraLite database
• if the database doesnt exist, create it from the
  schema
• Once the POD object has been created
• Continues to exist
• Persist connections between different pages

20
Access to UltraLite POD

• UltraLite POD is looking for the string prefix
• "iAnywhere.UltraLite.DatabaseManager"
• dbMgr CreateObject( "iAnywhere.UltraLite.Databas
  eManager.route_mgmt" )
• var dir dbMgr.directory
• var parm dbMgr.createConnectionParms()
• parm.connectionName "route_mgmt"
• parm.databaseOnCE dir "\\rm.udb"
• parm.schemaOnCE dir "\\rm.usm"

21
ULPod in M-Business Anywhere Client
MBA Client
iAnywhere.UltraLite.DatabaseManager.route_mgmt
22
Connection to UltraLite database

• try
• conn dbMgr.reOpenConnection( "route_mgmt"
  )
• if ( conn null )
• conn dbMgr.openConnectionWithParms(
  parm )
• catch ( ex )
• if ( dbMgr.sqlCode dbMgr.SQLError.SQLE_ULTR
  ALITE_DATABASE_NOT_FOUND )
• try
• conn dbMgr.createDatabaseWithParms(
  parm )

23
Issue SQL commands to UltraLite database

• this.getProducts function( my_cust_id )
• var query SELECT P.name "
• " FROM
  customer C, sales_history SH JOIN product P ON
  PRODUCT_ID "
• " WHERE
  C.CUSTOMER_ID SH.CUSTOMER_ID "
• " AND
  C.CUSTOMER_ID ?
• var stmt this.dbConn.conn.prepareStatement(
  query,
• 
  "stmt_get_products" )
• stmt.setIntParameter(1, my_cust_id)
• var rs, prod_array new Array
• try rs stmt.executeQuery( null )
• rs.moveBeforeFirst()
• while ( rs.moveNext() )
• prod_arrayprod_array.length
  rs.getString(1)
• catch ( ex ) // handle exception
• finally stmt.close()
• return prod_array

24
UltraLite for M-Business AnywhereAPI Object
Heirarchy
25
API Object HeirarchyConnection
26
API Object HeirarchyDynamic SQL
27
API Object HeirarchyTable
28
API Object HeirarchySynchonization
29
Getting Technical

• Client side processing accesses the UltraLite
  database
• Manipulating the Client side browser
• API to access to objects on the browser
• From the browser to UltraLite and back
• Synchronization
• Deployment

30
Manipulating the Client side browser

• Client-Side DHTML
• HTML object already exists
• add HTML objects dynamically through DOM API
• Web pages do not need refreshing (re-downloading)

• Server-Side DHTML
• Create full HTML file on server
• HTML tags and data are both string
• Once sent to client, changes must be resubmitted
  by client to server and back

• JavaScript in HTML must access the browser
  objects
• DOM API used for finding and accessing objects
• JavaScript events for interacting with User
• onclick, onsubmit, onload, etc
• Methology shift from server-side

31
Manipulating DOM

• Consider the following HTML BODY
• ltheadgtltscriptgtlt/scriptgtlt/headgt
• ltbodygt
• ltp align"center"gtltbgtList All Productslt/bgtlt/pgt
• lttable border"1" cellpadding"1" cellspacing"1"
  
• width"100" id"TableList"gt
• lt/tablegt
• ltpgtlta href"main_page.htm"gtBack to main
  pagelt/agtlt/pgt
• ltpgtlta href"insert_page.htm"gtInsert new
  Productlt/agtlt/pgt
• lt/bodygt

32
Manipulating DOM

• lttable border"1" cellpadding"1" cellspacing"1"
  
• width"100" id"TableList"gt
• lt/tablegt
• var idx, objTable, eleTR, eleTD1, eleTD2
• objTable document.getElementById(TableList)
• for ( idx0 idxltarray.length I )
• eleTR objTable.insertRow(idx)
• eleTR.style.color black
• eleTD1 eleTR.insertCell(0)
• eleTD1.appendChild( document.createTextNode(id
  x) )
• eleTD2 eleTR.insertCell(1)
• eleTD2.appendChild( document.createTextNode(ar
  rayidx) )

33
Manipulating DOM

• List All Products
• Back to main page
• Insert new Products

0 Widget
1 Wonder Thing
2 Fizzy Fizzy Poppers
3 Monkey Mask
34
SELECT Example of Client-Side DHTML

• lthtmlgtltheadgtltscript src"common.js"gtlt/scriptgt
• ltscript language"JavaScript1.2"gt
• function Display()
• try
• var pstmt conn.prepareStatement( "SELECT
  FIRST cust_name FROM ULCustomer ORDER BY
  cust_id", "cust_list_ps" )
• var rs pstmt.executeQuery("cust_list_ps")
  
• rs.moveNext()
• var objTitle document.getElementById("Head
  ing_Customer")
• objTitle.appendChild(document.createTextNode
  (rs.getString(1)))
• catch ( ex ) // handle exception
• lt/scriptgt
• lt/headgtltbody onload"Display()" gt
• ltH1 ID"Heading_Customer"gtlt/H1gt
• lt/bodygtlt/htmlgt

Database and conn are handled in common.js
35
INSERT Example of Client-Side DHTML

• ltscript language"JavaScript1.2"gt
• function insertRow()
• var pstmt conn.prepareStatement( INSERT
  INTO ULCustomer (cust_name) values (?)",
  "cust_insert_ps" )
• pstmt. setStringParameter(1,
  document.insert.custname.value)
• pstmt.executeStatement("cust_insert_ps")
• lt/scriptgt
• lt/headgtltbodygtltform name"insert"gt
• ltinput type"text" name"custname"
  size"10"gtltbrgt
• ltinput type"button" value"Insert"
  onclick"insertRow()"gtltbrgt
• ltinput type"reset" value"Reset"gt
• lt/formgtlt/bodygtlt/htmlgt

36
Getting Technical

• Client side processing accesses the UltraLite
  database
• Manipulating the Client side browser
• Synchronization
• Classes from the API
• Setting up the parameters
• Coding
• Architecture
• Deployment

37
UltraLite POD Synchronization with MobiLink
Web Content
M-Business Anywhere
M Business Anywhere Client
Web Apps
Enterprise Database
MobiLink
Content
ULPod
UDB
38
API Object HeirarchySynchonization
39
Data Synchronization Configuration

• Synchronization
• MobiLink for data
• MobiLink sync configuration can be embedded in
  app
• M-Business Anywhere for application UI and logic
• Integrated in ActiveSync Hotsync, use M-Business
  Connect for wireless

• Synchronization configuration for MobiLink within
  M-Business Client

40
UltraLite Synchronization

• conn.syncParms.setStream( conn.syncParms.STREAM_TY
  PE_TCPIP )
• conn.syncParms.setUserName( document.dbsync.syncus
  rname.value )
• conn.syncParms.setVersion( document.dbsync.syncver
  .value )
• // stream parms include host and port
• conn.syncParms.setStreamParms( document.dbsync.syn
  cparm.value )
• conn.syncParms.setDownloadOnly( true )
• conn.saveSyncParms()
• window.showBusy true
• conn.synchronize()
• window.showBusy false
• if ( conn.syncResult.getUploadOK() ! true )
• alert( "Upload is not OK!" )
• else
• alert( "Synchronization finished!" )

41
Getting Technical

• Client side processing accesses the UltraLite
  database
• Manipulating the Client side browser
• Synchronization
• Deployment
• M-Business Anywhere
• Deploying Channels
• Schema file
• Deployment Options

42
Deploying UltraLite for M-Business Anywhere

• Develop a Group for all your download content
• Each downloadable object is a Channel
• USM
• DLL
• Root HTML file (will bring its children down too)

Web Content
M-Business Anywhere
USM
M-Business Anywhere Admin
ULPod9.dll
43
Application Deployment

• Deployment - distributed as M-Business channels
• Ultralite Pod runtime (500 600 KB)
• Web applications
• Schema file

44
Architecture of the Client
Win32 M-BA Client
Each Channel Group gets its own directory for
web pages
All binaries including UL Pod DLLs, USMs,
ULConnect.exe

• Create one Channel Group exclusively for
  downloading the UL binaries
• Only download either ULPOD9.DLL or
  ULPODCLIENT9.DLL
• Each Channel Group that uses UltraLite, should
  download its own USM

45
Where Does the Database Come From?

• Create a USM file using Schema Painter
• Generate a USM from from an ASA database using
  ulinit

ulinit -f ../html/common/rm.usm -n Delivery -c
"dsnremote"
46
Simplified Development Steps

1. Create schema for database using Schema Painter
2. Develop web pages that access database
3. Create Group on M-Business Anywhere Server
4. Create channels for all download content
5. Add users to the Group
6. Use the Client to download the content and test
7. Generate MobiLink synchronization scripts
8. Modify Web pages to invoke synchronization
9. Client does a Sync All for channels and test

this process can be greatly helped by using an
ASA remote to auto-generate scripts
47
UltraLite POD for M-Business Anywhere (9.0.2)

• Two Options for POD implementation
• Standalone
• Client / Engine (9.0.2)
• Only one should be deployed

48
UltraLite POD Standalone Runtime

• Accesses databases directly and quickly but can
  not be shared.
• Through ULPod9.dll
• Deployment to
• Win32, Windows CE, Palm OS devices

49
UltraLite POD - UltraLite Engine

• UltraLite client communicates with UltraLite
  engine to access databases.
• Databases can be shared by different
  applications.
• Deployment
• Win32, Windows CE
• Requirements
• ulpodclient9.dll through an M-BA Channel
• dbuleng9.exe deployed separately
• ulpodclient9.dll replaces ulpod9.dll
  functionality
• Currently only one of ulpod9.dll or
  ulpodclient9.dll should be deployed to a M-BA
  Client
• POD technology will pass iAnywhere.UltraLite.Data
  baseManager for acceptance
• Only needed if UltraLite will be access outside
  of M-BA

50
One-Button-Synch (9.0.2)

• M-Business Anywhere Channel Sync will initiate a
  MobiLink/UltraLite Synchronization
• Deploy
• ulconnect.exe
• ulconnect.usm
• Each successful synchronization (previous
  example) adds a connection entry into
  ULConnect.UDB
• Each connection entry will be synchronized
• Connection entries can be skipped
• Connection.skipMBASync true
• Only exists for Win32 and PocketPC
• PocketPC has a CAB that will install itself

51
One-Button-Synch Architecture
ULConnect
MobiLink A
MobiLink B
MobiLink C
52
UltraLite for M-Business Anywhere

• Enables mobile workers anytime, anywhere
• Always Available information
• Easy to develop
• HTML, JavaScript, SQL
• Easy to deploy
• M-Business Anywhere clients download updated/new
  applications dynamically
• Secure from device to enterprise

53
More Information

• The newsgroups include the following
• ianywhere.public.sqlanywhere.general
• ianywhere.public.sqlanywhere.linux
• ianywhere.public.sqlanywhere.mobilink
• ianywhere.public.sqlanywhere.product_futures_discu
  ssion
• ianywhere.public.sqlanywhere.replication
• ianywhere.public.sqlanywhere.ultralite
• ianywhere.public.mbusinessanywhere.general