Introducing SQL Anywhere Studio
  Tutorial: Synchronizing Databases with MobiLink

Lesson 1: Create your databases

MobiLink synchronization requires that you have compatible consolidated and remote databases, data in database tables, and ODBC data sources for each database.

Create your database files 

The first step is to create each of the databases. In this procedure, you build a consolidated database and a remote database using the dbinit executable from the command prompt.

For more information on the dbinit utility, see Creating a database using the dbinit command-line utility.

To create your database files

1. At a command prompt, go to the directory you made called Samples\MobiLink\introducing_ML_tutorial directory.

2. Create a file for your consolidated database. At a command prompt type:

   dbinit consol.db

3. Create a file for your remote database. At a command prompt type:

   dbinit remote.db

4. Verify the successful creation of these database files by typing the following at a command prompt:

   dir

5. Your database files should appear in the directory listing. If not, review your procedures and repeat Steps 1 or 2 as needed.

Create ODBC data sources 

You are now ready to build ODBC data sources through which you can connect to your Adaptive Server Anywhere databases.

For more information on creating ODBC data sources see The Data Source utility.

To create ODBC data sources

1. Create your ODBC data source for a consolidated database by typing the following from a command prompt:

   dbdsn -w test_consol -y -c "uid=DBA;pwd=SQL;dbf=C:\MLTutorial\consol.db;eng=Consol"

2. Create an ODBC data source for a remote database by typing the following from a command prompt:

   dbdsn -w test_remote -y -c "uid=DBA;pwd=SQL;dbf=C:\MLTutorial\remote.db;eng=remote"

Now you can verify your data sources.

To verify your new data sources

1. Start the ODBC Administrator:

   • Choose Start > Programs > SQL Anywhere 9 > Adaptive Server Anywhere > ODBC Administrator.

   The ODBC Data Source Administrator appears.

2. Click the User DSN tab.

3. Scroll through the list to find your new data sources.

4. Select a data source and click Configure.

   The ODBC Configuration for Adaptive Server Anywhere dialog opens.

5. On the ODBC tab, test your data source by clicking the Test Connection button.

The MobiLink synchronization server and the MobiLink synchronization client use the ODBC data sources to connect to the consolidated and remote databases, respectively.

Populate your databases 

You can now create tables for each of your newly initialized databases by executing SQL statements in scripts using Interactive SQL. The scripts contain SQL statements that create tables in the consolidated and remote databases and insert data. The scripts also create synchronization subscriptions and publications on the remote.

To run scripts from Interactive SQL

1. Start Interactive SQL.

   • From the Start menu, choose Programs > SQL Anywhere 9 > Adaptive Server Anywhere > Interactive SQL

     or

   • From a command prompt, type dbisql.

2. Connect to the consolidated database.

3. Create a table in the consolidated database and add some rows to the table:

   • Enter the following instructions:

     CREATE TABLE cust (
        cust_id int default autoincrement primary key,
        emp_id int,
        cust_name varchar( 128 )
        );
     -- add data to cust table
     INSERT INTO cust ( emp_id, cust_name ) VALUES ( 1, 'cust1' );
     INSERT INTO cust ( emp_id, cust_name ) VALUES ( 1, 'cust2' );
     INSERT INTO cust ( emp_id, cust_name ) VALUES ( 2, 'cust3' );
     COMMIT;

4. Verify the successful creation of the table:

   • Enter the following command and verify that three rows are returned:

     SELECT * FROM cust

5. Now repeat steps 2, 3, and 4 for the remote database remote.db, using the following SQL statements. No rows are added to the table in the remote database.

   CREATE TABLE cust (cust_id int default autoincrement primary key,
      emp_id int,
      cust_name varchar( 128 )
      )

   The query SELECT * FROM cust should display no rows.