Lesson 2: Generate database schema

In this lesson, you generate a database schema, which includes a Dealer table, a non_sync_request table, a download_cursor synchronization script. This database schema satisfies the requirements for generating push requests.

To set up the database schema

Choose Start » Programs » SQL Anywhere 12 » Administration Tools » Sybase Central.
Perform the following tasks to connect to the consolidated database:
1. From the Connections menu, choose Connect with SQL Anywhere 12.
2. From the Action dropdown list, choose Connect with an ODBC Data Source.
3. Click ODBC Data Source Name, and click Browse.
4. Select SIS_CarDealer_LP_DBLSN_CONDB and then click OK.
5. Click Connect.

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 SIS_CarDealer_LP_DBLSN_CONDB - DBA database and click Open Interactive SQL.

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

dbisql -c "dsn=SIS_CarDealer_LP_DBLSN_CONDB"

Run the following SQL script to create and set up the Dealer and non_sync_request tables:

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

CREATE TABLE non_sync_request(
    poll_key       VARCHAR(128)
)

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 state to the ml_sis_sync_state system table:

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

CALL ml_add_connection_script(
    'CarDealer',
    'publication_nonblocking_download_ack',
    'CALL ml_set_sis_sync_state( 
        {ml s.remote_id},
        NULL,
        {ml s.publication_name},
        {ml s.username},
        NULL,
        {ml s.last_publication_download}
    )'
);

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

COMMIT;

This script sets the ml_sis_sync_state to record download-only synchronization. Recording the synchronization state allows you to reference the ml_sis_sync_state system table from the request_cursor event. You specify the request_cursor event in the next lesson.

Close Interactive SQL.

Lesson 2: Generate database schema

To set up the database schema

See also