Developing Cross-Platform Applications for Handhelds Using AppForge and UltraLite Technology

1. M406Developing Cross-Platform Applications for Handhelds • Paul Fast • Software Engineer • iAnywhere Solutions • Paul.Fast@sybase.com • Bob Holt • Product Manager • AppForge, Inc. • rhh@appforge.com

2. Agenda • Why use UltraLite? • What is AppForge MobileVB? • UltraLite For AppForge • Object descriptions and hierarchy • How to carry out tasks with UltraLite for AppForge • Future • Summary

3. Why are businesses “going mobile?” “Anywhere, anytime” access to information translates to: • Increased productivity and flexibility • Efficient - eliminate redundancies of data input • More accurate - less prone to error • Improved customer service • Convenience of user experience • Gain competitive advantage • Timely information allows companies to react to market conditions

4. What are some of the challenges? Device resource limitations • No hard disk, limited memory, slow processors, limited power, wireless connection Data communications concerns • Low bandwidth networks means slow applications • Limited battery life for wireless transmissions • Unreliable and costly wireless coverage

5. Overcoming the challenges Customers are using smart clients (persistent data store) Product characteristics required: • Resource efficiency - minimal footprint • Reliable storage of data • Data collected is often mission critical • Consider impact if data is lost • Seamless data synchronization • Heterogeneous data environments • New systems must complement existing systems • Good performance • Cross-platform

6. Limitations in Other Mobile Data Storage Options Custom-coded solutions • Build vs. buy: very resource intensive • Increased risk: proprietary • Increased maintenance costs: porting and testing ‘Shrunk-down’ enterprise database • Architecture not geared for mobile and wireless devices

7. Why use UltraLite? Relational database • Maintains extremely small footprint Reliable data storage • Transactional – support for commit/rollback • Referential integrity Excellent performance • Indexing support Cross-platform • Support for PalmOS, PocketPC and other handheld platforms

8. Why use UltraLite? • Provides flexible and robust bi-directional synchronization • Integration with Enterprise • Support Sybase, Oracle, IBM DB2, Microsoft SQL Server • Secure • Built-in authentication • 128-bit encryption of communication stream • Support for sub-setting and partitioning of data • Scalable to thousands of remote users • Conflict detection and resolution • Robust error handling

9. UltraLite APIs Today UltraLite for C/C++ • Embedded SQL • UltraLite C++ API UltraLite for Java • JDBC But what if your organization makes use of other languages and tools?

10. AppForge MobileVB

11. Topics • What is MobileVB? • Demonstration • Creating Cross-platform mobile applications • An overview of the Mobile VB architecture

12. AppForge Mobile VB • Platform for building and deploying mobile applications • Extends standard Microsoft Visual Basic product • Developer advantages • Rich IDE for application creation and debugging • Familiar language and object model • Common source to variety of mobile platforms

13. AppForge Leverages Existing Developers • AppForge enables 8 million existing Visual Basic and C++ developers to use the standard enterprise tools that they already know to create rich mobile and wireless applications. • By using applications written in AppForge, enterprises are not locked in to any one hardware device. Key Programming Languages for Professional Developers AppForge enables 75% of existing developers to easily write sophisticated mobile applications

14. Cross Platform Mobile Applications

15. Demonstration AppForge MobileVB 3.0

16. Fundamental Components Visual Basic “Add-in” Visual Basic Compiler Supporting Services Graphics Win32 Design/Debug Windowing Device Specific “Booster” Mem. Mgmt. . . . Virtual Machine

17. The AppForge Piedmont Framework

18. Cross-platform Data-centric Applications Presentation Palm OS Nokia iPAQ Device/UI Abstraction ISuperGrid Application Logic Appl. Specific Rules Backing Store Data Access UltraLite Synchronization

19. Summary • MobileVB targets largest base of trained developers • Leverages familiar tools, but brings new opportunities • Rapid application development -- Even more rapid application porting • Through Piedmont framework, iAnywhere seamlessly adds true database and synchronization support to product

20. UltraLite Component Suite The evolution of UltraLite • Existing UltraLite with new interface • Does not replace, but complements existing UltraLite Key differences in this offering • Database schema not compiled into the application • Database schema can change after initial deployment • A well-defined API provides data on a per-table basis

21. UltraLite and AppForge UltraLite integrates with AppForge • Allows development of UltraLite applications in Microsoft Visual Basic • Gives AppForge programmers a cross platform relational database with built-in synchronization • Requires the AppForge Booster+ VM

22. Databases and Schemas • Schema is just the definition of a database • For NT/Pocket PC stored as a .usm file • For Palm stored as a Palm database (PDB) • Database is the definition (schema) and data • For NT/Pocket PC stored as a .udb file • For Palm stored as a Palm database or file on an expansion card • Identified by Palm creator ID

23. Databases and Schemas • Connecting to a database that does not exist creates it • Newly created database has no schema • Schema is assigned to a connected database • Schema file or PDB is typically deployed with an application • Schema can be applied to a database that already has one • Means schema upgrades are possible • No maintained link between schema and database

24. UltraLite Schema Creation • Based on an Adaptive Server Anywhere database; or • Created via a GUI tool

25. Object Hierarchy ULDatabaseManager ULConnection ULDatabaseSchema ULPublicationSchema ULSyncInfo ULTable ULTableSchema ULIndexSchema ULColumn ULColumnSchema

26. Managing Connections • ULDatabaseManager object returns database connections • ULDatabaseManager.OpenConnection takes connection string: • “DBF=\CustDB\mydb.udb;UID=DBA;PWD=SQL” • “palm_db=Syb1;UID=DBA;PWD=SQL” • Returns a ULConnection object

27. Connections and Databases ULConnection represents a connection to one database • Controls transactions (Commit, Rollback, AutoCommit) • Used to retrieve tables and publications • Used to synchronize ULDatabaseSchema • Contains information about the database • Can be “upgraded”

28. Connecting to an UltraLite Database • Create your schema file • Write something like: Dim DbMgr as New ULDatabaseManager Dim Conn as ULConnection … #If APPFORGE Then connparms = “palm_db=CRID” schema = “schema” #Else connparms = "DBF=" & App.Path & “\db.udb“ schema = App.Path & “\schema.usm“ #End If Set Conn = DbMgr.OpenConnection( connparms ) If Conn.DatabaseNew Then Conn.schema.UpgradeFromFile schema, “” End If • Include the schema file as an AppForge dependency

29. Tables and Columns ULTable represents a cursor over all the rows of a table • Contains methods to position at rows in the table • Contains methods to insert, update or delete rows • On Palm OS, tables can be left open on application shutdown, and reopened on startup ULColumn used to retrieve or set a value from a column in a table • Return value type must be specifically requested • BLOBs are supported

30. Table Schema ULTableSchema represents the schema of a table • Returns column count, index count, name, etc. ULColumnSchema represents the schema of a column in the table • Indicates data type, size, default value, nullability, etc. ULIndexSchema represents the schema of an index • Can be unique or not • Index could be a foreign key

31. Fetching Data Dim id As Integer Dim salary As Single Dim table As ULTable Dim col_id As ULColumn Dim col_salary As ULColumn Set table = Conn.GetTable("salary") table.Open "" Set col_id = table.GetColumn("id") Set col_salary = table.GetColumn("salary") While table.MoveNext id = col_id.IntegerValue salary = col_salary.RealValue ' Process values Wend t.Close • Tip: Use variable for ULColumn if fetching in a loop • Tip: Use return value of MoveNext instead of EOF

32. Finding Values • Find all rows with “last_name” set to “Smith” Dim table As ULTable Set table = Conn.GetTable("customer") table.OpenByIndex "lname_idx", "" table.FindBegin table.GetColumn("last_name").StringValue = "Smith" If table.FindFirst Then Do ' Process the row Loop While table.FindNext Else ' row not found End If table.Close

33. Fetching BLOB data • For columns declared “binary” or “long binary” Dim table As ULTable Dim col As ULColumn Dim data(1 To 1024) As Byte Dim data_fit As Boolean Dim size As Long Set table = Conn.GetTable("image") table.Open "" size = 1024 Set col = table.GetColumn(“img_data") data_fit = col.GetBytes(VarPtr(data(1)), size) If data_fit Then ' No truncation. Size of data stored in size Else ' data truncated at 1024 End If table.Close

34. Inserting a row Dim t As ULTable Dim the_date As Date Set t = Conn.GetTable("employee") t.Open "" t.InsertBegin t.GetColumn("lname").StringValue = "Doe" t.GetColumn("start_date").DatetimeValue = Now t.Insert t.Close Conn.Commit • Columns with defaults do not need to be set • Omit the Commit if AutoCommit is True

35. Updating a row Dim table As ULTable Set table = Conn.GetTable("employee") table.OpenByIndex "salary_idx", "" table.MoveLast table.UpdateBegin table.GetColumn("salary").LongValue = 75000 table.GetColumn("manager").SetNull table.GetColumn("vacation").SetToDefault table.Update Conn.Commit table.Close • Commit is unnecessary if AutoCommit is True

36. Publications A Publication is a collection of tables used for synchronization ULPublicationSchema represents a publication in the database • Each publication has a unique identifier (mask) • Several publications can be OR’d together to produce a unique mask • Use for priority synchronization

37. Synchronizing UltraLite Databases ULSyncInfo • Writable properties control synchronization • Read-only properties give status of last synchronize • Works with MobiLink Synchronization Server • Synchronizes with most ODBC-compliant databases • Sybase Adaptive Server Anywhere & Enterprise • Oracle • Microsoft SQL Server • IBM DB2

38. Synchronizing • Setup the SyncInfo structure once Dim HPPub As ULPublicationSchema Set HPPub = Conn.Schema. _ GetPublicationSchema("HighPriorityTables") With Conn.SyncInfo .UserName = "jdoe" .Password = "banana" .Stream = ulSocket .SendColumnNames = True .Version = "ul_default“ .PublicationMask = HPPub.Mask End With • Call Synchronize on the connection (unless Palm HotSync is used) Conn.Synchronize

39. Handling Errors • Use the Visual Basic Error Object • Err.Number contains the SQLCODE On Error Resume Next Set table = Conn.GetTable("BadTable") If Err.Number < 0 Then ' Handle the error End If • Optionally On Error GoTo label • Error handling will be enhanced to include description

40. DEMO

41. Development Tips • Include the control in VB’s References dialog • Make a dependency on the schema file • For Pocket PC • If using AppForge version earlier than 3.0, manually copy the file ultralite\tools\appforge\ce\arm\ulingot8.dll to your device in \Program Files\AppForge

42. Tips for Palm OS • Do not close connection if you want state saved • Fill in unique names for “persistent name” parameter of ULTable.Open and ULTable.OpenByIndex • To synchronize with HotSync • Register the conduit with dbcond8c.exe • Configure SyncInfo • Set Stream to ulPalmConduit • No need to call ULConnection.Synchronize

43. Future • Support for Dynamic SQL • Default Synchronization dialog • One application with connections to multiple databases • Ability to modify schema through the API • Support for Symbian OS

44. Summary Easy and quick development with AppForge MobileVB A robust mobile data store with enterprise synchronization for handheld developers Integration of two award-winning products • AppForge – Best Application Development Tool • iAnywhere Solutions – Best Mobile Database www.appforge.com www.ianywhere.com/ultralitebeta