MBS902: Using Dynamic SQL With the UltraLite Component Suite

1
MBS902 Using Dynamic SQL With the UltraLite
Component Suite
Alex ReifTechnical Product Manager, iAnywhere
Solutionsalex.reif_at_ianywhere.com August 7, 2003
2
Agenda

• Introduction to UltraLite
• UltraLite Component Development
• Dynamic SQL
• Demos and Samples

3
Demo

• UltraLite application
• Dynamic SQL
• .NET Compact Framework

4
Agenda

• Introduction to UltraLite
• UltraLite Component Development
• Dynamic SQL
• Samples

5
What is UltraLite?

• Deployment option for Adaptive Server Anywhere
• High-performance mobile data store
• Data synchronization with the enterprise
• Aimed at small, mobile and embedded devices
• Personal organizers, smart phones, etc.
• Point-of-sale devices
• Typical constraints
• No hard disk
• Limited memory
• Slow processors
• Limited power
• Wireless connection

6
Goals of UltraLite

• Provide advantages of an enterprise database
• Transaction processing, data types (including
  numeric and date/time), referential integrity,
  indexing, strong encryption, Dynamic SQL
• Provide synchronization to industry standard
  enterprise databases (robust and secure)
• Maintain extremely small footprint
• Maintain hand-coded speed
• Reduce the requirement for platform specific
  knowledge

7
UltraLite Platform Support

• Development
• UL Components Windows NT/2000/XP only
• Static interfaces also support Windows 98 SE
• For Palm CodeWarrior 8 and 9 only
• Note support for PRC Tools (Palm), MobileBuilder
  removed in 9.0
• Deployment
• Palm (3.x or higher, min 4 MB), Windows CE (3.0
  or higher), Windows XP
• Static Java now requires JDK 1.1.8 or higher (was
  1.1.4)
• Note removed VxWorks in 9.0
• Synchronization
• TCP/IP, HTTP(S), HotSync, ActiveSync
• Note removed ScoutSync in 9.0

8
UltraLite Two Development Models

• Static interfaces (predefined SQL)
• UltraLite Components (Dynamic SQL)

9
UltraLite Static Interfaces

• Classic UltraLite
• Embedded SQL
• Static C API
• Static Java API (JDBC, 100 Java code)
• Need to run UltraLite Analyzer (ulgen) to
  generate custom database

10
UltraLite Components

• Originally introduced in 8.0.2
• No longer require UltraLite Analyzer
• Table-based API
• Dynamic SQL (new in 9.0)
• Integration with various development
  tools/environments
• AppForge MobileVB
• Native UltraLite for Java (Jeode JVM for Pocket
  PC)
• UltraLite ActiveX
• UltraLite.NET (new in 9.0)
• UltraLite C (new in 9.0)

11
Core UltraLite Runtime
Core UltraLite runtime (static library or DLL)
Java UltraLite runtime
12
Static UltraLite
ulapi.cpp/h
ULGEN
Core UltraLite runtime (static library or DLL)
Code generated by ulgen
Java UltraLite runtime
13
New C Interface
ulapi.cpp/h
ULGEN
Core UltraLite runtime (static library or DLL)
Code generated by ulgen
Java UltraLite runtime
New C Interface (uliface.h)
14
UltraLite Components
3rd Party tool components
ulapi.cpp/h
ULGEN
Core UltraLite runtime (static library or DLL)
Code generated by ulgen
Java UItraLite runtime
New C Interface (uliface.h)
15
UltraLite Components
3rd Party tool components
Core UltraLite runtime (static library or DLL)
Running ULGEN is no longer necessary. This
changes the development process.
New C Interface (uliface.h)
16
Agenda

• Introduction to UltraLite
• UltraLite Component Development
• Dynamic SQL
• Demos and Samples

17
Application Development Differences

• Static UltraLite
• Define schema and SQL statements in an ASA
  database
• Run ULGEN to generate code
• Compile generated code with your code to produce
  an application
• SQL statements (if any) are defined at compile
  time
• UltraLite Components
• Define your schema (more on this shortly)
• Pick a development language (C, Java, BASIC,
  VB.NET, C, Javascript)
• Access the UltraLite database via an API in that
  language
• SQL statements (if any) are ad-hoc

18
Component Application Development

• Design your schema
• Write your application
• Create a new database using the schema if a
  database doesnt exist
• Otherwise, connect to your database
• Deploy your application and your schema file to a
  device
• This is slightly different from current UltraLite
  application development steps

19
Defining Schema

• ULGEN defines the schema with generated code
• For UL Components, schema is defined with
• ULINIT
• Like ULGEN but generates a schema file
• Requires a reference ASA database
• This is the recommended way to create schema
  files!
• The UltraLite Schema Painter
• GUI Explorer-like tool for editing UltraLite
  schemas
• ULXML converts .USM to .XML and vice versa

20
Demo UltraLite Schema Painter
21
More on Schemas

• Schema files are used to create new databases
• Schemas can be altered after apps are deployed
• API method allows applications to explicitly
  alter schema via a schema file
• Data will be preserved whenever possible
• Important to prepare schema for deployment
• Static UltraLite applications will also detect
  schema changes and apply them
• Dont rename objects if you want data preserved!

22
UltraLite Component Class Hierarchy
ULDatabaseManager
ULConnectionParms
ULConnection
ULDatabaseSchema
ULPublicationSchema
ULTable
ULColumn
ULColumnSchema
ULTableSchema
ULIndexSchema
ULPreparedStatement
ULResultSet
ULResultSetSchema
ULSyncResult
ULSyncParms
23
Connecting and Synchronizing
ULDatabaseManager
ULConnectionParms
ULConnection
ULDatabaseSchema
ULPublicationSchema
ULTable
ULColumn
ULColumnSchema
ULTableSchema
ULIndexSchema
ULPreparedStatement
ULResultSet
ULResultSetSchema
ULSyncResult
ULSyncParms
24
Connecting to a Database

• Common to Dynamic SQL and Table APIs
• Using a DatabaseManager object
• Try to connect to your database
• If the database is not found
• Create a new database and connect to it
• Connecting and creating require parameters
• Parameters include userid, password, database
  file, schema file,
• Connection string (required in C, optional in
  others)
• Connection parameters object
• Differs slightly depending on component
• Use properties on object to set connection
  parameters

25
Connect Example (Native UL for Java)

• // Parameter string to support both CE and
  Windows NT/2000/XP
• CreateParms openParms new CreateParms()
• openParms.connectionName "custdb"
• openParms.databaseOnCE ceAppDir
  "ul_custapi.udb"
• openParms.databaseOnDesktop "ul_custapi.udb"
• try
• _conn _dbMgr.openConnection( openParms )
• catch( SQLException ex )
• if( ex.getErrorCode()
• SQLCode.SQLE_ULTRALITE_DATABASE_NO
  T_FOUND )
• openParms.schema.schemaOnCE ceAppDir
  "ul_custapi.usm"
• openParms.schema.schemaOnDesktop
  "ul_custapi.usm"
• conn dbMgr.createDatabase( openParms )
• else
• throw ex

26
Connect Example (MobileVB)

• MobileVB UltraLite component includes a graphical
  ULConnectionParms object

27
Synchronization MobiLink

• MobiLink is
• A two-way synchronization technology for large
  scale mobile database deployment
• Remote database (mobile, embedded, or workgroup
  database server)
• Consolidated database (enterprise, workgroup, or
  desktop database)
• A server that processes synchronization requests
  from remote databases
• Supported synchronization streams
• TCP/IP, HTTP(S), HotSync, ActiveSync
• 128-bit strong encryption of synchronization
  stream supported using Certicom TLS

28
MobiLink Consolidated Databases

• Oracle8
• Microsoft SQL Server
• IBM DB2 UDB
• Adaptive Server Enterprise (ASE)
• Adaptive Server Anywhere (ASA)
• List of Recommended ODBC Drivers
• www.sybase.com/detail?id1011880

29
Adding Synchronization

• Set properties of ULConnection.SyncParms
• myConn.SyncParms.UserName sync_user
• myConn.SyncParms.Stream ULStreamType.TCPIP
• myConn.SyncParms.Version sync_version
• To synchronize
• ULConnection.Synchronize
• Status will be in ULConnection.SyncResult

30
Template Synchronization Forms

• All components (except C) provide a
  customizable dialog that shows synchronization
  status
• Each component provides call-backs during
  synchronization
• Allows you to create your own status dialog

31
Template Synchronization Forms Example (MobileVB)

• Add form to your project
• One extra line of code
• Form_Sync.InitSyncForm Conn

32
Accessing Data Table API
ULDatabaseManager
ULConnectionParms
ULConnection
ULDatabaseSchema
ULPublicationSchema
ULTable
ULColumn
ULColumnSchema
ULTableSchema
ULIndexSchema
ULPreparedStatement
ULResultSet
ULResultSetSchema
ULSyncResult
ULSyncParms
33
UltraLite Table API

• Similar in concept to static C API
• ULTable object
• Properties BOF, EOF, Rowcount, Schema, IsOpen
• Methods Open, Close
• Data Methods Column
• DML InsertBegin/Insert, UpdateBegin/Update,
  Delete, Truncate, DeleteAllRows
• Navigation MoveBeforeFirst, MoveAfterLast,
  MoveFirst, MoveLast, MoveNext, MovePrevious,
  MoveRelative
• Searching FindBegin, FindFirst, FindLast,
  FindNext, FindPrevious, LookupBegin,
  LookupForward, LookupBackward

34
Table API Example (MobileVB)

• Dim QuestionTable As ULTable
• Set QuestionTable Connection.GetTable("questions
  ")
• Public Function DBNextQuestion(text As String) As
  Boolean
• ' is there another question in the database?
• If Not QuestionTable.MoveNext Then
• DBNextQuestion False
• Exit Function
• End If
• ' get the question's number and text
• CurrentQuestionID QuestionTable.Column("id")
  .IntegerValue
• text QuestionTable.Column("text").StringValu
  e
• DBNextQuestion True
• End Function

35
Table API Example (Pocket IE)

• var custlist conn.GetTable( "ULCustomer" )
• custlist.Open("ULCustomerName")
• custlist.FindBegin()
• custlist.Columns("cust_name").value custName
• if ( custlist.FindFirst() ) // already
  present. Done.
• custlist.Close()
• return
• var custid NextCustomerID()
• custlist.InsertBegin()
• custlist.Columns("cust_id").value custid
• custlist.Columns("cust_name").Value custName
• custlist.Insert()
• custlist.Close()
• conn.Commit()

36
UltraLite Table API Performance Tip

• Instead of
• While myTable.MoveNext
• MsgBox myTable.Column(lname).StringValue
• End While
• Do this
• Dim lname_col As ULColumn
• Set lname_col myTable.Column(lname)
• While myTable.MoveNext
• MsgBox lname_col.StringValue
• End While
• Why is this faster?

37
Agenda

• Introduction to UltraLite
• UltraLite Component Development
• Dynamic SQL
• Demos and Samples

38
Dynamic SQL

• Queries and statements built at runtime instead
  of development time
• Prepare a statement, set parameters, execute
• Support for a (large!) subset of what ASA
  supports
• SELECT, INSERT, UPDATE, DELETE
• No subqueries or UNIONs
• CROSS and LEFT OUTER joins supported
• Syntax directed optimization

39
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
40
UltraLite SQL Support - 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 LOWER LTRIM MA
X MIN MINUTE MINUTES MOD MONTH MONTHNAME MONTHS NE
WID NOW NULLIF PATINDEX PI POWER QUARTER RADIANS R
EMAINDER
REPEAT REPLACE REPLICATE RIGHT ROUND RTRIM SECOND
SECONDS SIGN SIMILAR SIN SOUNDEX SPACE SQRT STR ST
RING STRTOUUID STUFF SUBSTRING SUM TAN TODAY TRIM
TRUNCATE TRUNCNUM
UCASE UPPER UUIDTOSTR WEEKS YEAR YEARS YMD
41
More SQL Support

• Operators (binary, arithmetic, string, bitwise)
• Statements
• SELECT
• INSERT (and INSERT INTO SELECT )
• UPDATE
• DELETE

42
SELECT Statement

• SELECT  DISTINCT   FIRST  TOP n  select-list
   FROM table-expression  WHERE search-condition
    GROUP BY group-by-expression,...group-by-expr
  ession  ORDER BY  order-by-expression,...order-
  by-expression 
• table-expression table-name   AS  correlation
  -name  table-expression  join-operator table-e
  xpression  ON join-condition  ,...  ( table-e
  xpression, ... )
• join-operator   , (ON condition not allowed) C
  ROSS JOIN (ON condition not allowed) INNER JOIN
   JOIN (requires ON phrase) LEFT OUTER JOIN
• order-by-expression  integer  expression   A
  SC  DESC 

43
Accessing Data Dynamic SQL
ULDatabaseManager
ULConnectionParms
ULConnection
ULDatabaseSchema
ULPublicationSchema
ULTable
ULColumn
ULColumnSchema
ULTableSchema
ULIndexSchema
ULPreparedStatement
ULResultSet
ULResultSetSchema
ULSyncResult
ULSyncParms
44
Dynamic SQL Classes

• ULPreparedStatement
• Returned by ULConnection.PrepareStatement
• Can include parameters (?)
• SetStringParameter, SetIntegerParameter, etc.
• Set BLOB and LONG VARCHAR parameters in pieces
  AppendByteChunk, AppendStringChunk
• ExecuteQuery and ExecuteStatement methods
• ULResultSet
• Returned by ULPreparedStatement.ExecuteQuery
• Similar to ULTable for fetching data GetString,
  GetInteger, etc.
• BLOB and LONG VARCHAR data returned in pieces
  GetByteChunk, GetStringChunk

45
Dynamic SQL Example (UltraLite.NET C)

• PreparedStatement prepStmt
• ResultSet rSet
• prepStmt connection.PrepareStatement(
• "SELECT description, "
• " COUNT(), "
• " CAST(100(CAST(COUNT() AS
  NUMERIC(10,2))/?) AS INTEGER) "
• "FROM leads l LEFT OUTER JOIN lead_levels ll
  "
• " ON l.lead_level ll.level_id
  "
• "GROUP BY lead_level, description"
• )
• prepStmt.SetIntParameter( 1, num_leads )
• rSet prepStmt.ExecuteQuery()
• while( rSet.MoveNext() )
• MessageBox.Show( rSet.GetString(1) " "
  rSet.GetInt(2) )

46
When to Use Dynamic SQL

• Joining tables is a manual process with the Table
  API
• while child_table.MoveNext()
• parent_table.FindBegin()
• parent_table.Column("id").IntegerValue
• child_table.Column(id").Intege
  rValue
• if parent_table.FindFirst()
• // do something with the data
• end if
• end while
• Joins are much simpler with Dynamic SQL
• prepstmt conn.PrepareStatement(
• "select from child c join parent p on
  p.id c.id" )
• rset prepstmt.ExecuteQuery()
• while( rset.MoveNext() )
• // do something with the data
• end while

47
When to Use Dynamic SQL

• Processing of result set required
• Complex WHERE conditions
• SELECT STRING( UPPER(lname), ', ', fname ) FROM
  employee
• SELECT COUNT() FROM employee WHERE lname LIKE
  'R' AND
• hire_date gt 'jan 1 1995' AND hire_date
  lt 'dec 31 1997'

48
When Not to Use Dynamic SQL

• Simple single-table uses
• Instead of
• pStmt conn.PrepareStatement( SELECT COUNT()
  FROM mytable )
• rSet pStmt.ExecuteQuery()
• num_rows rSet.GetIntValue(1)
• You can do this
• tbl conn.GetTable( mytable )
• tbl.Open()
• num_rows tbl.RowCount

49
When Not to Use Dynamic SQL

• Instead of
• pStmt conn.PrepareStatement( SELECT col1,
  col2, col3 FROM mytable )
• rSet conn.ExecuteQuery()
• While rSet.Next()
• col1_val rSet.GetInteger( rSet.Schema.GetColumnI
  D( col1 ) )
• col2_val rSet.GetInteger( rSet.Schema.GetColumnI
  D( col2 ) )
• do something
• Do this
• tbl conn.GetTable( mytable )
• tbl.Open
• While tbl.MoveNext()
• col1_val tbl.Column(col1).IntegerValue
• col2_val tbl.Column(col2).IntegerValue
• do something

50
UltraLite Query Optimization

• UltraLite optimization is less extensive than ASA
• Syntax-directed optimization
• Query plan can be retrieved
• ULPreparedStatement.Plan property
• PreparedStatement.GetPlan() function (C
  interface)

51
Plan Example

• SELECT description,
• COUNT(),
• CAST(100(CAST(COUNT() AS
  NUMERIC(10,2))/?) AS BIGINT)
• FROM leads l LEFT OUTER JOIN lead_levels ll ON
• l.lead_level
  ll.level_id
• GROUP BY lead_level, description
• templojoinscan(leads,3),index-scan(lead_levels,0
  )
• leads3 is index on leads(lead_level)
• lead_levels0 is PK index

52
Miscellaneous

• Desktop ActiveX and MobileVB components now use
  the Unicode runtime
• .udb files compatible between Win32 and WinCE
• Make sure that desktop .udb viewers are Unicode
  as well!
• UltraLite Engine
• UltraLite C API also provides a database
  server
• Allows multiple applications to concurrently use
  the same UltraLite database
• Supported on Win32, WinCE
• dbuleng9.exe is the server
• Will be auto-started on first connect
• Will auto-stop on last disconnect (or call
  dbulstop.exe)
• Link applications with ulimpcw.lib (Win32) or
  ulrtc.lib (CE)

53
Agenda

• Introduction to UltraLite
• UltraLite Component Development
• Dynamic SQL
• Demos and Samples

54
Demos

• What do YOU want to see?
• C Interface
• ulsqlsamp
• CustDB for
• AppForge MobileVB
• UltraLite.NET
• UltraLite ActiveX (eVB and PocketIE)
• Native UltraLite for Java

55
UltraLite Samples

• Banff Reviewers Guide
• Dynamic SQL (UltraLite.NET)
• UL.NET
• UltraLite Engine (new C API)
• SQL Anywhere Studio install (samples directory)
• CustDB (of course!)
• UltraLite\ulsqlsamp (C API, Dynamic SQL)
• Various samples for other components
• http//www.ianywhere.com/developer/ulcs.html

56
iAnywhere Solutions at TechWave2003
Activities for iAnywhere Solutions

• Ask the iAnywhere Experts on the Technology
  Boardwalk
• 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
• m-Business Pavilion
• Visit the m-Business Pavilion in the exhibit hall
  to see how companies like Intermec have built
  m-Business solutions using iAnywhere Solutions
  technology
• Wi-Fi Hotspots brought to you by Intel
  iAnywhere Solutions
• You can enjoy wireless internet access via a
  Wi-Fi hotspot provided by Intel and iAnywhere
  Solutions. Using either a laptop or PDA that is
  Wi-Fi 802.11b wirelessly-enabled, visitors can
  access personal email, the internet, and
  "TechWave To Go", a My AvantGo channel providing
  up-to-date information about TechWave classes,
  events and more.

57
iAnywhere Solutions at TechWave2003
Activities for iAnywhere Solutions

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

58
Questions?