TestINSERT.java

// 
// Lapetus Ltd Java Class.  Copyright (c) Lapetus Systems Ltd, 2009, 2010. 
// ----------------------------------------------------------------------- 
// This is the intellectual property of Lapetus Systems Ltd, Artemis, Greece. 
// -------------------------------------------------------------------------- 
// www.lapetus-ltd.com, www.lapetus.com.gr, www.lapetus.eu 
// ------------------------------------------------------- 
// $LastChangedRevision: 1190 $ 
// $LastChangedDate:: 2010-11-17 13:21:35#$ 
// ---------------------------------------- 
// 
 
import com.lapetus_ltd._2009.xml.types.*; 
import com.lapetus_ltd.api.TLptsMainDatabase; 
import com.lapetus_ltd.api.common.TLptsCryptoUtil; 
import com.lapetus_ltd.api.common.TLptsFileUtil; 
import com.lapetus_ltd.api.common.TLptsSysInfoUtil; 
import com.lapetus_ltd.api.common.logger.ILptsLogListener; 
import com.lapetus_ltd.api.common.logger.TLptsLog; 
import com.lapetus_ltd.api.common.logger.TLptsLogger; 
import com.lapetus_ltd.api.db.control.*; 
import com.lapetus_ltd.api.db.utils.TLptsDriverLoader; 
import com.lapetus_ltd.api.db.xml.types.TLptsDBConnectionType; 
import com.lapetus_ltd.api.db.xml.types.TLptsDBUpInsDelSetType; 
import com.lapetus_ltd.api.db.xml.types.TLptsDriverType; 
import junit.framework.TestCase; 
 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.util.HashMap; 
import java.util.LinkedList; 
import java.util.Map; 
import java.util.ResourceBundle; 
 
 
// ###################################################################################################### 
// ####   Test code for the dbJAPI build process. This code will stop the build process on ERROR     #### 
// ###################################################################################################### 
 
 
// 
// Class Description : This is the module for testing the INSERT capabilities of dbJAPI. 
// 
// In the code below we connect to 4 different data sources (MS SQL, MySQL, PostgreSQL and Access). 
// The data is taken from MySQL with 4 select statements (each just selecting one column), and then the data 
// is inserted into the other 3 databases. The data is then verified in the 3 databases after insertion. 
// 
// See the Javadoc of com.lapetus_ltd.api.db.xml.types.TLptsDBUpInsDelSetType for more info on 
// INSERT, UPDATE and DELETE. 
// 
 
 
public class TestINSERT extends TestCase 
{ 
  private String msSqlSelectStatementId; 
  private String mySqlSelectStatementId; 
  private String postgreSqlSelectStatementId; 
  private String accessSelectStatementId; 
 
  private String connectionMySqlId = ""; 
  private String connectionMSSqlId = ""; 
  private String connectionPostgresId = ""; 
  private String connectionMsAccessId = ""; 
 
  private int insertsComplete = 0; 
 
  LinkedList<Integer> idListMySQL = new LinkedList<Integer>(); 
  private Map<Integer, String> occupationMapMySQL = new HashMap<Integer, String>(); 
  private Map<Integer, String> genderMapMySQL = new HashMap<Integer, String>(); 
  private Map<Integer, Integer> numChildrenMapMySQL = new HashMap<Integer, Integer>(); 
 
  LinkedList<Integer> idListTest = new LinkedList<Integer>(); 
  private Map<Integer, String> occupationMapTest = new HashMap<Integer, String>(); 
  private Map<Integer, String> genderMapTest = new HashMap<Integer, String>(); 
  private Map<Integer, Integer> numChildrenMapTest = new HashMap<Integer, Integer>(); 
 
  static public void main(String[] args) 
  { 
    TestINSERT tdi = new TestINSERT(); 
    tdi.testConnections(); 
  } 
 
  public void testConnections() 
  { 
    // required so that the API can function correctly. 
    TLptsMainDatabase.init(); 
 
    // register a listener for the logging system. 
    TLptsLogger.addListener(new ILptsLogListener() 
    { 
      public void newLogGenerated(TLptsLog log) 
      { 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.ERROR)) 
        { 
          fail("LOG ERROR :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage()); 
          System.exit(0); 
        } 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.WARNING)) 
          System.out.println("LOG WARNING :" + log.getMessage() + " : " + log.getSupportingText()); 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.MESSAGE)) 
          System.out.println("LOG MESSAGE :" + log.getMessage() + " : " + log.getSupportingText()); 
      } 
    }); 
 
    // register a listener for the connections 
    TLptsFactoryConnection.addListener(new ConnectionStatementListener()); 
 
    // register a listener for the rowsets produced 
    TLptsFactoryRowSet.addListener(new RowSetListener()); 
 
    // register a listener for the statements 
    TLptsFactoryStatement.addListener(new StatementListener()); 
 
    TLptsDBConnectionType connectionType; 
 
    // Connect To PostgreSQL 
    connectionType = connectToDB("POSTGRESQL_DB"); 
    if (connectionType != null) 
    { 
      connectionPostgresId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
    // Connect To MySQL 
    connectionType = connectToDB("MYSQL_DB"); 
    if (connectionType != null) 
    { 
      connectionMySqlId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
    // Connect To To MS SQL 
    connectionType = connectToDB("MSSQL_DB"); 
    if (connectionType != null) 
    { 
      connectionMSSqlId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
    // the odbc drivers are Windows only 
    if (TLptsSysInfoUtil.isHostWindows()) 
    { 
      System.out.println("This is a Windows HOST. Able to test ODBC"); 
      connectionType = connectToDB("ACCESS_DB"); 
      if (connectionType != null) 
      { 
        connectionMsAccessId = connectionType.getId(); 
        TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
      } 
    } 
  } 
 
 
  // #################################################################################### 
  //            C O N N E C T I O N       
  // #################################################################################### 
 
  private TLptsDBConnectionType connectToDB(String title) 
  { 
    String driverName = "", dbName = "", userName = "", password = ""; 
    ResourceBundle bundle = ResourceBundle.getBundle("resources"); // we have stored all the database info in this bundle 
 
    TLptsDBConnectionType connectionType = new TLptsDBConnectionType(); 
    connectionType.setTitle(title); 
 
    if (title.equals("MYSQL_DB")) 
    { 
      dbName = bundle.getString("Testing.database.mysql.testdb2"); 
      userName = bundle.getString("Testing.database.mysql.user"); 
      password = bundle.getString("Testing.database.mysql.password"); 
      driverName = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource"; 
    } 
    if (title.equals("MSSQL_DB")) 
    { 
      dbName = bundle.getString("Testing.database.mssql.testdb1"); 
      userName = bundle.getString("Testing.database.mssql.user"); 
      password = bundle.getString("Testing.database.mssql.password"); 
      driverName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"; 
    } 
    if (title.equals("POSTGRESQL_DB")) 
    { 
      dbName = bundle.getString("Testing.database.postgresql.testdb1"); 
      userName = bundle.getString("Testing.database.postgresql.user"); 
      password = bundle.getString("Testing.database.postgresql.password"); 
      driverName = "org.postgresql.ds.PGSimpleDataSource"; 
    } 
    if (title.equals("ACCESS_DB")) 
    { 
      String fileName = TLptsFileUtil.getCurrentDirectory() + bundle.getString("Testing.database.access.testdb1"); 
      connectionType.setDataFileUrl(fileName);  // Access file from sample-data directory 
      userName = bundle.getString("Testing.database.access.user"); 
      password = bundle.getString("Testing.database.access.password"); 
      driverName = "sun.jdbc.odbc.JdbcOdbcDriver"; 
    } 
 
    XLptsDriverType driver = TLptsDriverLoader.getDriverTypeByClassName(driverName); 
 
    if (driver == null) 
    { 
      System.out.println("Could not find driver for class " + driverName); 
      return null; 
    } 
 
    TLptsDriverType driverType = new TLptsDriverType(driver); 
    // changing values that are default in the driver.loader.xml file. 
    // the DB is now set correctly and will filter through to the Datasource interface execution 
    if (!title.equals("ACCESS_DB")) 
      driverType.setValueForInterfaceFunctionParameter("setDatabaseName", "Database Name", dbName); 
 
    connectionType.setDriverType(driverType); 
 
    if (userName == null || password == null) 
      connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(driverType.getGuestUser(), 
                                                                                 driverType.getGuestPassword())); 
    else 
      connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(userName, password)); 
 
    return connectionType; 
  } 
 
 
  // #################################################################################### 
  //            I N S E R T     F U N C T I O N 
  // #################################################################################### 
 
  private void insertData() 
  { 
    /***************************************** Programmatic Insert Data **********************************************/ 
    TLptsConnection odbcConnection; 
 
    if (TLptsSysInfoUtil.isHostWindows()) 
    { 
      // Find the connection to add data to 
      odbcConnection = TLptsFactoryConnection.getConnection(connectionMsAccessId); 
      if (odbcConnection == null) 
      { 
        TLptsLogger.logError("Cannot find the MS Access Connection.", null); 
        return; 
      } 
      createInsertStatement(odbcConnection, "testTable", "windows-1253"); 
    } 
 
    /*********************************Insert Data with Lapetus_Statement or Stored Procedure*****************************/ 
    /*********************************Get Data From MySQL****************************************************************/ 
 
    TLptsConnection mySqlConnection = TLptsFactoryConnection.getConnection(connectionMySqlId); 
    if (mySqlConnection == null) 
    { 
      TLptsLogger.logError("Cannot find the MySQL Connection.", null); 
      return; 
    } 
 
    // Create Lapetus_Statement to get data for each column 
    createSelectStatement(mySqlConnection, "customers", "idcustomers"); 
    createSelectStatement(mySqlConnection, "customers", "Occupation"); 
    createSelectStatement(mySqlConnection, "customers", "Gender"); 
    createSelectStatement(mySqlConnection, "customers", "NumChildren"); 
 
    /*********************************Add Data to MS SQL Server**********************************************************/ 
    TLptsConnection msSqlConnection = TLptsFactoryConnection.getConnection(connectionMSSqlId); 
    if (msSqlConnection == null) 
    { 
      TLptsLogger.logError("Cannot find the MSSQL Connection.", null); 
      return; 
    } 
    createDynamicInsertStatement(msSqlConnection, "testTable", "UTF-16LE"); 
 
    /*********************************Add Data to PostgreSQL ************************************************************/ 
    TLptsConnection postgresConnection = TLptsFactoryConnection.getConnection(connectionPostgresId); 
    if (postgresConnection == null) 
    { 
      TLptsLogger.logError("Cannot find the PostgreSQL Connection.", null); 
      return; 
    } 
    createDynamicInsertStatement(postgresConnection, "testTable", null); 
 
    /********************************Add Data to Microsoft Office Access***********************************************/ 
    if (TLptsSysInfoUtil.isHostWindows()) 
    { 
      // Find the connection to add data 
      odbcConnection = TLptsFactoryConnection.getConnection(connectionMsAccessId); 
      if (odbcConnection == null) 
      { 
        TLptsLogger.logError("Cannot find the MS Access Connection.", null); 
        return; 
      } 
      // Create the InsertStatement. We pass the appropriate connection and the name of the table in which we want to 
      // insert data. 
      createDynamicInsertStatement(odbcConnection, "testTable", "windows-1253"); 
    } 
  } 
 
  private void createDynamicInsertStatement(TLptsConnection connection, String tableName, String charset) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.INSERT); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
 
    XLptsDBStatementTableType table = statement.getTableItemByName(tableName); 
    if (table == null) 
    { 
      fail("Could not find table : " + tableName + " for connection " + connection.getTitle()); 
    } 
    table.setSelected(true); 
 
    // this is where we use the statements created before from the MySQL DB 
    initColumn(statement, table.getTableName(), "ID", XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "idcustomers", charset); 
    initColumn(statement, table.getTableName(), "Occupation", XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "Occupation", charset); 
    initColumn(statement, table.getTableName(), "Gender", XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "Gender", charset); 
    initColumn(statement, table.getTableName(), "NumChildren", XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "NumChildren", charset); 
 
    statement.setTitle("Insert Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicInsert(statement); 
  } 
 
  private void createInsertStatement(TLptsConnection connection, String tableName, String charset) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.INSERT); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
 
    XLptsDBStatementTableType table = statement.getTableItemByName(tableName); 
    if (table == null) 
    { 
      fail("Could not find table : " + tableName + " for connection " + connection.getTitle()); 
    } 
    table.setSelected(true); 
 
    initColumn(statement, table.getTableName(), "ID", XLptsDBUpInsDelSourceType.PROGRAMMATIC, "1", charset); 
    initColumn(statement, table.getTableName(), "Occupation", XLptsDBUpInsDelSourceType.PROGRAMMATIC, "Software Developer", charset); 
    initColumn(statement, table.getTableName(), "Gender", XLptsDBUpInsDelSourceType.PROGRAMMATIC, "M", charset); 
    initColumn(statement, table.getTableName(), "NumChildren", XLptsDBUpInsDelSourceType.PROGRAMMATIC, "2", charset); 
    statement.setTitle("Insert Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicInsert(statement); 
  } 
 
  private void initColumn(TLptsStatement statement, String tableName, String columnName, 
                          XLptsDBUpInsDelSourceType sourceType, String sourceString, String charset) 
  { 
    XLptsDBStatementColumnType col = statement.getColumnItemByName(tableName, columnName); 
    if (col != null) 
    { 
      // create a new SET type and provide the source information (data supplier) 
      col.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(sourceType, sourceString)); 
 
      // If we want the db-JAPI to handle the column as another SQL Type we can set this type to this parameters like: 
      // column.getUpInsDelParameterItem().setSqlSubstituteType(java.sql.Types.BINARY); 
 
      // Note the following when it comes to updating STRING values in different types of data sources: 
      // 1. Access requires the setCharset as it cannot update STRING values directly. 
      // 2. PostgreSQL and MySQL support direct update of the string values, without setting the charset. 
      // 3. MS SQL has two types of strings, n-type (nvarchar etc) and simple types (varchar etc.). N-types 
      //    requires the setCharset(), but the normal char does not require it. 
      col.getUpInsDelParameterItem().setCharset(charset); 
    } 
  } 
 
  private void createSelectStatement(TLptsConnection connection, String tableName, String columnName) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
 
    XLptsDBStatementTableType table = statement.getTableItemByName(tableName); 
    table.setSelected(true); // select the table and all the columns 
    statement.getColumnItemByName(tableName, columnName).setSelected(true); 
 
    statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE); 
    statement.setTitle(columnName); 
 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
  } 
 
 
  public void getMySQLData() 
  { 
    TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMySqlId); 
 
    if (connection == null) 
    { 
      TLptsLogger.logError("MySQL Connection was not found", null); 
      return; 
    } 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    statement.setSqlStatementUser("SELECT * from `storesdb`.`customers`"); 
    statement.setSqlStatementModified(true); 
    statement.setTitle("Get MySQL Data"); 
    mySqlSelectStatementId = statement.getId(); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
  } 
 
  private void getMsSqlData() 
  { 
    TLptsConnection connection = null; 
    for (TLptsConnection con : TLptsFactoryConnection.getConnectionList()) 
      if (con.getId().equals(connectionMSSqlId)) 
        connection = con; 
 
    if (connection == null) 
      TLptsLogger.logError("MS SQL Connection was not found or is null", null); 
 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    statement.setSqlStatementUser("SELECT * FROM [insurance_policy].[dbo].[testTable]"); 
    statement.setSqlStatementModified(true); 
    statement.setTitle("Get Ms SQL Data"); 
    msSqlSelectStatementId = statement.getId(); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
  } 
 
  private void getAccessData() 
  { 
    checkData("PostgreSQL"); 
 
    if (TLptsSysInfoUtil.isHostWindows()) 
    { 
      TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMsAccessId); 
      if (connection == null) 
        TLptsLogger.logError("Access Connection was not found or is null", null); 
 
      TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
      statement.setSqlStatementUser("SELECT \"testTable.ID\", \"testTable.Occupation\", \"testTable.Gender\", " + 
                                    "\"testTable.NumChildren\" FROM \"testTable\""); 
      statement.setSqlStatementModified(true); 
      statement.setTitle("Get Access Data"); 
      accessSelectStatementId = statement.getId(); 
      statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
      System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
      TLptsFactoryStatement.createNewStatement(connection, statement); 
    } 
  } 
 
  private void getPostgreSqlData() 
  { 
    checkData("MS SQL"); 
 
    TLptsConnection connection = null; 
    for (TLptsConnection con : TLptsFactoryConnection.getConnectionList()) 
      if (con.getId().equals(connectionPostgresId)) 
        connection = con; 
 
    if (connection == null) 
      TLptsLogger.logError("PostgreSQL Connection was not found or is null", null); 
 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    statement.setSqlStatementUser("SELECT * FROM \"testTable\""); 
    statement.setSqlStatementModified(true); 
    statement.setTitle("Get PostgreSQL Data"); 
    postgreSqlSelectStatementId = statement.getId(); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
  } 
 
//  ############################################################################################################### 
//  ####                    V E R I F I C A T I O N    F U N C T I O N S 
//  ############################################################################################################### 
 
  private void checkData(String db) 
  { 
    System.out.println("***************************************************"); 
    System.out.println("  Verifying inserted data against selected data"); 
    System.out.println("***************************************************"); 
 
    if (occupationMapMySQL.isEmpty()) 
      TLptsLogger.logError("Occupation Map for MySQL is Empty", null); 
    if (genderMapMySQL.isEmpty()) 
      TLptsLogger.logError("Gender Map for MySQL is Empty", null); 
    if (numChildrenMapMySQL.isEmpty()) 
      TLptsLogger.logError("NumChildren Map for MySQL is Empty", null); 
 
    if (occupationMapTest.isEmpty()) 
      TLptsLogger.logError("Occupation Map for " + db + " is Empty", null); 
    if (genderMapTest.isEmpty()) 
      TLptsLogger.logError("Gender Map for " + db + " is Empty", null); 
    if (numChildrenMapTest.isEmpty()) 
      TLptsLogger.logError("NumChildren Map for " + db + " is Empty", null); 
 
    String occupationMySql; 
    String occupationTest; 
    String genderMySql; 
    String genderTest; 
    Integer numChildrenMySql; 
    Integer numChildrenTest; 
 
    for (Integer id : idListMySQL) 
    { 
      occupationMySql = occupationMapMySQL.get(id); 
      occupationTest = occupationMapTest.get(id); 
      genderMySql = genderMapMySQL.get(id); 
      genderTest = genderMapTest.get(id); 
      numChildrenMySql = numChildrenMapMySQL.get(id); 
      numChildrenTest = numChildrenMapTest.get(id); 
 
      if (!idListTest.contains(id)) 
        TLptsLogger.logError(db + " does not contain id " + id, null); 
      else 
        idListTest.remove(id); 
 
      if (occupationMySql == null) 
        TLptsLogger.logError("My SQL occupation for id " + id + " is null", null); 
      else 
      { 
        if (occupationTest == null) 
          TLptsLogger.logError(db + " occupation for id " + id + " is null", null); 
        else 
        { 
          if (!occupationMySql.equals(occupationTest)) 
            TLptsLogger.logError("Occupation for id " + id + " does not match", null); 
          else 
            occupationMapTest.remove(id); 
        } 
      } 
 
      if (genderMySql == null) 
        TLptsLogger.logError("My SQL gender for id " + id + " is null", null); 
      else 
      { 
        if (genderTest == null) 
          TLptsLogger.logError(db + " gender for id " + id + " is null", null); 
        else 
        { 
          if (!genderMySql.equals(genderTest)) 
          { 
            if (genderTest.equals(" ")) 
              genderTest = ""; 
            if (!genderMySql.equals(genderTest)) 
              TLptsLogger.logError("Gender for id " + id + " does not match", null); 
            else 
              genderMapTest.remove(id); 
          } else 
            genderMapTest.remove(id); 
        } 
      } 
 
      if (numChildrenMySql == null) 
        TLptsLogger.logError("My SQL numChildren for id " + id + " is null", null); 
      else 
      { 
        if (numChildrenTest == null) 
          TLptsLogger.logError(db + " numChildren for id " + id + " is null", null); 
        else 
        { 
          if (!numChildrenMySql.equals(numChildrenTest)) 
            TLptsLogger.logError("NumChildren for id " + id + " does not match", null); 
          else 
            numChildrenMapTest.remove(id); 
        } 
      } 
    } 
 
    if (db.equals("Access")) 
    { 
      if (idListTest.contains(1)) 
        idListTest.remove(new Integer(1)); 
      if (occupationMapTest.get(1) != null) 
        occupationMapTest.remove(1); 
      if (genderMapTest.get(1) != null) 
        genderMapTest.remove(1); 
      if (numChildrenMapTest.get(1) != null) 
        numChildrenMapTest.remove(1); 
    } 
 
    if (!idListTest.isEmpty()) 
      TLptsLogger.logError("Id List for " + db + " is not Empty", null); 
    if (!occupationMapTest.isEmpty()) 
      TLptsLogger.logError("Occupation Map for " + db + " is not Empty", null); 
    if (!genderMapTest.isEmpty()) 
      TLptsLogger.logError("Gender Map for " + db + " is not Empty", null); 
    if (!numChildrenMapTest.isEmpty()) 
      TLptsLogger.logError("NumChildren Map for " + db + " is not Empty", null); 
 
    System.out.println("***************************************************"); 
    System.out.println("Data verified and passed for : " + db); 
    System.out.println("***************************************************"); 
 
    if (db.equals("Access") || (TLptsSysInfoUtil.isHostWindows() && db.equals("PostgreSQL"))) 
    { 
      System.out.println("All test passed successfully..."); 
      System.exit(0); 
    } 
  } 
 
//  ############################################################################################################### 
//  ####             C O N N E C T I O N      L I S T E N E R 
//  ############################################################################################################### 
 
  private class ConnectionStatementListener implements ILptsFactoryConnectionListener 
  { 
    public synchronized void newConnectionCreated(TLptsConnection connection) 
    { 
      System.out.println("New Connection created successfully. Statements can be processed."); 
      if ((TLptsFactoryConnection.getConnectionListSize() == 4 && TLptsSysInfoUtil.isHostWindows()) || 
          (TLptsFactoryConnection.getConnectionListSize() == 3 && !TLptsSysInfoUtil.isHostWindows())) 
        insertData(); 
    } 
 
    public void newConnectionProcessStarted() 
    { 
      System.out.println("Connecting..."); 
    } 
 
    public void newConnectionFailed(TLptsLog log) 
    { 
      System.out.println("New Connection Failed. The logger got this."); 
    } 
 
    public void removedAndClosedConnection(TLptsConnection connection) 
    { 
      System.out.println("Connection removed from Connection Manager and closed successfully ... " + connection.getTitle()); 
    } 
  } 
 
 
//  ############################################################################################################### 
//  ####                   S T A T E M E N T       L I S T E N E R 
//  ############################################################################################################### 
 
  private class StatementListener implements ILptsFactoryStatementListener 
  { 
    public void newStatementProcessStarted(TLptsConnection connection) 
    { 
    } 
 
    public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) 
    { 
      // executes only the SELECT statements for verification 
      if (statement.getId().equals(postgreSqlSelectStatementId) || 
          statement.getId().equals(msSqlSelectStatementId) || 
          statement.getId().equals(mySqlSelectStatementId) || 
          statement.getId().equals(accessSelectStatementId)) 
        TLptsFactoryRowSet.executeSelectStatement(statement, 1, 0, false); 
    } 
 
    public void newStatementFailed(TLptsConnection connection) 
    { 
      TLptsLogger.logError("Fail to Create Statement for connection " + connection.getTitle(), null); 
    } 
 
    public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement) 
    { 
    } 
  } 
 
 
//  ############################################################################################################### 
//  ####                    R O W S E T      L I S T E N E R 
//  ############################################################################################################### 
 
  private class RowSetListener implements ILptsFactoryRowSetListener 
  { 
    public void rowEvent(TLptsRowEvent rowEvent) 
    { 
    } 
 
    public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) 
    { 
      if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.INSERT_COMPLETE) 
      { 
        System.out.println("Statement " + rowSetEvent.getStatement().getTitle() + " " + 
                           ((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()).getTitle() + " insert execution complete..."); 
 
        System.out.println(rowSetEvent.getRows() + " rows inserted, " + rowSetEvent.getObjects() + " objects inserted"); 
 
        if (rowSetEvent.getRowSetsNotFinished().isEmpty()) 
          System.out.println("All row sets completed in sync."); 
        else 
          for (String rowSet : rowSetEvent.getRowSetsNotFinished()) 
            System.out.println(rowSet + " did not complete in sync."); 
 
        insertsComplete++; 
        if (insertsComplete == 4 || (insertsComplete == 3 && !TLptsSysInfoUtil.isHostWindows())) 
        { 
          System.out.println("############################"); 
          System.out.println("# Process Insert Completed #"); 
          System.out.println("############################"); 
          // after all the inserts, we can start the verification 
          getMySQLData(); 
        } 
      } 
 
      if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.ERROR_OCCURRED) 
      { 
        System.out.println("The insert was not successful. We need to check the output from the logger for more information:"); 
        for (TLptsLog log : TLptsLogger.getLogList(false)) 
          if (log.getType().equals(TLptsLogger.LOG_TYPE.ERROR)) 
            System.out.println("LOG ERROR : " + log.getMessage() + " : " + log.getSupportingText() + " : " + log.getExceptionMessage()); 
        fail(); 
      } 
 
      if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.NEW_SELECT_RESULTSET && 
          (insertsComplete == 4 || (insertsComplete == 3 && !TLptsSysInfoUtil.isHostWindows()))) 
      { 
        ResultSet rs = rowSetEvent.getRowSet(); 
        try 
        { 
          if (rowSetEvent.getStatement().getId().equals(mySqlSelectStatementId)) 
          { 
            while (rs.next()) 
            { 
              idListMySQL.add(rs.getInt(1)); 
              occupationMapMySQL.put(rs.getInt(1), rs.getString(2)); 
              genderMapMySQL.put(rs.getInt(1), rs.getString(3)); 
              numChildrenMapMySQL.put(rs.getInt(1), rs.getInt(4)); 
            } 
            getMsSqlData(); 
          } else if (rowSetEvent.getStatement().getId().equals(msSqlSelectStatementId)) 
          { 
            while (rs.next()) 
            { 
              idListTest.add(rs.getInt(1)); 
              occupationMapTest.put(rs.getInt(1), rs.getString(2)); 
              genderMapTest.put(rs.getInt(1), rs.getString(3)); 
              numChildrenMapTest.put(rs.getInt(1), rs.getInt(4)); 
            } 
            getPostgreSqlData(); 
          } else if (rowSetEvent.getStatement().getId().equals(postgreSqlSelectStatementId)) 
          { 
            while (rs.next()) 
            { 
              idListTest.add(rs.getInt(1)); 
              occupationMapTest.put(rs.getInt(1), rs.getString(2)); 
              genderMapTest.put(rs.getInt(1), rs.getString(3)); 
              numChildrenMapTest.put(rs.getInt(1), rs.getInt(4)); 
            } 
            getAccessData(); 
          } else if (rowSetEvent.getStatement().getId().equals(accessSelectStatementId)) 
          { 
            TLptsRowSet.ResultSetObjectLists rsol; 
            TLptsRowSet rowSet = new TLptsRowSet(rowSetEvent.getStatement(), rs); 
            rowSet.beforeFirst(); 
            while (rowSet.next()) 
            { 
              rsol = rowSet.getCurrentRowObjectLists(); 
              idListTest.add((Integer) rsol.getObjectList().get(0)); 
              occupationMapTest.put((Integer) rsol.getObjectList().get(0), rsol.getObjectList().get(1).toString()); 
              genderMapTest.put((Integer) rsol.getObjectList().get(0), rsol.getObjectList().get(2).toString()); 
              numChildrenMapTest.put((Integer) rsol.getObjectList().get(0), (Integer) rsol.getObjectList().get(3)); 
            } 
            checkData("Access"); 
          } 
        } catch (SQLException sqle) 
        { 
          TLptsLogger.logError("SQL Exception", sqle.getLocalizedMessage(), sqle); 
        } catch (Exception e) 
        { 
          TLptsLogger.logError("Exception", e); 
        } 
      } 
      return false; 
    } 
  } 
}