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_at_sybase.com
2
Outline

• UltraLite and MobiLink overview
• Introduction to UltraLite, Java Edition

3
What is UltraLite?

• 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.

4
UltraLite Small-Footprint Databases

• A minimal database is about 50K in size using the
  C version of UltraLite, about 200-300K with Java.

5
UltraLite Customized Databases

• 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.

6
UltraLite Embeddable Databases

• Database is part of the application, no separate
  server to run.
• No communication overhead.
• Database cant be shared with another application.

7
UltraLite Fully-relational SQL Databases

• 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.

8
UltraLite Bi-directional Data Synchronization

• 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.

9
Why Use UltraLite?

• 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.

10
UltraLite, Java Edition

• 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.

11
Why a Java Version?

• Some customers working exclusively in Java.
• Portability across different mobile devices.
• Really just another platform for UltraLite.

12
How it Works

• 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.

13
Reference vs. Consolidated Database

• Reference database is an ASA database,
  consolidated database is any ODBC-compliant
  database.
• Reference database only used during development.
  Typically runs on developers own machine.
• Consolidated database only used during
  deployment. Typically an existing enterprise
  database.

14
Building the Reference Database

• 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.

15
Defining SQL Statements

• 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' )

16
SQL Statement Syntax

• 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 ?

17
Generate the Database Engine

• 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.

18
Invoking ulgen

• ulgen c connparms t java options projectname
  outputfile
• For example
• ulgen c "dsnUltraLite 7.0 Sampleuiddbapwdsql
  " 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.

19
Java-specific Options to ulgen

• -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.

20
Adding Synchronization Scripts

• 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.

21
The UltraLite JDBC Driver

• 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.

22
Connecting to an UltraLite Database

• Using normal JDBC syntax
• Class.forName(
• "com.sybase.asa.ultralite.jdbc.JdbcDriver" )
• Connection conn DriverManager.getConnection(
• "jdbcultraliteULDemo" )
• By directly using the generated class
• ULDemo db new ULDemo( null )
• Connection conn db.getConnection()

23
Connections and Transactions

• 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.

24
Executing Queries

• 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()

25
Executing Updates/Inserts/Deletes

• 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()

26
SQL Statement Strings

• 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.

27
Data Persistence

• 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.

28
Data Synchronization

• 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.

29
Synchronization Example

• import com.sybase.asa.ultralite.support.
• UlSynchOptions opts new UlSynchOptions()
• opts.setUserName( "Eric" )
• opts.setStream( new UlSocketStream() )
• opts.setStreamParms( "hostlocalhost" )
• conn.synchronize( opts )

30
Synchronization Observer

• 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.

31
Synchronization Progress Viewer

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

32
CustDB Sample

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

33
Summary

• 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!