The Oracle Lite Blog

If you have been doing Oracle Lite development, chances are you have been either using the Packaging Wizard or the Mobile Workbench to create your applications. In some cases, you have had to use the APIs to do partition mapping or altering the publication items (I will show those in another post). I on the other hand use the APIs. Why do I do this? I started doing this because the new application I was working on was estimated to have 150+ tables. In my previous experience of using the packaging wizard, publishing over a VPN line was time consuming and prone to problems. So, when I started a new project where I knew there would be a lot of tables with a very good probability that the database model would be changing quite frequently, I decided to create my own code using the APIs to manage the repository. That repository today is over 360 tables. I am now able to use that code that I wrote for one application, and use it to maintain other applications as well. It is reusable and reliable. Reliable enough that I can have a 3rd party deploy the application without the use of the Oracle Lite SDK. The only information I need to know is “what tables are changing and what are the details of the change.” That little information requires 2 minutes or less of work from my schedule.

Here is the sample of how to create a publication item and add it to your publication. In the sample, it will also show you how to add a regular index to the publication item.

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import oracle.lite.sync.ConsolidatorException;
import oracle.lite.sync.ConsolidatorManager;
public class Sample {
    public static void main(String arg[]) throws ConsolidatorException,
                                                 SQLException {
        Connection con = null;
        ConsolidatorManager cm = new ConsolidatorManager();
        String MOBILE_SCHEMA   = "mobileadmin";
        String MOBILE_PASSWORD = "password";
        String JDBC_URL        = "jdbc:oracle:thin:@localhost:1521:XE";
        String PUBLICATION     = "LITE";
        String PUB_ITEM        = "WTGPI_MYTABLE";
        String STORE_NAME      = "MYTABLE";
        String OWNER           = "MASTER";
        try {
            DriverManager.registerDriver((Driver)(Class.forName("oracle.jdbc.OracleDriver").newInstance()));
            try {
                con = DriverManager.getConnection(JDBC_URL, MOBILE_SCHEMA, MOBILE_PASSWORD);
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        cm.openConnection(con);
        PreparedStatement stmt = null;
        // If you are using a multilingual environment, this will need to be set.
        stmt = con.prepareStatement("ALTER SESSION SET NLS_LENGTH_SEMANTICS=’BYTE’");
        stmt.execute();
        // attempt to drop the publication item in the case that it already exists
        try {
            cm.dropPublicationItem(PUB_ITEM);
        } catch (ConsolidatorException ce){
            System.out.println("Drop failed: Publication Item does not exist!");
        }
        cm.createPublicationItem(
           PUB_ITEM,                // publication item name
           OWNER,                   // owner
           STORE_NAME,              // store
           "F",                     // refresh mode (F - Fast Refresh, C - Complete Refresh)
           "SELECT USER_ID, COL1, COL2, COL3 FROM MASTER.MYTABLE WHERE USER_ID = :USER_ID",  // select stmt
           null,                    // callback package owner
           null);                   // callback package name
        cm.addPublicationItem(
            PUBLICATION,
            PUB_ITEM,
            null, 

            "",                     // To make read-only, this value will be IUD
            "S",                    // Conflict rule (S - Server wins, C - Client Wins)
            null,
            1,
            null,
            true,
            false,                  // Shared map.  Set to true for shared map and make pub item read only hint: IUD"
            null);
        try {
            cm.createPublicationItemIndex(  "COL123_IDX",       // index name
                                            "WTGPI_MYTABLE",    // Publication item
                                            "I",                // I - Regular Index, U - Unique, P - Primary Key
                                            "COL1, COL2, COL3"  // Columns included in the index.
                                        );
            // Note that you should not have to create a primary key index as the
            // createPublicationItem method will handle this.
        } catch (ConsolidatorException ce){
            System.out.println("Add publication item index failed: " + ce.getMessage());
        }
        cm.resetCache();
    }
}

To compile the code, use the following:
javac -classpath %ORACLE_HOME%/mobile/classes/consolidator.jar;%ORACLE_HOME%/jdbc/lib/ojdbc14.jar;. Sample.java

Run the code using the following:
java -classpath %ORACLE_HOME%/mobile/classes/consolidator.jar;%ORACLE_HOME%/jdbc/lib/ojdbc14.jar;. Sample

Assumptions:
You have the Oracle Lite SDK installed and you have an Java JDK 1.4 or higher installed.