MobiLink Synchronization User's Guide
  Tutorial: Writing SQL Scripts Using Sybase Central

Lesson 1: Creating your databases

MobiLink synchronization requires that you have data in a relational database, an ODBC data source for the consolidated database, and two supported databases.

Before you start, to ensure that there are no conflicts with databases or DSNs created during other tutorials, navigate to the Samples\MobiLink\Autoscripting subdirectory of your SQL Anywhere 9 installation and run clean.bat.

Create databases 

In this procedure, you build a consolidated database and a remote database using Sybase Central.

To create your databases

1. Choose Start > Programs > Sybase SQL Anywhere 9 > Sybase Central.

   Sybase Central appears.

2. In Sybase Central, choose Tools > Adaptive Server Anywhere 9 > Create Database.

   The Create a Database wizard appears. Click Next.

3. Leave the default of Create a Database on this Computer. Click Next.

4. Enter the following filename and path for the database:

   Samples\MobiLink\Autoscripting\test_consol.db

   Click Finish.

5. Repeat steps 2 through 4 for the remote database, using the filename test_remote.db in place of test_consol.db.

Create ODBC data sources 

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

To create ODBC data sources

1. In Sybase Central, choose Tools > Adaptive Server Anywhere 9 > Open ODBC Administrator.

2. Create your ODBC data source for a database:

   • Click the User DSN tab and click Add.

     The Create New Data Source dialog appears.

   • Select Adaptive Server Anywhere 9.0 and click Finish.

     The ODBC Configuration for Adaptive Server Anywhere 9.0 dialog appears.

3. Click the ODBC tab and enter test_consol as the Data Source Name.

4. Click the Login tab and enter the User ID dba and password SQL.

5. Enter test_consol under Server Name.

6. Click the Database tab. Click Browse to locate test_consol.db.

7. Clear the Stop Database After Last Disconnect checkbox.

8. Click OK to return to the ODBC Data Source Administrator.

9. Repeat steps 2 through 8 using the name test_remote instead of test_consol for the ODBC connection name, database file name, and server name.

10. Click OK to close the ODBC Data Source Administrator.

To verify your new data sources

1. In Sybase Central, choose Tools > Adaptive Server Anywhere 9 > Open ODBC Administrator.

   The ODBC Data Source Administrator appears.

2. Click the User DSN tab.

3. Scroll through the list to find test_consol and test_remote.

4. Select each data source and click Configure.

5. Test your data source by clicking the Test Connection button.

Create your schema 

The following procedure executes SQL statements using the Interactive SQL utility to create and populate tables in the consolidated database. It also creates tables and inserts synchronization subscriptions and publications into the remote database.

The SQL files build_consol.sql and build_remote.sql are created specifically for this tutorial.

To run your scripts in Interactive SQL

1. In Sybase Central, choose Tools > Adaptive Server Anywhere 9 > Open Interactive SQL.

2. Connect to the consolidated database using the test_consol ODBC data source.

3. Choose File > Run Script.

   The Open File dialog appears. Browse to Samples\MobiLink\AutoScripting\build_consol.sql.

   The SQL statements create and populate two tables, emp and dept.

4. Verify the successful creation of each of the emp and cust tables.

   • Execute the following command in the SQL Statements pane:

     SELECT * FROM emp, cust

     The tables should be populated with data.

5. Close Interactive SQL.

6. Repeat steps 1 to 3 for the remote, using the ODBC data source test_remote and the file build_remote.sql.

   The SQL statements create, but do not populate, two tables, emp and dept. Synchronization subscriptions and publications define the synchronization parameters for the MobiLink synchronization client.

7. Verify the successful creation of each of the emp and cust tables.

   • Execute the following command in the SQL Statements pane:

     SELECT * FROM emp, cust

     The tables should contain no data.

8. Close Interactive SQL. Leave the consolidated and remote databases running.

Synchronization subscriptions and publications 

MobiLink synchronization requires a MobiLink synchronization subscription and publication. Synchronization subscriptions and publications are stored in the remote database.

Publications identify the tables and columns on your remote database that you want synchronized. These tables and columns are called articles. After you create articles for your publication, the MobiLink synchronization server uses the information contained in the publication to construct SQL statements that are used to transfer data from your remote to your consolidated database.

This tutorial uses the SQL file build_remote.sql. The SQL statements in build_remote perform the following steps to a build publication and a synchronization subscription:

• The following SQL statement creates the emp_cust synchronization publication that identifies two articles, the cust and emp tables:

  CREATE PUBLICATION emp_cust (TABLE cust, TABLE emp)

• The following SQL statement creates a synchronization user, ml_user:

  CREATE SYNCHRONIZATION SUBSCRIPTION USER ml_user

• The following SQL statement creates a synchronization subscription for ml_user to the emp_cust publication. The synchronization subscription identifies a MobiLink user, the publication name (emp_cust), and optional communication parameters.

  CREATE SYNCHRONIZATION SUBSCRIPTION TO emp_cust FOR ml_user

• The following SQL statement specifies the communication mode for the synchronization subscription as TCP/IP:

  ALTER SYNCHRONIZATION SUBSCRIPTION
  TO emp_cust FOR ml_user
  TYPE TCPIP ADDRESS 'host=localhost'

In the next lesson, you will learn how to modify these objects in Sybase Central.

Further reading 

For more information about consolidated databases, see Consolidated database.

For more information about remote databases, see MobiLink clients.

For more information about Interactive SQL, see The Interactive SQL utility.

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

For more information about defining publications and subscriptions, see Publishing data.