Title: Arial 28pt'

1
SQL 928 - Building Handheld Database Applications
Using UltraLite
Paul Fast UltraLite Development
Manager paulf_at_ianywhere.com August 18, 2004
2
Agenda

• Overview of UltraLite
• Demo CustDB
• Architecture
• Creating applications
• Demo
• Summary
• Questions

3
Agenda

• Overview of UltraLite
• Demo CustDB
• Architecture
• Creating applications
• Demo
• Summary
• Questions

4
What is UltraLite?

• Database technology in SQL Anywhere Studio
• Aimed at small, mobile and embedded devices
• PDAs, smart phones, etc.
• Point-of-sale devices
• Key features
• Small footprint with high performance
• Built-in synchronization
• RDBMS features including transactions,
  referential integrity, SQL support
• Easy to use
• Available from a variety of programming languages
  and development environments
• Strong encryption
• In-process and separate-process database engines

5
Who needs a database on device?

• Access to information anywhere, anytime
• Increases productivity
• Improves customer service
• Data communication concerns
• Low bandwidth networks means slow applications
• Limited battery life for wireless transmissions
• Unreliable and costly wireless coverage
• Instead, use the network wisely
• Local data store
• Synchronize only what is needed
• Reliable data storage
• Referential integrity, transactions and
  recoverability, encryption

6
The Enterprise. Unwired.
7
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
8
Consider the Constraints

• Targeting small devices presents challenges
• Limited memory
• Slow processors
• Limited number of processes running at a time
• No convergence in hand held market
• Pocket PC and Palm OS both strategically
  important
• Historically, different chip hardware (ARM, x86,
  68K, MIPS, etc.)
• Need to weigh value of feature with cost of
  implementation
• Some common RDBMS features not available with
  UltraLite
• Stored procedures, triggers, table permissions

9
Powerful Database Functionality

• Support for dynamic SQL statements
• SELECT, INSERT (including FROM SELECT), UPDATE,
  DELETE
• DDL statements like CREATE/DROP TABLE
• Wide breadth of SQL functions
• Supports a variety of data types
• INTEGER, NUMERIC, CHAR, LONG VARCHAR, LONG
  BINARY, DATETIME, etc.
• Default values autoincrement, global
  autoincrement, current date, new UUID
• Indexes
• Foreign keys, unique keys (primary keys), unique
  and non-unique indexes
• Transaction support
• Ability to commit and rollback changes

10
Plus Built-in Synchronization

• Synchronization is done via iAnywhere's MobiLink
• Synchronize with a variety of databases
• Sybase Adaptive Server Enterprise, Adaptive
  Server Anywhere
• Oracle
• IBM DB2
• Microsoft SQL Server
• UltraLite tracks changes to remote data and
  uploads only what changed
• Downloads are automatically applied to the
  UltraLite database
• Different stream support
• HTTP(S), TCPIP, HotSync, ActiveSync

11
Programming Components

• PocketBuilder (version 1.5.2) Component
• Deploy to Pocket PC
• M-Business Anywhere (AvantGo) Component
• Browser based applications
• JavaScript
• Deploy to Pocket PC and Palm OS
• Microsoft
• ActiveX component for eMbedded Visual Basic or
  Pocket IE
• .NET component for C, VB.NET, managed C
  development
• C component with Visual Studio eMbedded
  Visual C

12
Programming Components (continued)

• AppForge Component
• MobileVB integrates with VB 6.0
• Crossfire integrates into VS.NET 2003
• Both allow deployment to Pocket PC and Palm OS
• Java
• Integration with Borland JBuilder
• "Native UltraLite for Java" component uses JNI
• Palm Native applications
• Codewarrior 8 and 9 for C development

13
UltraLite vs. Adaptive Server Anywhere

• Both are available on some platforms (CE, XP)
• ASA has more SQL and RDBMS functionality
• Stored procedures, triggers, permissions
• More SQL support
• UltraLite has more limitations than ASA
• Row count, number of users, row size, table count
• ASA does not have built-in (in-process)
  synchronization
• However, it is available via DBMLSYNC
• UltraLite is smaller than ASA
• UltraLite can run in-process
• Performance

14
Agenda

• Overview of UltraLite
• Demo CustDB
• Architecture
• Creating applications
• Demo
• Summary
• Questions

15
Agenda

• Overview of UltraLite
• Demo CustDB
• Architecture
• Creating applications
• Demo
• Summary
• Questions

16
A Brief History of UltraLite

• Required an Adaptive Server Anywhere reference
  database
• Generated application code
• Created very small applications
• Custom (application-specific) APIs
• Custom (application-specific) database
• No dynamic SQL
• All queries had to be defined at development time
• C, C and Java the only supported languages
• Unintuitive development model

17
The Evolution of UltraLite

• Devices became more powerful
• More memory available for larger applications
• Processor speed improved
• Made a query processor possible on handheld
  devices
• Popular programming environments became available
  for handhelds
• For example, Visual Basic and PocketBuilder
• Customers wanted dynamic SQL and independent
  databases
• SQL Anywhere 8.0.2 introduced independent
  databases and a non-generated API
• SQL Anywhere 9 introduced a query processor and
  dynamic SQL support

18
Database Application Architectures
Traditional database application architecture
Presentation layer
Application logic (SQL statements)
Database Management system
Database
Database, file or device
Client
Server
UltraLite application architecture
Presentation layer
Application logic (SQL statements)
UltraLite runtime
Database (schema can be changed)
UltraLite application
Persistent memory or file
19
Agenda

• Overview of UltraLite
• Demo CustDB
• Architecture
• Creating applications
• Demo
• Summary
• Questions

20
Development Steps

• Design the schema of the remote database
• Usually similar to, or a subset of, the
  consolidated database
• Write code for the application using the database
• A variety of languages are available (consider
  the target platform when choosing a language)
• Construct SQL queries as part of your application
• Create the synchronization scripts
• MobiLink can create default scripts which are
  good for getting started

21
Utilities for Database Schema Modification

• ULINIT
• Command line tool that extracts the schema from
  an ASA database
• UltraLite Schema Painter
• ULXML loads/unloads schemas to/from XML

22
UltraLite Application Programming Interface

• UltraLite accessible from several programming
  languages
• C, ODBC, Java, Visual Basic, VB.NET, C,
  JavaScript, PocketBuilder
• Current API is Proprietary (but easy to learn)
• Initial table-only access to data lacked a
  standard
• Dynamic SQL added later
• Visual Basic, C and Java all have different de
  facto standard APIs
• Moving to industry standards where possible
• Already have a subset of ODBC
• ADO.NET in development
• JDBC for Java access
• Embedded SQL

23
UltraLite Component Class Hierarchy
DatabaseManager
ConnectionParms
Connection
DatabaseSchema
PublicationSchema
Table
Column
ColumnSchema
TableSchema
IndexSchema
PreparedStatement
ResultSetSchema
ResultSet
SyncResult
SyncParms
24
Connection Classes
DatabaseManager
ConnectionParms
Connection
DatabaseSchema
PublicationSchema
Table
Column
ColumnSchema
TableSchema
IndexSchema
PreparedStatement
ResultSetSchema
ResultSet
SyncResult
SyncParms
25
Connections and Databases

• DatabaseManager
• Root object of the tree
• Should only be instantiated once per application
• Handles opening connections to databases
• Handles creating and dropping databases
• Connection
• Represents a connection to a database
• ConnectionParms
• Convenience class for connection parameters
• Handles the creation of a connection string

26
Know Your UltraLite Runtime

• In-process version ("standalone")
• Initial implementation of UltraLite
• Library or component (runtime) included in an
  application
• Offers connection to one or more databases
• Separate-process version ("engine")
• Developed more recently
• Allows multiple connections to access the same
  database
• Applications must connect from same device
  running the engine
• Currently only available with C, Native
  UltraLite for Java, UL.NET
• Thread safe but not multi-threaded
• Some UltraLite-supported platforms do not support
  threading

27
Data Access Classes
DatabaseManager
ConnectionParms
Connection
DatabaseSchema
PublicationSchema
Table
Column
ColumnSchema
TableSchema
IndexSchema
PreparedStatement
ResultSetSchema
ResultSet
SyncResult
SyncParms
28
Using Dynamic SQL

• PreparedStatement
• A SQL statement that UltraLite has parsed
• SELECT, INSERT (including FROM SELECT), UPDATE,
  DELETE
• COMMIT, ROLLBACK
• CREATE/DROP TABLE, CREATE/DROP INDEX
• Can contain placeholders/host variables
• PreparedStatement has a query plan
• Query plan is available to diagnose possible
  performance problems
• ResultSet
• Represents a cursor over a result set
• Methods to move forward or backward over data and
  access columns

29
UltraLite Data Types

• CHAR
• VARCHAR
• UNSIGNED BIGINT
• DECIMAL
• NUMERIC
• DOUBLE
• FLOAT
• UNSIGNED INTEGER
• REAL
• UNSIGNED SMALLINT
• UNSIGNED TINYINT

DATE TIME DATETIME TIMESTAMP BINARY VARBINARY LONG
VARCHAR LONG BINARY
30
SELECT Statement
SELECT  DISTINCT   FIRST  TOP n  select-list
 FROM table-expression   WHERE search-conditi
on   GROUP BY group-by-expression,...group-by-e
xpression   HAVING search-condition   ORDER 
BY  order-by-expression,...order-by-expression 
table-expression table-name AS
correlation-name table-expression
join-operator table-expression ON
join-condition ,... ( table-expression, ...
) ( select-statement ) AS
derived-table-name ( column-name, ...
column-name )
join-operator , (ON condition not allowed)
CROSS JOIN (ON condition not allowed) INNER
JOIN JOIN (ON phrase required) LEFT OUTER JOIN
order-by-expression integer expression
ASC DESC
31
UltraLite Functions

• ABS
• ACOS
• ARGN
• ASCII
• ASIN
• ATAN
• ATAN2
• AVG
• BYTE_LENGTH
• BYTE_SUBSTR
• CAST
• CEILING
• CHAR
• CHARINDEX
• CHAR_LENGTH
• COALESCE
• CONVERT
• COS
• COT

DATEDIFF DATEFORMAT DATENAME DATEPART DATETIME DAY
DAYNAME DAYS DEGREES DIFFERENCE DOW EXP FLOOR GET
DATE GREATER HEXTOINT HOUR HOURS IFNULL INSERTSTR
INTTOHEX ISDATE ISNULL
LCASE LEFT LENGTH LESSER LIST LOCATE LOG LOG10 LOW
ER LTRIM MAX MIN MINUTE MINUTES MOD MONTH MONTHNAM
E MONTHS NEWID NOW NULLIF PATINDEX PI
POWER QUARTER RADIANS REMAINDER REPEAT REPLACE REP
LICATE RIGHT ROUND RTRIM SECOND SECONDS SIGN SIMIL
AR SIN SOUNDEX SPACE SQRT STR STRING STRTOUUID STU
FF SUBSTRING
SUM TAN TODAY TRIM TRUNCATE TRUNCNUM UCASE UPPER U
UIDTOSTR WEEKS YEAR YEARS YMD
32
Table API

• Table class
• Similar to ResultSet
• SELECT FROM table ORDER BY indexed column(s)
• Knowledge of SQL is unnecessary to use this class
• The cursor for the table is updatable
• The current row can be updated or deleted for the
  open table
• Can be more efficient than ResultSet but it
  depends on the usage

33
Using the Table API

• Column class (if the API has it) represents a
  column of data in the table
• Additional methods to do searching

table.Open "name_index" table.FindBegin table.Colu
mn("last_name").StringValue "Smith" If
table.FindFirst(1) Then Do ' Found a
record for Smith Loop While table.FindNext(1) E
lse ' Didn't find a record for Smith End If
34
Schema Classes
DatabaseManager
ConnectionParms
Connection
DatabaseSchema
PublicationSchema
Table
Column
ColumnSchema
TableSchema
IndexSchema
PreparedStatement
ResultSetSchema
ResultSet
SyncResult
SyncParms
35
Accessing Schema Information

• DatabaseSchema
• Information about the database
• Timestamp format, date format, date order, etc.
• Number of tables and their names
• ResultSetSchema, TableSchema, ColumnSchema
• Number of columns in the result set or table, the
  type of data, name of a column, etc.
• Useful for writing generic code
• IndexSchema
• Information like number of columns in the index,
  sort order of each, etc.
• PublicationSchema
• Name, mask (identifier), whether a table is in a
  publication

36
Synchronization Classes
DatabaseManager
ConnectionParms
Connection
DatabaseSchema
PublicationSchema
Table
Column
ColumnSchema
TableSchema
IndexSchema
PreparedStatement
ResultSetSchema
ResultSet
SyncResult
SyncParms
37
Configuring Synchronization

• SyncParms
• Contains properties to configure synchronization
  with MobiLink
• Stream type (TCPIP, HTTP(S), HotSync, ActiveSync)
• Location of the MobiLink server
• Authentication information
• Optionally, a subset of tables to synchronize
• SyncResult
• Feedback on the most recent synchronization
• Error codes and status
• Connection
• Has a Synchronize() method
• Has event handlers to provide progress on
  synchronization
• Template sync progress forms are available

38
UltraLite Synchronization Architecture
Device/Client Side
Server Side
Scripts determine how to apply changes and what
to send down
Application calls Synchronize
MobiLink Synchronization Server
UltraLite determines what needs to be sent and
how to handle received changes
1. Upload
2. Download
3. Download Ack (optional)
Consolidated database
UltraLite database
39
How Synchronization Works

• Upload
• UltraLite runtime keeps track of all changes
  since previous synchronization
• All changes are sent in a single upload stream
• MobiLink applies changes in a single transaction
• Download
• Scripts in the consolidated database determine
  changes that need to be downloaded
• All changes are sent in a single download stream
• Download Acknowledgement (optional)

40
Desktop Utilities

• ULCONV multi-purpose conversion utility
• Unloads and loads UltraLite databases to and from
  XML format
• Can be used to synchronize an UltraLite database
• Command line tool
• Runs only on the desktop
• DBCOND
• Installs a Palm conduit
• Required if synchronizing data via HotSync

41
Agenda

• Overview of UltraLite
• Demo CustDB
• Architecture
• Creating applications
• Demo
• Summary
• Questions

42
Summary of UltraLite

• An RDBMS made for constrained environments
• Offers many features available in traditional SQL
  database systems
• Designed with synchronization via MobiLink in
  mind
• Accessible from a variety of programming
  languages and development environments
• Mission to improve, maintain and add new
  features while remaining small and fast and easy
  to use

43
Resources

• CustDB
• The ubiquitous UltraLite sample application
• CodeXchange
• http//ianywhere.codexchange.sybase.com
• news//forums.sybase.com
• sybase.public.sqlanywhere.ultralite
• sybase.public.sqlanywhere.mobilink
• sybase.public.sqlanywhere.product_futures_discussi
  on
• iAnywhere developer website
• http//www.ianywhere.com/developer

44
iAnywhere at TechWave2004

• Ask the iAnywhere Experts on the Technology
  Boardwalk (exhibit hall)
• 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!
• TechWave ToGo Channel
• TechWave To Go, an AvantGo channel providing
  up-to-date information about TechWave classes,
  events, maps and more also, keep up to date
  with the TechWave Newsletter now available via
  your handheld device!
• www.ianywhere.com/techwavetogo
• Mobile and Wireless Email using Pylon Anywhere
• iAnywhere is providing access to your corporate
  email at TechWave using Pylon Anywhere. You can
  keep up-to-date with your latest email, calendar,
  contacts, and tasks from your PDA or any
  Web-client! Visit the iAnywhere demo station in
  the Sybase booth or our Ask the Experts area in
  the Technology Boardwalk (Exhibit Hall) for
  details on how you can evaluate Pylon Anywhere
  yourself!

45
iAnywhere at TechWave2004

• Wi-Fi Hotspots brought to you by Intel
• You can enjoy wireless internet access via Wi-Fi
  hotspots provided by Intel. Using either a
  laptop or PDA that is Wi-Fi 802.11b
  wirelessly-enabled, visitors can access personal
  email, the internet and TechWave ToGo
• 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/

46
Questions?