Lesson 2: Generating a database schema

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

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.

Set up the database schema

Click Start » Programs » SQL Anywhere 12 » Administration Tools » Sybase Central.
Perform the following tasks to connect to the consolidated database:
1. Click Connections » Connect with SQL Anywhere 12.
2. From the Action dropdown list, click Connect with an ODBC Data Source.
3. Click ODBC Data Source Name, and click Browse.
4. Select sis_cons, and then click OK.
5. 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 12\ with the location of your SQL Anywhere 12 installation.
READ "C:\Program Files\SQL Anywhere 12\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

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

Lesson 2: Generating a database schema

Set up the database schema

See also