SQL 928 - Building Handheld Database Applications Using UltraLite

1. SQL 928 - Building Handheld Database Applications Using UltraLite Paul Fast UltraLite Development Manager paulf@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 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

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 | TOPn ] select-list [ FROMtable-expression ] [ WHEREsearch-condition ] [ GROUPBYgroup-by-expression,...group-by-expression ] [ HAVINGsearch-condition ] [ ORDERBYorder-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 COUNT DATALENGTH DATE DATEADD DATEDIFF DATEFORMAT DATENAME DATEPART DATETIME DAY DAYNAME DAYS DEGREES DIFFERENCE DOW EXP FLOOR GETDATE GREATER HEXTOINT HOUR HOURS IFNULL INSERTSTR INTTOHEX ISDATE ISNULL LCASE LEFT LENGTH LESSER LIST LOCATE LOG LOG10 LOWER LTRIM MAX MIN MINUTE MINUTES MOD MONTH MONTHNAME MONTHS NEWID NOW NULLIF PATINDEX PI POWER QUARTER RADIANS REMAINDER REPEAT REPLACE REPLICATE RIGHT ROUND RTRIM SECOND SECONDS SIGN SIMILAR SIN SOUNDEX SPACE SQRT STR STRING STRTOUUID STUFF SUBSTRING SUM TAN TODAY TRIM TRUNCATE TRUNCNUM UCASE UPPER UUIDTOSTR 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.Column("last_name").StringValue = "Smith" If table.FindFirst(1) Then Do ' Found a record for Smith Loop While table.FindNext(1) Else ' 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_discussion • 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?