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
  1. Connect to the consolidated database:

    1. In Sybase Central, right-click SQL Anywhere 11 and choose Connect.

    2. Click the Identification tab.

    3. Click ODBC Data Source Name, and type SIS_CarDealer_LP_DBLSN_CONDB. Click OK.

  2. Start Interactive SQL:

    In the left pane, right-click the database and choose Open Interactive SQL.

  3. Execute the following statements 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)
    )
  4. 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;
  5. Execute the following command to create the MobiLink system tables and stored procedures. Replace c:\Program Files\SQL Anywhere 11\ with the location of your SQL Anywhere 11 installation.

    read "c:\Program Files\SQL Anywhere 11\MobiLink\setup\syncsa.sql"
  6. Execute the following statements 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}
        )'
    )
    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 request_cursor event from the request_cursor event. Your specify the request_cursor event in the next lesson.

  7. Close Interactive SQL.

See also