Lesson 2: Generating a database schema

In this lesson, you generate the database schema, which includes a Dealer table and a download_cursor synchronization script. A table and stored procedure is used to generate server-initiated synchronization push requests.

Prerequisites

This lesson assumes you have completed all preceding lessons. See Lesson 1: Setting up the consolidated database.

This lesson assumes that you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Configuring server-initiated synchronization using gateways.

Task

Click Start » Programs » SQL Anywhere 16 » Administration Tools » Sybase Central.
Perform the following tasks to connect to the consolidated database:
1. Click Connections » Connect with SQL Anywhere 16.
2. In the User ID field, type DBA.
3. In the Password field, type sql.
4. From the Action dropdown list, click Connect with an ODBC Data Source.
5. Click ODBC Data Source name, and click Browse.
6. Select sis_cons, and then click OK.
7. Click Connect.

Connect to your database using Interactive SQL.

You can start Interactive SQL from Sybase Central or at 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=sis_cons"

Run the following SQL statement to create and set up the Dealer table:

CREATE TABLE Dealer (
    name VARCHAR(10) NOT NULL PRIMARY KEY,
    rating VARCHAR(5),
    last_modified TIMESTAMP DEFAULT TIMESTAMP
)

Insert data into the Dealer table using the following statements:

INSERT INTO Dealer(name, rating) VALUES ('Audi', 'a');
INSERT INTO Dealer(name, rating) VALUES ('Buick', 'b');
INSERT INTO Dealer(name, rating) VALUES ('Chrysler', 'c');
INSERT INTO Dealer(name, rating) VALUES ('Dodge', 'd');
INSERT INTO Dealer(name, rating) VALUES ('Eagle', 'e');
INSERT INTO Dealer(name, rating) VALUES ('Ford', 'f');
INSERT INTO Dealer(name, rating) VALUES ('Geo', 'g');
INSERT INTO Dealer(name, rating) VALUES ('Honda', 'h');
INSERT INTO Dealer(name, rating) VALUES ('Isuzu', 'I');
COMMIT;

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

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

Run the following SQL script to specify a download_cursor synchronization script and record the synchronization:

CALL ml_add_table_script(
    'sis_ver1',
    'Dealer',
    'download_cursor',
    'SELECT * FROM Dealer WHERE last_modified >= ?'
);

CALL ml_add_table_script(
    'sis_ver1', 'Dealer', 'download_delete_cursor', '--{ml_ignore}'
);

COMMIT

Do not close Interactive SQL.

Results

The database schema is generated, which includes a Dealer table and a download_cursor synchronization script, and MobiLink system tables and stored procedures are installed.

Next

Proceed to Lesson 3: Creating a table to store push request.

Lesson 2: Generating a database schema

Task

See also