This paper assists with the mobile application migration process from Oracle Database Lite to Sybase SQL Anywhere technology. The goal of this document is to provide helpful information regarding the migration process by documenting the steps needed to transform the sample .NET application “Transport” included in the Oracle Database Lite installation. Topics covered range from connecting to the Oracle backend database, creating an UltraLite database to replace the Oracle Lite database, setting up MobiLink to replace the Mobile Sync Server and altering the application to function with the UltraLite database.
3. SQL Anywhere Overview
3.1. SQL Anywhere Terminology
3.2. Migration Diagram
4. The Migration Process
4.1. Creating the ODBC Data Source
4.2. Creating a MobiLink Project
4.3. Creating a Synchronization Model
4.4. Deploying the Synchronization Model
4.5. Create a User for UltraLite Database
4.6. Altering the Application
4.6.1 Form: Transport.vb
4.6.2. Form: frmMain.vb
4.6.3. Form: frmUser.vb
4.6.4. Form: frmView.vb
4.6.5. Windows Mobile Application
4.6.6. Running the Application
This document assists with the mobile application migration process from Oracle Database Lite to Sybase SQL Anywhere technology. SQL Anywhere enables bi-directional enterprise data synchronization between multiple mobile devices and an Oracle Database.
The goal of this paper is to provide helpful information regarding the migration process by documenting the steps needed to transform the sample .NET application “Transport” included in the Oracle Database Lite installation.
We will cover topics ranging from connecting to the Oracle backend database, creating an UltraLite database to replace the Oracle Lite database, setting up MobiLink™ to replace the Mobile Sync Server and altering the application to function with the UltraLite database.
This document assumes the user has a good understanding of Oracle and Oracle Lite databases and is in the process of becoming familiar with Sybase SQL Anywhere technology.
• Oracle Database 11g R2
–Install sample schemas (MASTER owner)
• Oracle Database Lite 10g R2
–Install Mobile Server
–Install Mobile Development Kit with sample applications
• Visual Studio 2010
• Sybase SQL Anywhere 12.0.0
–Download the free Developer Edition
For this paper, the Oracle Database Lite Mobile Development Kit sample named “Transport” has been setup to be fully functional and ready to synchronize with a sample Oracle 11g R2 database named “Orcl”.
Default sample location: ORACLEHOME\Mobile\SDK\samples\ADO.NET\Win32\Transport. Please note that a Windows Mobile (WinCE) sample is also available and can be migrated to SQL Anywhere technology using the same steps described in this document.
Before migrating it is recommended to properly backup the Oracle Database files and the application. It’s also a good practice to set up a test environment with SQL Anywhere and the application before deploying the new solution into production.
SQL Anywhere is a comprehensive suite of solutions that provides data management, synchronization and data exchange technologies that enable the rapid development and deployment of database-powered applications in remote and mobile environments. SQL Anywhere consists of several components, described below.
SQL Anywhere Server:
A relational, transaction-based database that is the core of SQL Anywhere. It runs on a variety of hardware (from PDAs to multi-CPU servers) and operating systems (Windows, Windows Mobile, Linux, Unix, and Mac® OS X).
SQL Anywhere is comparable to Oracle database. Both provide enterprise caliber functionality, including database triggers, stored procedures, user-defined functions and full text search. Both can be embedded into applications and both can act as backend databases in a mobile data synchronization environment. In this migration process, we will be keeping the Oracle 11g database as the backend with no further changes to the database.
UltraLite is used to build and deploy relational database applications on small devices running Windows Mobile, BlackBerry® and iPhone® operating systems. UltraLite has built-in support for data synchronization and lets you create relational databases that can be as small as 75KB.
UltraLite compared to Oracle Lite has many similarities and differences. Both are small-footprint databases and are easily deployed to mobile devices. Oracle Lite can only synchronize to an Oracle database. However, UltraLite is able to synchronize to many different backends including Sybase, Oracle, Microsoft and IBM. In this migration process we will be replacing an Oracle Lite database with an UltraLite database.
This server manages the synchronization process and provides the interface between all MobiLink clients (remote/mobile databases) and the backend database server. MobiLink is intended for two-way synchronization of data between the central database and a large number of remote/mobile databases (SQL Anywhere or UltraLite).
MobiLink Server and Oracle Mobile Sync Server are both middle-tier servers used for data synchronization. By changing over to MobiLink, you will be able to use visual administration tools to help diagnose and solve performance issues. MobiLink will also immediately notify client applications of any problems during the synchronization process, thus requiring less manual administration of errors. In this migration process, we will be using MobiLink to synchronize between an Oracle 11g database and an UltraLite database, hence replacing the Mobile Sync Server.
There are two types of clients: UltraLite and SQL Anywhere databases. The MobiLink client can be installed on a handheld device, a server or desktop computer, or a smartphone.
Consolidated (Backend) Database:
This database typically contains the central copy of your application information in the synchronization system. It also typically holds system tables and procedures that are required by MobiLink synchronization, and state information needed to synchronize. The backend database can be SQL Anywhere, Sybase Adaptive Server® Enterprise, Oracle, MySQL, Microsoft SQL Server, or IBM DB2.
In this migration process we will use Oracle Database 11g R2 as our backend database.
Remote or Mobile Database:
This database typically contains the application information specific to the remote device and is either an UltraLite database (for handheld or smartphone) or a SQL Anywhere database (for desktop computers).
In this migration process we will create an UltraLite database. Note that SQL Anywhere can also run on Windows Mobile devices, so it’s also possible to migrate from Oracle Database Lite to SQL Anywhere when using that platform.
An easy-to-use database management GUI application for configuring database settings and properties. Sybase Central can also be used for managing other Sybase products including MobiLink synchronization with non-Sybase backend databases. Sybase Central is similar to the Mobile Device Workbench in that it enables you to design and develop the schema of your mobile database.
A typical Oracle Database Lite deployment is illustrated in the following diagram:
The application is connected to an Oracle Lite 10g R2 database and synchronizing data with an Oracle 11g R2 database. The end result of this section will present the same transportation application connected to an UltraLite 12.0.0 database synchronizing data with the same Oracle 11g R2 database.
1. Go to: Start – Programs – SQL Anywhere 12 - Administration Tools – ODBC Data Source Administrator.
3. Click on the Add… button.
4. Select the driver iAnywhere Solutions 12 – Oracle and click Finish.
5. Fill in the following options:
Data source name: OracleODBC
User ID: SYSTEM
6. Check the box Procedure returns results or uses VARRAY Parameters.
7. Click the Test Connection button and click OK.
|If an error occurs, consult additional information regarding this driver in the documentation at:
iAnywhere Solutions 12 – Oracle ODBC driver
8. Click OK to finish configuration of the ODBC Data Source.
9. Click OK to finish using the ODBC Data Source Administrator.
1. Go to: Start – Programs – SQL Anywhere 12 – Administration Tools – Sybase Central.
2. From the View menu, select Folders.
3. On the left pane, right click on MobiLink 12 and select New – Project
4. In the Name field, type a name for the project (example mlp).
5. Specify the location you would like to save the new project or click Browse to choose a folder (example C:)
6. Click the Next button.
7. Check Add a consolidated database to the project.
8. Enter a Database display name (example OracleDB).
9. Check Remember the password.
10. Click on the Edit… button.
11. Click on the Browse… button.
12. Select the data source name that you created in the section 4.1 (example OracleODBC).
13. Click the OK button.
14. In the Connection Parameters window, enter the user name and password for the Oracle database.
15. Click the Save button to dismiss this dialog.
16. Click the Next button.
17. Choose Do not add a model. We will be adding the model to the backend database at a later time.
18. Click Finish to save the new project.
19. You will be prompted with the message below, click Yes.
The MobiLink setup will create system tables, views, triggers, procedures, and sequences that store information about MobiLink users, subscriptions, tables, scripts, script versions and other information required for MobiLink synchronization.
1. Right click on Synchronization Models and select New – Synchronization Model
2. From the Welcome page, enter SyncModel as the name for your synchronization model.
3. Click the Next button.
4. Select the three checkboxes and then click Next.
5. Click the Next button.
6. Select the data source name that you created in the section 4.1 (example OracleODBC).
7. Select Only load the database schema for selected owners.
8. Choose the owner Master and click Next.
9. Select No, create a new remote database schema.
10. Click the Next button.
11. Select the tables Packages, Routes and Trucks since these are the tables that you want to have in the remote database.
12. Click the Next button.
13. Choose Timestamp-based download and click Finish to continue with default settings on the rest of the configuration pages.
14. View our documentation to learn more about the synchronization model and options to customize your settings.
1. Right click on the Synchronization Model created in section 4.3 (example SyncModel) and select Deploy.
2. Choose Specify the deployment details for one or more of the following:
3. Check all three options:
Remote database and synchronization clients
4. Click the Next button.
5. Keep the default values for the location and click Next.
6. You will be prompted with the message The directory… does not exist. Do you want to create it?
7. At this point MobiLink will issue an error because views belonging to the user ‘SYSTEM’ cannot be accessed. All objects belonging to ‘SYSTEM’ are handled as system objects. A typical database will not use ‘SYSTEM’ as its owner for user objects.
You may safely ignore this error and continue by clicking OK.
8. You may also safely assume the database has been correctly setup and continue by clicking Yes.
9. Choose New UltraLite database and click Next.
10. For the remote UltraLite Database location leave the default value and click Next.
11. You will be prompted with the message The directory… does not exist. Do you want to create it?
12. Enter “mldba” and “mlsql” for the Username and Password. These credentials will be used to connect to the MobiLink Server.
13. Click the Next button.
14. Select the type of communication stream and port as HTTP and 8080 respectively and click Next.
Make sure the port number selected is not being used by other programs running on your machine.
15. Leave the default values for the Client Stream Parameters and click Next.
16. Leave the field blank and click Next.
17. Select Maximal verbosity (-v+) and click Next. Maximal verbosity is useful for testing and debugging.
18. In the Additional MobiLink server command-line option field, enter:
This option instructs all MobiLink server messages to be written in the file ‘mlsrv.log’.
19. Click Finish to use the default settings for the rest of the Wizard.
1. Right click on UltraLite 12 and select Connect.
2. Enter C:\mpl\SyncModel\remote\SyncModel_remote.udb beside the Database file field and click Connect.
3. Expand SyncModel_remote – DBA and right click on Users, followed by New – Users.
4. Enter JUNIUS as the name for the new user and click Next.
5. Enter JUNIUS as the password for the new user and click Finish.
6. Right click on SyncModel_remote – DBA and select Disconnect.
You may now choose to delete the DBA user. For simplicity, we leave that user in the database.
At this point you have successfully created an UltraLite database with the same schema as your Oracle Lite database. The UltraLite database is currently empty, but on the first synchronization it will be populated with the Oracle data. You have also created the same user for your UltraLite database compared to the Oracle Lite database. You now have an UltraLite version of the Oracle Lite database and a backend database (Oracle 11g) ready to synchronize. You have finished configuring MobiLink which has replaced the Mobile Server to synchronize data between the remote and central databases.
In this section you will be altering a Windows application called ‘Transport’ that can be used to manage package delivery information for a trunk delivery service. The application allows you to view packages, delete pages, update packages and synchronize the data between the remote and backend database.
The default location of the sample is: ORACLEHOME\Mobile\SDK\samples\ADO.NET\Win32\Transport. We will now refer to this location as <transport_dir>. The same changes apply to a Windows Mobile (WinCE) sample located at: ORACLEHOME\Mobile\SDK\samples\ADO.NET\WinCE\Transport.
Section 4.7.5 is added to show additional steps required for the Windows Mobile application.
|Application Using Oracle Technology||Application Using SQL iAnywhere Technology|
OracleParameter (name, type, sourceColumn)
|ULParameter (name, type, size, sourceColumn)|
|Application Using Oracle Technology||Application Using SQL iAnywhere Technology|
|CreateCommand() function||We removed this function
as it is not needed.
|OracleEngine Synchronization() method||The synchronization() method||The synchronization()
method is called
to initialize the
between the backend
database (Oracle 11g DB)
and the remote database
|OracleSync Connect() method||The connect() method||The connect() method
declared in the form
‘Transport.vb’ is called to
connect to the database
and access the database
1. Change Line 3 (namespace)
a. To do so, go to the Project tab and click on Add Reference…
|If you cannot find iAnywhere.Data.UltraLite:
a. Click on the Browse Tab
b. Locate the directory <install-dir>\SQL Anywhere 12\UltraLite\UltraLite.NET\Assembly\V2\
<install-dir> is the location where you installed SQL Anywhere. On Windows, the default location is
C:\Program Files\SQL Anywhere 12.
c. Select the iAnywhere.Data.UltraLite.dll file and click OK.
|Oracle||Public conn As OracleConnection
Public cmdDel As OracleCommand
Public cmdSel As OracleCommand
Public cmdUpd As OracleCommand
|Sybase||Public conn As ULConnection
Public cmdDel As ULCommand
Public cmdSel As ULCommand
Public cmdUpd As ULCommand
Change Lines 17-21 (Connect to the database)
Dim dsn As String = “dsn=” & usr & “_” &
“transport;uid=system;pwd=” & pwd
conn = New Oracle.DataAccess.Lite.OracleConnection(dsn)
|Sybase||Dim dbf As String = ”dbf=C:\mlp\SyncModel\remote\SyncModel_
+ “;uid=” + usr + “;pwd=” +
conn = New iAnywhere.Data.UltraLite.ULConnection(dbf)
Change Line 29 (Select command)
|Oracle||Oracle cmdSel = CreateCommand()|
|Sybase||cmdSel = conn.CreateCommand()|
Change Lines 32-35 (Delete command and add parameter)
|Oracle||cmdDel = CreateCommand()
Dim par As OracleParameter = New OracleParameter(“@did”,
cmdDel = conn.CreateCommand()
cmdDel = conn.CreateCommand()
Dim par As New ULParameter
par.ParameterName = “@did”,
par.Dbtype = DbType.String
par.SourceColumn = “DID”
|Oracle||cmdUpd = CreateCommand()|
|Sybase||cmdUpd = conn.CreateCommand()|
|Oracle||par = New OracleParameter(“@prty”, DbType.String, “PRTY”)|
par = New ULParameter("@prty", DbType.String, 0, "PRTY")
par = New ULParameter
|Oracle||par = New OracleParameter("@sts", DbType.String, "DSTS")|
par = New ULParameter(“@sts”, DbType.String, 0, “DSTS”)
par = New ULParameter
Change Line 59 (Add Parameter)
|Oracle||par = New OracleParameter(“@did”, DbType.String, “DID”)|
par = New ULParameter(“@did”, DbType.String, 0, “DID”)
par = New ULParameter
4. Change Line 73 (Declaring variable)
|Oracle||Public Function GetConnection() As OracleConnection|
Public Function GetConnection() As ULConnection
|Oracle||Public Function CreateCommand() As OracleCommand
Return New OracleCommand(conn)
|Oracle||Dim sync As OracleSync = New OracleSync
textUser.Text = sync.UserName
textPassword.Text = sync.Password
Dim UID As String = textUser.Text
|Oracle||Dim dba As OracleDataAdapter|
|Sybase||Dim dba As ULDataAdapter|
3. Change Line 362
|Oracle||dba = New OracleDataAdapter|
dba = New ULDataAdapter
Note: Do not exit Visual Studio until specified later.
The following steps were tested with the Windows Mobile 6.0 Professional SDK. However, they are also applicable to the PocketPC 2003 and Windows Mobile 5.0 SDKs.
1. You must open the Windows Mobile Application in Visual Studio 2008 (or lower).
2. From the Project menu, choose Add Existing Item and browse to <install-dir>\SQL Anywhere 12\UltraLite\UltraLite.NET\CE\Arm.50
3. In the Files of Type list, choose Executable Files.
4. Select ulnet12.dll, click the arrow on the Add button and select Add as Link.
5. In the Solutions Explorer right click on ulnet12.dll and select Properties.
6. Select the value “Content” for Build Action.
7. Select the value “Copy always” for Copy to Output Directory.
8. From the Project menu, choose Add Existing Item and browse to C:\mlp\SyncModel\remote\
9. In the Files of Type list, choose All Files
10. Select SyncModel_remote.udb, click the arrow on the Add button and select Add as Link.
11. In the Solutions Explorer right click on SyncModel_remote.udb and select Properties.
12. Select the value “Content” for Build Action.
13. Select the value “Copy if newer” for Copy to Output Directory.
14. Open the file “transport.vb” and at the very top, add the following:
Option Strict Off
This Option Strict statement restricts implicit data type conversions to only widening conversions.
In the subroutine “Connect” change the location of the UltraLite database (lines 17-21):
Dim dbf As String=”dbf=\Program Files\Transport\SyncModel_remote.udb”
You are now finished altering the application but do not exit Visual Studio until specified later.
2. Confirm that the application deploys successfully to the screen (or Windows Mobile 6 Professional Emulator for Windows CE application).
|If the application did not deploy successfully:
a. Confirm that the path to the database file in your application code is correct.
b. Confirm that you chose Link File when adding ulnet12.dll and the UltraLite db.
c. Confirm that you set Build Action to Content and set Copy to Output Directory to Copy Always for the properties of ulnet12.dll
d. Confirm that you set Build Action to Content and set Copy to Output Directory to Copy if Newer for the properties of the UltraLite database.
8. The MobiLink server starts and launches its status dialog.
This migration paper covers many differences between Oracle Lite Database and Sybase
It provides information and steps necessary to migrate from Oracle Lite to UltraLite. This paper
the simplicity involved in this migration process. However, the procedure may vary depending
to the backend Oracle database, creating a new UltraLite database to replace Oracle Lite, configuring
technology for synchronization and altering the application code. For more information please
Online documentation for SQL Anywhere 12: