MC411 UltraLite, Java Edition – Moving Data Back to the Client

1. MC411UltraLite, Java Edition – Moving Data Back to the Client • Eric Giguère • Software Engineer • iAnywhere Solutions • giguere@sybase.com

2. UltraLite and MobiLink overview Introduction to UltraLite, Java Edition Outline

3. UltraLite technology generates small-footprint, customized, embeddable, fully-relational SQL databases with bi-directional data replication. Included with SQL Anywhere Studio. C, C++ and Java versions. What is UltraLite?

4. A minimal database is about 50K in size using the C version of UltraLite, about 200-300K with Java. UltraLite: Small-Footprint Databases

5. Bottom-up approach to database generation. Start with a set of tables and a set of SQL statements. UltraLite analyzer generates a custom database engine based only on the tables and statements used by the application. UltraLite: Customized Databases

6. Database is part of the application, no separate server to run. No communication overhead. Database can’t be shared with another application. UltraLite: Embeddable Databases

7. True relational database: foreign keys and referential integrity. Full SQL capabilities: transactions, multi-table joins, indices, runtime functions. Limitations: no runtime schema changes, no metadata, SQL statements known at development time, no stored procedures or triggers. UltraLite: Fully-relational SQL Databases

8. Data synchronization with MobiLink Works with any ODBC-compliant database. Conflict resolution via scripts running in database. Optimized data exchange protocol to reduce network bandwidth. Can optionally use HTTP to work across firewalls. If no sync required, can use UltraLite simply as an offline data cache with all the advantages of a relational database. UltraLite: Bi-directional Data Synchronization

9. Lets you deploy database-enabled applications to a variety of mobile platforms, including Palm, Windows CE, DOS and EPOC, where memory and/or processing power are limited. Handles data synchronization for you, letting you concentrate on the application instead of the data exchange. Why Use UltraLite?

10. A port of the C version. Written completely in Java, no native code. Supports Java 1.1 and Java 2. Same features as the C version, but uses JDBC instead of embedded SQL. Internal test suite used to ensure that both versions of UltraLite work correctly and in the same fashion. UltraLite, Java Edition

11. Some customers working exclusively in Java. Portability across different mobile devices. Really just another platform for UltraLite. Why a Java Version?

12. Outside the application: Create a reference database in ASA. Define required SQL statements. Generate UltraLite database engine. Add synchronization scripts to consolidated database. Inside the application: Use UltraLite JDBC driver to access generated database engine. How it Works

13. Reference database is an ASA database, consolidated database is any ODBC-compliant database. Reference database only used during development. Typically runs on developer’s own machine. Consolidated database only used during deployment. Typically an existing enterprise database. Reference vs. Consolidated Database

14. Reference database models the required parts of the consolidated database. Tables in reference database are usually a subset of the tables in the consolidated database. Can contain representative data to help the generation process make better decisions. Normal Java-enabled ASA database. Building the Reference Database

15. Add to reference database using Sybase Central or stored procedure. Statements grouped by project name. Statements assigned a descriptive name. Statements shareable with C++ version. ISQL Example: call ul_add_statement( 'MyProject', 'GetNames', 'select emp_lname, emp_fname from employee' ) Defining SQL Statements

16. Any SQL involving Data Manipulation Language (DML) commands (SELECT, INSERT, UPDATE, DELETE). Commits and rollbacks done using JDBC API. Use JDBC syntax ('?') for placeholders, as in: select birth_date from employee where emp_id = ? SQL Statement Syntax

17. Run ulgen tool to generate a set of Java class files to include with your application. ulgen runs the UltraLite analyzer on the reference database. The analyzer passes each SQL statement in a project to the ASA engine to obtain its execution plan and generates a custom database based on this information. Generate the Database Engine

18. ulgen –c connparms –t java [options] projectname outputfile For example: ulgen –c "dsn=UltraLite 7.0 Sample;uid=dba;pwd=sql" –t java –a –s IStatements CustDemo ULDemo.java This generates a ULDemo.java source file. The name of the class determines the name of the database. Invoking ulgen

19. -a Generate uppercase SQL string names. -i Generate inner classes. -p packagename Package to use for generated classes. -s filename Generate SQL strings in a separate interface. Java-specific Options to ulgen

20. Use Sybase Central wizards. Scripts stored in consolidated database. Written in SQL dialect of the database. Invoked by MobiLink server. Minimally need scripts to select data for download to device and to update/insert rows uploaded from device. Fairly complex, discussed elsewhere. Adding Synchronization Scripts

21. Supports subset of JDBC 1.2, with a few extensions from JDBC 2.0 to support scrollable result sets. No metadata support since UltraLite databases have no system tables. Exception: minimal implementation of ResultSetMetaData. Driver only supports features exposed by UltraLite and the generated database. The UltraLite JDBC Driver

22. Using normal JDBC syntax: Class.forName( "com.sybase.asa.ultralite.jdbc.JdbcDriver" ); Connection conn = DriverManager.getConnection( "jdbc:ultralite:ULDemo" ); By directly using the generated class: ULDemo db = new ULDemo( null ); Connection conn = db.getConnection(); Connecting to an UltraLite Database

23. Multiple connections allowed. No nested transactions: one transaction per connection. Not thread-safe, so be sure to serialize all access to a database if accessed from two or more threads. Application can load and use more than one UltraLite database if convenient. Connections and Transactions

24. Create a PreparedStatement object and call executeQuery: String sel = "select emp_id from employee"; PreparedStatement stmt; stmt = conn.prepareStatement( sel ); ResultSet rs = stmt.executeQuery(); while( rs.next() ){ System.out.println( rs.getString( 1 ) ); } rs.close(); Executing Queries

25. Call executeUpdate instead: String ins = "insert into employee (emp_id,emp_fname,emp_lname) values(?,?,?)"; PreparedStatement stmt; stmt = conn.prepareStatement( ins ); stmt.setInt( 1, 1000 ); stmt.setString( 2, "Harry" ); stmt.setString( 3, "Houdini" ); int count = stmt.executeUpdate(); Executing Updates/Inserts/Deletes

26. Strings passed to the JDBC driver must exactly match those in the reference database. ulgen defines string constants (public static final fields) for you, either in the generated class or in a separate interface. Names of constants defined when SQL statements added to the project. SQL Statement Strings

27. Default is to use in-memory database: when application terminates the database is lost. Can persist to a local file if not running as an applet. (Connection option.) Can initialize in-memory database from a file, by synchronizing, or by manually INSERTing data when application starts. Data Persistence

28. Call the synchronize method to synchronize local UltraLite database with the consolidated database. MobiLink server must be running on the remote host. Communication occurs over TCP/IP. Can be tunneled through HTTP if necessary. Data Synchronization

29. import com.sybase.asa.ultralite.support.*; UlSynchOptions opts = new UlSynchOptions(); opts.setUserName( "Eric" ); opts.setStream( new UlSocketStream() ); opts.setStreamParms( "host=localhost" ); conn.synchronize( opts ); Synchronization Example

30. New to 7.0.1, capability to be notified of progress as synchronization occurs. Simply implement the UlSynchObserver interface and register the object when setting the synchronization options. Observer is called at start and end of synchronization and at every major step in between. Synchronization Observer

31. An implementation of UlSynchObserver that displays a progress bar and allows the user to cancel the synchronization. AWT and Swing versions. Synchronization Progress Viewer

32. A complete example written in Java. Demonstrates all the major features of UltraLite, including joins and synchronization. Can run as application or applet. CustDB Sample

33. UltraLite packs all the power of a relational database in a small footprint solution. Future work possible in a number of areas: Java 2 Micro Edition, JDBC 2.x, better tool support. Let us know your requirements! Summary