Lesson 1: Set up your MobiLink consolidated database

This lesson guides you through the following steps to set up your SQL Anywhere consolidated database:

Create the consolidated database and schema.
Run the MobiLink setup script.
Define an ODBC data source for the consolidated database.

Create your consolidated database

In this tutorial, you create a SQL Anywhere database using the Sybase Central Create Database Wizard.

To create your SQL Anywhere RDBMS

Choose Start » Programs » SQL Anywhere 12 » Administration Tools » Sybase Central.
In Sybase Central, choose Tools » SQL Anywhere 12 » Create Database.
Click Next.
Leave the default of Create Database On This Computer, and then click Next.
In the Save The Main Database File To The Following File field, type the file name and path for the database. For example, c:\MLmon\MLconsolidated.db. Click Next.
Follow the remaining instructions in the Create Database Wizard and accept the default values. On the Connect To The Database page, clear the Stop The Database After Last Disconnect option.
Click Finish.

The MLconsolidated database appears in Sybase Central.
Click Close on the Creating Database window.

Define an ODBC data source for the consolidated database

Use the SQL Anywhere 12 driver to define an ODBC data source for the MLconsolidated database.

To define an ODBC data source for the consolidated database

From the Sybase Central Tools menu, choose SQL Anywhere 12 » Open ODBC Administrator.
Click the User DSN tab, and click Add.
In the Create New Data Source window, click SQL Anywhere 12 and click Finish.
Perform the following tasks in the ODBC Configuration For SQL Anywhere window:
1. Click the ODBC tab.
2. In the Data Source Name field, type mlmon_db.
3. Click the Login tab.
4. In the Authentication dropdown list, choose Database to connect using your user ID and password.
5. In the User ID field, type DBA.
6. In the Password field, type sql.
7. From the Action dropdown list, choose Connect To A Running Database On This Computer.
8. In the Server Name field, type MLconsolidated.
9. Click OK.
Close the ODBC data source administrator.

Click OK on the ODBC Data Source Administrator window.

Set up tables for synchronization

In this procedure, you create the Product table in the MobiLink consolidated database. The Product table contains the following columns:

Column	Description
name	The name of the product.
quantity	The number of items sold.
last_modified	The last modification date of a row. You use this column for timestamp based downloads, a common technique used to filter rows for efficient synchronization.

You create temporary tables for conflict resolution purposes in addition to the Product table. These tables are named Product_old, and Product_new.

To create the tables

Connect to your database using Interactive SQL.

You can start Interactive SQL from Sybase Central or from a command prompt.

To start Interactive SQL from Sybase Central, right-click the MLconsolidated - DBA database and click Open Interactive SQL.

To start Interactive SQL at a command prompt, run the following command:

dbisql -c "dsn=mlmon_db"

Run the following SQL script in Interactive SQL to create the Product table:

CREATE TABLE Product (
    name            VARCHAR(128) NOT NULL PRIMARY KEY,
    quantity        INTEGER,
    last_modified   TIMESTAMP DEFAULT TIMESTAMP
)

Interactive SQL creates the Product table in your consolidated database.

Run the following SQL script in Interactive SQL to create the temporary tables:

CREATE TABLE Product_old (
    name            VARCHAR(128) NOT NULL PRIMARY KEY,
    quantity        INTEGER,
    last_modified   TIMESTAMP DEFAULT TIMESTAMP
)
CREATE TABLE Product_new (
    name            VARCHAR(128) NOT NULL PRIMARY KEY,
    quantity        INTEGER,
    last_modified   TIMESTAMP DEFAULT TIMESTAMP
)

Interactive SQL creates the Product_old and Product_new tables in your consolidated database.

After creating the tables, you must populate the Product table.

To populate the Product table

Connect to your consolidated database in Interactive SQL if you are not already connected.

You can start Interactive SQL from Sybase Central or from a command prompt.

To start Interactive SQL from Sybase Central, right-click the MLconsolidated - DBA database and click Open Interactive SQL.

To start Interactive SQL at a command prompt, run the following command:

dbisql -c "dsn=mlmon_db"

Run the following SQL script in Interactive SQL to create the Product table:

INSERT INTO Product(name, quantity)
    VALUES ( 'Screwmaster Drill', 10);

INSERT INTO Product(name, quantity)
    VALUES ( 'Drywall Screws 10lb', 30);

INSERT INTO Product(name, quantity)
    VALUES ( 'Putty Knife x25', 12);

COMMIT;

Verify that the Product table contains product information.

Run the following SQL script to verify the contents:
SELECT * FROM Product
The contents of the Product table should appear in Interactive SQL.

Stay connected in Interactive SQL for the next procedure.

Run MobiLink setup scripts

You can find setup scripts for each supported consolidated database in the MobiLink/setup subdirectory of your SQL Anywhere 12 installation.

In this procedure you set up a SQL Anywhere consolidated database. You do this using the syncsa.sql setup script. Running syncsa.sql creates a series of system tables and stored procedures prefaced with ml_. The MobiLink server works with these tables and stored procedures in the synchronization process.

To install MobiLink system tables

Connect to your consolidated database in Interactive SQL if you are not already connected.

Run the following command:
dbisql -c "dsn=mlmon_db"

Run the following command in Interactive SQL to create MobiLink system tables and stored procedures. Replace C:\Program Files\SQL Anywhere 12\ with the location of your SQL Anywhere 12 installation.

READ "C:\Program Files\SQL Anywhere 12\MobiLink\setup\syncsa.sql"

Interactive SQL applies syncsa.sql to your consolidated database.

Stay connected in Interactive SQL for the next lesson.