Creating a Synchronized System
Page 5 of 6

Sample Code for Creating a Synchronized System

NOTE: TThis example is an altered and simplified subset of the application described in Chapter 7, "Designing Synchronized Applications".

This small piece of code creates a complete synchronized system consisting of one source database and one target database. The target database is designed to contain a subset of all orders: just those placed by sales representative number two.

After these databases are created, a target user can issue a single command (REFRESH) to synchronize any target changes to the source, or source changes to the target. ( Chapter 4, "Synchronizing Databases", explains how.)

In this example, the source is on a Cloudconnector server, Cloudscape's connectivity software. For simplicity's sake, the source and target are assumed to be on the same network. This example uses Windows-style path names.

//Start the Cloudconnector driver
Class.forName("COM.cloudscape.core.WebLogicDriver").newInstance();

//Create the source database and connect to it using a Cloudconnect server
Connection conn = DriverManager.getConnection
    ("jdbc:cloudscape:weblogic:toysSourceDB;createSource=true");

//Create a statement in the source database
Statement s = conn.createStatement();

/* Create TOYS and ORDERS tables in the source database.   
/* (Note that tables to be published must contain a primary key).
 */
s.execute("CREATE TABLE toys " +
    "(toy VARCHAR(30) CONSTRAINT toys_pk PRIMARY KEY, " +
    "price DECIMAL(5,2), " +
    "quantity INT)");
s.execute("CREATE TABLE orders " +
    "(orderID INT CONSTRAINT orders_pk PRIMARY KEY, " +
    "salesRepID INT, " +
    "customer VARCHAR(50), " +
    "toy VARCHAR(50), " +
    "quantity INT CONSTRAINT enoughToys CHECK (quantity >= 0)");

//Populate the TOYS table
s.execute("INSERT INTO toys VALUES ('barbie',9.50,1)");
s.execute("INSERT INTO toys VALUES ('beanie baby elephant',6.50,0)");

/*Create a publication named ToyOrders that contains TOYS and ORDERS 
 *(primary keys are implicitly published).  All columns from both
 * tables are published, and a row filter (WHERE clause) is specified for
 * the ORDERS table.
 */ 
s.execute("CREATE PUBLICATION ToyOrders (salesRepID INT) " +
    "ADD TABLE toys " +
    "ADD TABLE orders " +
    "WHERE (salesRepID = ?salesRepID)");

//Copy the publication to a file, specifying parameters entered by user
s.execute("COPY PUBLICATION ToyPub (salesRepID = 2) " +
    " TO FILE 'c:\\MyDBs\\toysFile.copy'");

//Close the connection to the source database
conn.close();

//Start the JDBC embedded driver
Class.forName("COM.cloudscape.core.JDBCDriver").newInstance();

/* Create a target database using the copy file, and connect to the
 * target database.
 * Make sure not to introduce blank spaces in the URL.
 */
Connection conn2 = DriverManager.getConnection
    ("jdbc:cloudscape:toysTargetDB;" +
    "createTargetFrom=c:\\MyDBs\\toysFile.copy");

//Close the connection to the target database
conn2.close();

//You now have a synchronizable, read-write target database.

Because the toysFile copyfile was created using the parameter `salesRepID=2', refreshes at the resulting ToysTargetDB database will receive only those ORDERS rows from the source that contain a value of `2' in the SalesRepID column.