Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » MobiLink - Getting Started » Tutorial: Using Direct Row Handling

Introduction to direct row handling tutorial Next Page

Lesson 1: Set up your MobiLink consolidated database


You can use a SQL Anywhere, Adaptive Server Enterprise, Oracle, Microsoft SQL Server, or IBM DB2 MobiLink consolidated database as your consolidated database. Your MobiLink consolidated database is a central repository of data and includes MobiLink system tables and stored procedures used to manage the synchronization process. With direct row handling, you synchronize with a data source other than a consolidated database, but you still need a consolidated database to maintain information used by the MobiLink server.

In this lesson, you will:

Note

If you already have a MobiLink consolidated database set up with MobiLink system objects and a DSN, you can skip this lesson.

Create your consolidated database

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

To create your SQL Anywhere RDBMS
  1. Start Sybase Central.

    Choose Start > Programs > SQL Anywhere 10 > Sybase Central.

    Sybase Central appears.

  2. In Sybase Central, choose Tools > SQL Anywhere 10 > Create Database.

    The Create Database wizard appears.

  3. Click Next.

  4. Leave the default of Create Database on this Computer, and then click Next.

  5. Enter the file name and path for the database. For example, enter:

    c:\MLdirect\MLconsolidated.db

  6. Follow the remaining instructions in the wizard, accepting the default values, except as follows.

  7. Click Finish.

    The database called MLconsolidated appears in Sybase Central.

Define an ODBC data source for the consolidated database.

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

To define an ODBC data source for the consolidated database
  1. Start the ODBC Administrator:

    From the Sybase Central Tools menu, choose SQL Anywhere 10 > Open ODBC Administrator.

    The ODBC Data Source Administrator appears.

  2. On the User DSN tab, click Add.

    The Create New Data Source dialog appears.

  3. Select SQL Anywhere 10, and then click Finish.

    The ODBC Configuration for SQL Anywhere 10 dialog appears.

  4. On the ODBC tab, type the Data source name mobilink_db. On the Login tab, type DBA for the User ID and sql for the Password. On the Database tab, type MLconsolidated for the Server Name and c:\MLdirect\MLconsolidated.db for the Database file.

  5. Click OK to define the data source and OK again to close ODBC Administrator.

Create tables for synchronization

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

Column

Description

order_id

A unique identifier for orders.

product_id

A unique identifier for products.

quantity

The number of items sold.

order_status

The order status.

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.

To create the RemoteOrders table
  1. Connect to your database using Interactive SQL.

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

  2. Run the following command in Interactive SQL to create the RemoteOrders table.

    CREATE TABLE RemoteOrders
    (
     order_id           integer not null,
     product_id         integer not null,
     quantity           integer,
     order_status       varchar(10) default 'new',
     last_modified      timestamp default current timestamp,
     primary key(order_id)
    )

    Interactive SQL creates the RemoteOrders table in your consolidated database.

    Stay connected in Interactive SQL for the following procedure.

  3. Run MobiLink setup scripts

    You can find setup scripts for each supported consolidated database (SQL Anywhere, Adaptive Server Enterprise, Oracle, Microsoft SQL Server, and IBM DB2) in the MobiLink/setup subdirectory of your SQL Anywhere 10 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
    1. Connect to your consolidated database in Interactive SQL if you are not already connected.

      At a command prompt, type the following command:

      dbisql -c "dsn=mobilink_db"
    2. Run the following command in Interactive SQL to create MobiLink system tables and stored procedures. Replace c:\Program Files\SQL Anywhere 10\ with the location of your SQL Anywhere 10 installation.

      read "c:\Program Files\SQL Anywhere 10\MobiLink\setup\syncsa.sql"

      Interactive SQL applies syncsa.sql to your consolidated database.

    3. Stay connected in Interactive SQL for the next lesson.

      Further reading

      For information about creating SQL Anywhere databases, see Initialization utility (dbinit).

      For information about creating tables, see CREATE TABLE statement.

      For information about setting up MobiLink consolidated databases, see MobiLink Consolidated Databases.