MobiLink system tables

MobiLink Synchronization User's Guide
Tutorial: Using MobiLink with an Oracle 8i Consolidated Database
Lesson 1: Create your databases

MobiLink system tables

MobiLink comes with a script called syncora.sql, located in the MobiLink\setup subdirectory of your SQL Anywhere installation. Syncora.sql contains SQL statements, written in Oracle SQL, to prepare Oracle databases for use as consolidated databases. It creates a series of system tables, triggers, and procedures for use by MobiLink. The system tables are prefaced with ML_. MobiLink works with these tables during the synchronization process.

Create MobiLink system tables within Oracle

Start SQL Plus. Choose Start > Programs > Oracle - OraHome81 > Application Development > SQL Plus.

Connect to your Oracle database by using Oracle SQL Plus. Log on using the system schema with password manager.
Run syncora.sql by typing the following command:
```
@path\syncora.sql;
```
where path is the MobiLink\setup subdirectory of your SQL Anywhere 9 installation. If there are spaces in your path, you should enclose the path and filename in quotation marks.

To verify that the system tables are installed

Start SQL Plus. Choose Start > Programs > Oracle - OraHome81 > Application Development > SQL Plus.
Run the following SQL statement to yield a listing of the MobiLink system tables, procedures, and triggers:
```
SELECT object_name
FROM all_objects
WHERE object_name
LIKE 'ML_%';
```
If all of the objects shown in the following table are included, you can proceed to the next step.

OBJECT_NAME
ML_ADD_CONNECTION_SCRIPT ML_ADD_DNET_CONNECTION_SCRIPT ML_ADD_DNET_TABLE_SCRIPT ML_ADD_JAVA_CONNECTION_SCRIPT ML_ADD_JAVA_TABLE_SCRIPT ML_ADD_LANG_CONNECTION_SCRIPT ML_ADD_LANG_TABLE_SCRIPT ML_ADD_TABLE_SCRIPT ML_ADD_USER ML_CONNECTION_SCRIPT ML_CONNECTION_SCRIPT_TRIGGER ML_SCRIPT ML_SCRIPTS_MODIFIED ML_SCRIPT_TRIGGER ML_SCRIPT_VERSION ML_SUBSCRIPTION ML_TABLE ML_TABLE_SCRIPT ML_TABLE_SCRIPT_TRIGGER ML_USER

OBJECT_NAME

ML_ADD_CONNECTION_SCRIPT

ML_ADD_DNET_CONNECTION_SCRIPT

ML_ADD_DNET_TABLE_SCRIPT

ML_ADD_JAVA_CONNECTION_SCRIPT

ML_ADD_JAVA_TABLE_SCRIPT

ML_ADD_LANG_CONNECTION_SCRIPT

ML_ADD_LANG_TABLE_SCRIPT

ML_ADD_TABLE_SCRIPT

ML_ADD_USER

ML_CONNECTION_SCRIPT

ML_CONNECTION_SCRIPT_TRIGGER

ML_SCRIPT

ML_SCRIPTS_MODIFIED

ML_SCRIPT_TRIGGER

ML_SCRIPT_VERSION

ML_SUBSCRIPTION

ML_TABLE

ML_TABLE_SCRIPT

ML_TABLE_SCRIPT_TRIGGER

ML_USER

Note

If any of the objects are missing, the procedure you just completed was not successful. In this case, you need to review the MobiLink error messages to see what went wrong; correct the problem; and then drop the MobiLink system tables as follows. However, do not drop system tables if there are any tables starting with ML_ other than the ones listed above.

To drop the MobiLink system tables

Generate and run the drop statements:
- Run the following SQL statement in SQL Plus:
```
select 'drop ' || object_type || ' ' || object_name || ';'
from all_objects
where object_name like 'ML_%';
```
  This generates a list of tables, procedures and triggers to be dropped. Copy this list to a text file and save it as drop.sql in your OracleTut directory. Remove any lines that do not contain drop statements.
- Execute the SQL statements in drop.sql by running the following command:
```
@c:\OracleTut\drop.sql;
```
  Replace c:\ with the location of your OracleTut directory. Run drop.sql a second time to delete tables that were not removed the first time because of dependencies.
  
  You can now repeat the instructions for Creating MobiLink system tables in Oracle.

The remote data source

To initialize your remote database

Open a command prompt and navigate to your OracleTut directory, for example c:\OracleTut. Run the following command line:
```
dbinit remote.db
```
Verify the successful creation of the database by getting a listing of the contents of this directory. The file remote.db should appear in the directory listing.

To create an ODBC data source for the remote database

Open a command prompt and navigate to your OracleTut directory. Run the following command line:
```
dbdsn -w test_remote -y -c "uid=DBA;pwd=SQL;
   dbf=c:\OracleTut\remote.db;eng=remote"
```
Replace c:\ with the location of your OracleTut directory.

To verify your new data source

Choose Start > Programs > Sybase SQL Anywhere 9 > Adaptive Server Anywhere > ODBC Administrator.

The ODBC Data Source Administrator appears.
Click the User DSN tab.
Select test_remote from the list of data sources and click Configure.
Test test_remote by clicking the Test Connection button.
Click OK to close the ODBC Data Source Administrator.