TestSqlOperationsOnODBCAdvanced.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: 1203 $ 
// $LastChangedDate:: 2010-11-28 09:35:04#$ 
// ---------------------------------------- 
// 
 
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.*; 
import junit.framework.TestCase; 
 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.sql.Timestamp; 
import java.util.*; 
 
 
// ###################################################################################################### 
// ####   Test code for the dbJAPI build process. This code will stop the build process on ERROR     #### 
// ###################################################################################################### 
 
// 
// Class Description : This is one of the test operations modules. 
// 
// 
// This module tests OPERATIONS (=,<,>,<>,etc) against many different data types. 
// This class creates tables, inserts data based on criteria, selects the data with operations, updates with criteria, 
// deletes with criteria and verifies the data integrity. 
// 
// The tests in here are run against an ODBC connection, but there are other similar modules for Oracle, Postgres, MySQL and MS-SQL. 
// 
// The test starts by connecting {@link #connectToDB} and creating a main table {@link #createAllTableStatements} through 
// {@link ConnectionStatementListener} called "odbctable" and 10 other tables that only have two columns (ID and TYPE) 
// {@link #createIntegerTableStatement}. 
// 
// After the creation of the tables, the INSERT statement is executed {@link #insertDataIntoAllTables} and data is inserted 
// into the main table. At this point maps are generated with the inserted values for later selection and comparison. 
// 
// On completion of the INSERT {@link RowSetListener#processNewRowSetRows} new SELECT statements are generated 
// {@link #selectColumnStatement} for every column of the main table "odbctable". 
// After all the selects have been created, data is inserted into all the tables from the selected statements 
// {@link #insertDataInMultipleTables}. 
// 
// Thereafter multiple new dynamic statements are generated {@link #dynamicStatement} through {@link RowSetListener#processNewRowSetRows}. 
// These dynamic statements are for the separate type tables (10 of them), which are declared as primary statements to the main table.. 
// As these dynamic statements are being executed, each row is checked against the maps used to generate the main table. 
// {@link RowSetListener#rowEvent}. 
//  
// After completion of the verification for every dynamic statement, the update procedure commences {@link #updateData}. 
// This updates 4 columns using many operations from multiple columns with criteria. 
// The updated data is then selected and the expected data is verified {@link #selectAfterUpdateStatement} and {@link RowSetListener#rowEvent}. 
// 
// This whole procedure uses most of the capabilities of dbJAPI, and controls the whole situation with the 3 listeners 
// for Connections, Statements and RowSets. The listeners are utilised in various ways. by many separate threads, 
// with no synchronisation required by the application (dbJAPI takes care of all that). 
// 
 
 
public class TestSqlOperationsOnODBCAdvanced extends TestCase 
{ 
  private String connectionCreateTableOdbcId = ""; 
  private String selectAfterUpdateStatementId = ""; 
  private String selectAfterDeleteStatementId = ""; 
  private String connectionOdbcId = ""; 
  private String selectStatementId = ""; 
 
  private List<Integer> idList = new LinkedList<Integer>(); 
  private Map<Integer, Integer> integerMap = new HashMap<Integer, Integer>(); 
  private Map<Integer, String> varcharacterMap = new HashMap<Integer, String>(); 
  private Map<Integer, Timestamp> timestampMap = new HashMap<Integer, Timestamp>(); 
  private Map<Integer, Double> doubleMap = new HashMap<Integer, Double>(); 
  private Map<Integer, Short> smallintegerMap = new HashMap<Integer, Short>(); 
  private Map<Integer, Boolean> bitMap = new HashMap<Integer, Boolean>(); 
 
  private List<Integer> idListSelected = new LinkedList<Integer>(); 
  private Map<Integer, Integer> integerMapSelected = new HashMap<Integer, Integer>(); 
  private Map<Integer, String> varcharacterMapSelected = new HashMap<Integer, String>(); 
  private Map<Integer, Timestamp> timestampMapSelected = new HashMap<Integer, Timestamp>(); 
  private Map<Integer, Double> doubleMapSelected = new HashMap<Integer, Double>(); 
  private Map<Integer, Short> smallintegerMapSelected = new HashMap<Integer, Short>(); 
  private Map<Integer, Boolean> bitMapSelected = new HashMap<Integer, Boolean>(); 
 
  private String idStatementId = ""; 
  private String integerStatementId = ""; 
  private String varcharStatementId = ""; 
  private String timestampStatementId = ""; 
  private String doubleStatementId = ""; 
  private String smallIntegerStatementId = ""; 
  private String bitStatementId = ""; 
 
  private String primaryStatementId = ""; 
  private String subStatementId = ""; 
 
  private int insertions = 0; 
  private int createdTables = 0; 
  private int dynamicIndex = 0; 
  private boolean isSubstitute = false; 
 
  private int NO_OF_INSERTIONS = 100; 
  private int MULTIPLE_LIMIT = 106; 
 
  private List<String> columnNames = new LinkedList<String>(); 
  private List<String> tableNames = new LinkedList<String>(); 
 
  static public void main(String[] args) 
  { 
    TestSqlOperationsOnODBCAdvanced sqlOperationsOnAdvanced = new TestSqlOperationsOnODBCAdvanced(); 
    sqlOperationsOnAdvanced.testOperations(); 
  } 
 
  public void testOperations() 
  { 
    // 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)) 
        { 
          cleanUp(false); 
          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()); 
    columnAndTableNames(); 
 
    TLptsDBConnectionType connectionType; 
 
    //Connect To ODBC 
    if (TLptsSysInfoUtil.isHostWindows()) 
    { 
      ResourceBundle bundle = ResourceBundle.getBundle("resources"); // we have stored all the database info in here 
      System.out.println("This is a Windows HOST. Able to test ODBC"); 
      connectionType = connectToDB(); 
      if (connectionType != null) 
      { 
        connectionCreateTableOdbcId = connectionType.getId(); 
        TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
      } 
    } 
 
  } 
 
 
  // #################################################################################### 
  //            C O N N E C T I O N 
  // #################################################################################### 
 
  private TLptsDBConnectionType connectToDB() 
  { 
    String driverName = "", userName = "", password = ""; 
    ResourceBundle bundle = ResourceBundle.getBundle("resources"); // we have stored all the database info in this bundle 
 
    TLptsDBConnectionType connectionType = new TLptsDBConnectionType(); 
    connectionType.setTitle("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; 
    } 
    connectionType.setDriverType(driver); 
 
    connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(userName, password)); 
 
    return connectionType; 
  } 
 
 
  private void createIntegerTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create integerTable"); 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("integerTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType integerColumn = new TLptsDBCreateStatementColumnType(); 
    integerColumn.setColumn("integerColumn"); 
    TLptsDBCreateStatementColumnSqlType integerSqlType = new TLptsDBCreateStatementColumnSqlType(integerColumn); 
    integerSqlType.setSqlType(java.sql.Types.INTEGER); 
    integerColumn.setSqlType(integerSqlType); 
    table.addColumn(integerColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createVarcharTableStatement(connection); 
  } 
 
  private void createVarcharTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create varcharTable"); 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("varcharTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType varcharColumn = new TLptsDBCreateStatementColumnType(); 
    varcharColumn.setColumn("varcharColumn"); 
    TLptsDBCreateStatementColumnSqlType varcharSqlType = new TLptsDBCreateStatementColumnSqlType(varcharColumn); 
    varcharSqlType.setSqlType(java.sql.Types.VARCHAR); 
    varcharColumn.setSqlType(varcharSqlType); 
    TLptsDBCreateStatementColumnSizeType varcharSize = new TLptsDBCreateStatementColumnSizeType(varcharColumn); 
    varcharSize.setSize1("50"); 
    varcharColumn.setSizes(varcharSize); 
    table.addColumn(varcharColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createTimestampTableStatement(connection); 
  } 
 
  private void createTimestampTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create timestampTable"); 
 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("timestampTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType timestampColumn = new TLptsDBCreateStatementColumnType(); 
    timestampColumn.setColumn("timestampColumn"); 
    TLptsDBCreateStatementColumnSqlType timestampSqlType = new TLptsDBCreateStatementColumnSqlType(timestampColumn); 
    timestampSqlType.setSqlType(java.sql.Types.TIMESTAMP); 
    timestampColumn.setSqlType(timestampSqlType); 
    table.addColumn(timestampColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createDoubleTableStatement(connection); 
  } 
 
  private void createDoubleTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create doubleTable"); 
 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("doubleTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType doubleColumn = new TLptsDBCreateStatementColumnType(); 
    doubleColumn.setColumn("doubleColumn"); 
    TLptsDBCreateStatementColumnSqlType doubleColumnSqlType = new TLptsDBCreateStatementColumnSqlType(doubleColumn); 
    doubleColumnSqlType.setSqlType(java.sql.Types.DOUBLE); 
    doubleColumn.setSqlType(doubleColumnSqlType); 
    table.addColumn(doubleColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createSmallIntegerTableStatement(connection); 
  } 
 
  private void createSmallIntegerTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create smallIntegerTable"); 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("smallIntegerTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType smallintegerColumn = new TLptsDBCreateStatementColumnType(); 
    smallintegerColumn.setColumn("smallintegerColumn"); 
    TLptsDBCreateStatementColumnSqlType smallintegerColumnSqlType = new TLptsDBCreateStatementColumnSqlType(smallintegerColumn); 
    smallintegerColumnSqlType.setSqlType(java.sql.Types.SMALLINT); 
    smallintegerColumn.setSqlType(smallintegerColumnSqlType); 
 
    table.addColumn(smallintegerColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createBitTableStatement(connection); 
  } 
 
  private void createBitTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create bitTable"); 
 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("bitTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType bitColumn = new TLptsDBCreateStatementColumnType(); 
    bitColumn.setColumn("bitColumn"); 
    TLptsDBCreateStatementColumnSqlType bitColumnSqlType = new TLptsDBCreateStatementColumnSqlType(bitColumn); 
    bitColumnSqlType.setSqlType(java.sql.Types.BIT); 
    bitColumn.setSqlType(bitColumnSqlType); 
 
    table.addColumn(bitColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
  } 
 
  private void createAllTableStatements(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create odbcTable"); 
 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("odbcTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType integerColumn = new TLptsDBCreateStatementColumnType(); 
    integerColumn.setColumn("integerColumn"); 
    TLptsDBCreateStatementColumnSqlType integerSqlType = new TLptsDBCreateStatementColumnSqlType(integerColumn); 
    integerSqlType.setSqlType(java.sql.Types.INTEGER); 
    integerColumn.setSqlType(integerSqlType); 
    table.addColumn(integerColumn); 
 
    TLptsDBCreateStatementColumnType varcharColumn = new TLptsDBCreateStatementColumnType(); 
    varcharColumn.setColumn("varcharColumn"); 
    TLptsDBCreateStatementColumnSqlType varcharSqlType = new TLptsDBCreateStatementColumnSqlType(varcharColumn); 
    varcharSqlType.setSqlType(java.sql.Types.VARCHAR); 
    varcharColumn.setSqlType(varcharSqlType); 
    TLptsDBCreateStatementColumnSizeType varcharSize = new TLptsDBCreateStatementColumnSizeType(varcharColumn); 
    varcharSize.setSize1("50"); 
    varcharColumn.setSizes(varcharSize); 
    table.addColumn(varcharColumn); 
 
    TLptsDBCreateStatementColumnType timestampColumn = new TLptsDBCreateStatementColumnType(); 
    timestampColumn.setColumn("timestampColumn"); 
    TLptsDBCreateStatementColumnSqlType timestampSqlType = new TLptsDBCreateStatementColumnSqlType(timestampColumn); 
    timestampSqlType.setSqlType(java.sql.Types.TIMESTAMP); 
    timestampColumn.setSqlType(timestampSqlType); 
    table.addColumn(timestampColumn); 
 
    TLptsDBCreateStatementColumnType doubleColumn = new TLptsDBCreateStatementColumnType(); 
    doubleColumn.setColumn("doubleColumn"); 
    TLptsDBCreateStatementColumnSqlType doubleColumnSqlType = new TLptsDBCreateStatementColumnSqlType(doubleColumn); 
    doubleColumnSqlType.setSqlType(java.sql.Types.DOUBLE); 
    doubleColumn.setSqlType(doubleColumnSqlType); 
    table.addColumn(doubleColumn); 
 
    TLptsDBCreateStatementColumnType smallintegerColumn = new TLptsDBCreateStatementColumnType(); 
    smallintegerColumn.setColumn("smallintegerColumn"); 
    TLptsDBCreateStatementColumnSqlType smallintegerColumnSqlType = new TLptsDBCreateStatementColumnSqlType(smallintegerColumn); 
    smallintegerColumnSqlType.setSqlType(java.sql.Types.SMALLINT); 
    smallintegerColumn.setSqlType(smallintegerColumnSqlType); 
    table.addColumn(smallintegerColumn); 
 
    TLptsDBCreateStatementColumnType bitColumn = new TLptsDBCreateStatementColumnType(); 
    bitColumn.setColumn("bitColumn"); 
    TLptsDBCreateStatementColumnSqlType bitColumnSqlType = new TLptsDBCreateStatementColumnSqlType(bitColumn); 
    bitColumnSqlType.setSqlType(java.sql.Types.BIT); 
    bitColumn.setSqlType(bitColumnSqlType); 
    table.addColumn(bitColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createIntegerTableStatement(connection); 
  } 
 
 
//  ############################################################################################################### 
//  ####                  I N S E R T       F U N C T I O N S 
//  ############################################################################################################### 
 
  private void insertDataIntoAllTables(TLptsConnection connection) 
  { 
    for (int i = 0; i < NO_OF_INSERTIONS; i++) 
    { 
      idList.add(i); 
      integerMap.put(i, new Integer("1354" + i)); 
      varcharacterMap.put(i, "my var character" + i + "column"); 
      timestampMap.put(i, new Timestamp(110, i % 12, i % 28, i % 24, i % 60, i % 33, i)); 
      doubleMap.put(i, new Double("158" + i + "12.25")); 
      smallintegerMap.put(i, new Short("" + i)); 
      bitMap.put(i, i % 2 == 0); 
      insertStatement(connection, i); 
    } 
  } 
 
  private void insertStatement(TLptsConnection connection, Integer index) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.INSERT); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName("odbcTable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnId = statement.getColumnItemByName("odbcTable", "id"); 
    if (columnId != null) 
      columnId.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, idList.get(index).toString())); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("odbcTable", "integerColumn"); 
    if (columnInteger != null) 
      columnInteger.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, integerMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnVarchar = statement.getColumnItemByName("odbcTable", "varcharColumn"); 
    if (columnVarchar != null) 
      columnVarchar.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, varcharacterMap.get(index))); 
 
    XLptsDBStatementColumnType columnTimestamp = statement.getColumnItemByName("odbcTable", "timestampColumn"); 
    if (columnTimestamp != null) 
      columnTimestamp.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, timestampMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnDouble = statement.getColumnItemByName("odbcTable", "doubleColumn"); 
    if (columnDouble != null) 
      columnDouble.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, doubleMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnSmallInt = statement.getColumnItemByName("odbcTable", "smallintegerColumn"); 
    if (columnSmallInt != null) 
      columnSmallInt.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, smallintegerMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnBit = statement.getColumnItemByName("odbcTable", "bitColumn"); 
    if (columnBit != null) 
      columnBit.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, bitMap.get(index).toString())); 
 
    statement.setTitle("Insert Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicInsert(statement); 
  } 
 
  private void selectColumnStatement(TLptsConnection connection) 
  { 
    createStatement(connection, "id"); 
    createStatement(connection, "integerColumn"); 
    createStatement(connection, "varcharColumn"); 
    createStatement(connection, "timestampColumn"); 
    createStatement(connection, "doubleColumn"); 
    createStatement(connection, "smallintegerColumn"); 
    createStatement(connection, "bitColumn"); 
  } 
 
  private void createStatement(TLptsConnection connection, String columnName) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    // set the table and columns for selecting 
    XLptsDBStatementTableType table = statement.getTableItemByName("odbcTable"); 
    table.setSelected(true); 
    statement.getColumnItemByName("odbcTable", columnName).setSelected(true); 
 
    statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE); 
    if (columnName.equals("id")) 
      idStatementId = statement.getId(); 
    else if (columnName.equals("integerColumn")) 
      integerStatementId = statement.getId(); 
    else if (columnName.equals("varcharColumn")) 
      varcharStatementId = statement.getId(); 
    else if (columnName.equals("timestampColumn")) 
      timestampStatementId = statement.getId(); 
    else if (columnName.equals("doubleColumn")) 
      doubleStatementId = statement.getId(); 
    else if (columnName.equals("smallintegerColumn")) 
      smallIntegerStatementId = statement.getId(); 
    else if (columnName.equals("bitColumn")) 
      bitStatementId = statement.getId(); 
    // Set the name of the statement to the name of the column so that it makes sense 
    statement.setTitle(columnName); 
    statement.setExecutable(false); 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
  } 
 
 
  private void insertDataInMultipleTables(TLptsConnection connection) 
  { 
    setMultipleInserts(connection, "integerTable", "integerColumn", integerStatementId); 
    setMultipleInserts(connection, "varcharTable", "varcharColumn", varcharStatementId); 
    setMultipleInserts(connection, "timestampTable", "timestampColumn", timestampStatementId); 
    setMultipleInserts(connection, "doubleTable", "doubleColumn", doubleStatementId); 
    setMultipleInserts(connection, "smallIntegerTable", "smallintegerColumn", smallIntegerStatementId); 
    setMultipleInserts(connection, "bitTable", "bitColumn", bitStatementId); 
  } 
 
  private void setMultipleInserts(TLptsConnection connection, String tableName, String columnName, String statementId) 
  { 
    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) 
      TLptsLogger.logError("Table not Found", null); 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnId = statement.getColumnItemByName(tableName, "id"); 
    if (columnId != null) 
      columnId.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT, TLptsFactoryStatement.getStatement(idStatementId).getTitle())); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName(tableName, columnName); 
    if (columnInteger != null) 
      columnInteger.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT, TLptsFactoryStatement.getStatement(statementId).getTitle())); 
 
    statement.setTitle("Insert " + columnName + " Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicInsert(statement); 
  } 
 
  private void dynamicStatement(TLptsConnection connection, String tableName, String columnName, boolean isSubstitute) 
  { 
    if (tableName.equals("bitTable")) 
      selectDynamicData(2); 
    else 
      selectDynamicData(NO_OF_INSERTIONS); 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    statement.getTableItemByName(tableName).setSelected(true); 
    statement.getColumnItemByName(tableName, columnName).setSelected(true); 
    statement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
 
    if (tableName.equals("tinyIntegerTable") || tableName.equals("charTable") || tableName.equals("bitTable")) 
    { 
      TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType(); 
      spit.setColumnItem(statement.getColumnItemByName(tableName, "id")); 
      spit.setOperation(TLptsFactoryStatement.OPERATION_LESS_THAN); 
      if (tableName.equals("bitTable")) 
        spit.addStaticStringListItem("2"); 
      ((TLptsDBStatementCriteriaType) statement.getCriteriaType()).addCriteriaListItem(spit); 
    } 
 
    statement.setTitle("Primary " + tableName); 
    statement.setExecutable(false); 
    primaryStatementId = statement.getId(); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
 
    TLptsStatement odbcTableStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    odbcTableStatement.getTableItemByName("odbcTable").setSelected(true); 
    odbcTableStatement.setSelectOnAllColumns("odbcTable", true); 
    odbcTableStatement.setPrimaryStatement(statement.getId(), isSubstitute); 
    odbcTableStatement.addPrimaryRelationship(dynamicIndex + 1, new int[]{1}, TLptsFactoryStatement.OPERATION_EQUAL); 
    odbcTableStatement.setTitle("odbcTable sub of " + tableName); 
    subStatementId = odbcTableStatement.getId(); 
    System.out.println("SQL Statement " + odbcTableStatement.getTitle() + ": " + odbcTableStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, odbcTableStatement); 
  } 
 
 
//  ############################################################################################################### 
//  ####                  S E L E C T       F U N C T I O N S 
//  ############################################################################################################### 
 
  private void selectDynamicData(Integer index) 
  { 
    for (Integer i = 0; i < index; i++) 
    { 
      idListSelected.add(i); 
      integerMapSelected.put(i, integerMap.get(i)); 
      varcharacterMapSelected.put(i, varcharacterMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      doubleMapSelected.put(i, doubleMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      bitMapSelected.put(i, bitMap.get(i)); 
    } 
  } 
 
  private void selectData(TLptsConnection connection) 
  { 
    Integer[] ids = {10, 11, 12, 13, 14, 15, 16, 17, 18, 19}; 
 
    for (Integer i : ids) 
    { 
      idListSelected.add(i); 
      integerMapSelected.put(i, integerMap.get(i)); 
      varcharacterMapSelected.put(i, varcharacterMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      doubleMapSelected.put(i, doubleMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      bitMapSelected.put(i, bitMap.get(i)); 
    } 
    selectStatement(connection); 
  } 
 
  private void selectStatement(TLptsConnection connection) 
  { 
    TLptsStatement selectStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    selectStatement.getTableItemByName("odbcTable").setSelected(true); 
    selectStatement.setSelectOnAllColumns("odbcTable", true); 
    selectStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
 
    TLptsDBStatementCriteriaItemType spitInteger = new TLptsDBStatementCriteriaItemType(); 
    spitInteger.setColumnItem(selectStatement.getColumnItemByName("odbcTable", "integerColumn")); 
    spitInteger.setOperation(TLptsFactoryStatement.OPERATION_NOT_EQUAL); 
    spitInteger.addStaticStringListItem("13549"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitInteger); 
 
    TLptsDBStatementCriteriaItemType spitVarchar = new TLptsDBStatementCriteriaItemType(); 
    spitVarchar.setColumnItem(selectStatement.getColumnItemByName("odbcTable", "varcharColumn")); 
    spitVarchar.setOperation(TLptsFactoryStatement.OPERATION_LIKE); 
    spitVarchar.addStaticStringListItem("my var character1%"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitVarchar); 
 
    TLptsDBStatementCriteriaItemType spitDouble = new TLptsDBStatementCriteriaItemType(); 
    spitDouble.setColumnItem(selectStatement.getColumnItemByName("odbcTable", "doubleColumn")); 
    spitDouble.setOperation(TLptsFactoryStatement.OPERATION_BETWEEN); 
    spitDouble.addStaticStringListItem("1581012.25"); 
    spitDouble.addStaticStringListItem("1581912.25"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitDouble); 
 
    selectStatement.setTitle("Select Statement"); 
    selectStatement.setExecutable(true); 
    selectStatementId = selectStatement.getId(); 
    System.out.println("SQL Statement " + selectStatement.getTitle() + ": " + selectStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, selectStatement); 
  } 
 
 
//  ############################################################################################################### 
//  ####                   U P D A T E       F U N C T I O N S 
//  ############################################################################################################### 
 
  private void updateData(TLptsConnection connection) 
  { 
    List<String> data = new LinkedList<String>(); 
    data.add("13549"); 
    data.add("my var character1%"); 
    data.add("2010-07-27 10:40:00"); 
    data.add("1581012.25"); 
    data.add("1581912.25"); 
    data.add("200"); 
    data.add("1"); 
 
    updateStatement(connection, data); 
  } 
 
  private void updateStatement(TLptsConnection connection, List<String> data) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.UPDATE); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName("odbcTable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("odbcTable", "integerColumn"); 
    if (columnInteger != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(0)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_NOT_EQUAL); 
      where.setAndConnector(true); 
      columnInteger.setUpInsDelParameterItem(where); 
    } 
    XLptsDBStatementColumnType columnVarchar = statement.getColumnItemByName("odbcTable", "varcharColumn"); 
    if (columnVarchar != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(1)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_LIKE); 
      where.setAndConnector(true); 
      columnVarchar.setUpInsDelParameterItem(where); 
    } 
    XLptsDBStatementColumnType columnTimestamp = statement.getColumnItemByName("odbcTable", "timestampColumn"); 
    if (columnTimestamp != null) 
      columnTimestamp.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(2))); 
 
    XLptsDBStatementColumnType columnDouble = statement.getColumnItemByName("odbcTable", "doubleColumn"); 
    if (columnDouble != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(3)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_BETWEEN); 
      where.setBetweenOther(new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(4))); 
      where.setAndConnector(true); 
      columnDouble.setUpInsDelParameterItem(where); 
    } 
    XLptsDBStatementColumnType columnSmallInt = statement.getColumnItemByName("odbcTable", "smallintegerColumn"); 
    if (columnSmallInt != null) 
      columnSmallInt.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(5))); 
 
    XLptsDBStatementColumnType columnBit = statement.getColumnItemByName("odbcTable", "bitColumn"); 
    if (columnBit != null) 
      columnBit.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(6))); 
 
    statement.setTitle("Update Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicUpdate(statement); 
  } 
 
  private void selectDataAfterUpdate(TLptsConnection connection) 
  { 
    List<Integer> ids = new LinkedList<Integer>(); 
    for (int i = 0; i < NO_OF_INSERTIONS; i++) 
      if (i > 9 && i < 20) 
        ids.add(i); 
 
    for (Integer i : ids) 
    { 
      idListSelected.add(i); 
      integerMapSelected.put(i, integerMap.get(i)); 
      varcharacterMapSelected.put(i, varcharacterMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      doubleMapSelected.put(i, doubleMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      bitMapSelected.put(i, bitMap.get(i)); 
    } 
    selectAfterUpdateStatement(connection); 
  } 
 
  private void selectAfterUpdateStatement(TLptsConnection connection) 
  { 
    TLptsStatement selectStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    selectStatement.getTableItemByName("odbcTable").setSelected(true); 
    selectStatement.setSelectOnAllColumns("odbcTable", true); 
    selectStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
 
    TLptsDBStatementCriteriaItemType spitDouble = new TLptsDBStatementCriteriaItemType(); 
    spitDouble.setColumnItem(selectStatement.getColumnItemByName("odbcTable", "doubleColumn")); 
    spitDouble.setOperation(TLptsFactoryStatement.OPERATION_BETWEEN); 
    spitDouble.addStaticStringListItem("1581012.25"); 
    spitDouble.addStaticStringListItem("1581912.25"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitDouble); 
 
    selectStatement.setTitle("Select After Update Statement"); 
    selectStatement.setExecutable(true); 
    selectAfterUpdateStatementId = selectStatement.getId(); 
    System.out.println("SQL Statement " + selectStatement.getTitle() + ": " + selectStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, selectStatement); 
  } 
 
 
//  ############################################################################################################### 
//  ####             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 (connection.getId().equals(connectionCreateTableOdbcId)) 
        createAllTableStatements(connection); 
      else if (connection.getId().equals(connectionOdbcId)) 
        insertDataIntoAllTables(connection); 
    } 
 
    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) 
    { 
      if (statement.getTypeOfStatement() == XLptsDBTypeOfStatementType.CREATE) 
      { 
        TLptsFactoryStatement.executeOtherStatement(statement); 
 
        System.out.println("Table Created for connection : " + connection.getTitle()); 
        createdTables++; 
        if (createdTables == 7) 
        { 
          TLptsFactoryConnection.removeConnection(connection, true); 
          TLptsDBConnectionType connectionType; 
 
          //Connect To ODBC 
          connectionType = connectToDB(); 
          if (connectionType != null) 
          { 
            connectionOdbcId = connectionType.getId(); 
            TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
          } 
        } 
      } else if (statement.getTypeOfStatement() == XLptsDBTypeOfStatementType.SELECT) 
      { 
        if (!integerStatementId.isEmpty() && !varcharStatementId.isEmpty() && 
            !timestampStatementId.isEmpty() && !doubleStatementId.isEmpty() && 
            !smallIntegerStatementId.isEmpty() && !bitStatementId.isEmpty() && 
            insertions != MULTIPLE_LIMIT) 
          insertDataInMultipleTables(connection); 
        else 
          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) 
    { 
      if (rowEvent.getStatement().getId().equals(selectStatementId) || 
          rowEvent.getStatement().getId().equals(selectAfterUpdateStatementId) || 
          rowEvent.getStatement().getId().equals(selectAfterDeleteStatementId) || 
          rowEvent.getStatement().getId().equals(subStatementId)) 
      { 
        if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_RECORD)) 
        { 
          int index = 0; 
          Integer id = -1; 
 
          for (Object obj : rowEvent.getRowObjectList()) 
          { 
            if (index == 0) 
              id = ((Integer) obj); 
            else if (index == 1) 
            { 
              if (integerMapSelected.get(id) != null) 
                integerMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Integer is not in list : " + obj, null); 
            } else if (index == 2) 
            { 
              if (varcharacterMapSelected.get(id) != null) 
                varcharacterMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Varcharacter is not in list : " + obj, null); 
            } else if (index == 3) 
            { 
              if (timestampMapSelected.get(id) != null) 
                timestampMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Timestamp is not in list : " + obj, null); 
            } else if (index == 4) 
            { 
              if (doubleMapSelected.get(id) != null) 
                doubleMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Double is not in list : " + obj, null); 
            } else if (index == 5) 
            { 
              if (smallintegerMapSelected.get(id) != null) 
                smallintegerMapSelected.remove(id); 
              else 
                TLptsLogger.logError("SmallInteger is not in list : " + obj, null); 
            } else if (index == 6) 
            { 
              if (bitMapSelected.get(id) != null) 
                bitMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Bit is not in list : " + obj, null); 
            } 
            idListSelected.remove(id); 
            index++; 
          } 
        } else if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE) && !rowEvent.getStatement().getId().equals(subStatementId)) 
        { 
          if (idListSelected.isEmpty() && integerMapSelected.isEmpty() && 
              varcharacterMapSelected.isEmpty() && doubleMapSelected.isEmpty() && 
              smallintegerMapSelected.isEmpty() && bitMapSelected.isEmpty()) 
          { 
            System.out.println("#######################"); 
            System.out.println("# Comparison Complete #"); 
            System.out.println("#######################"); 
            if (selectAfterUpdateStatementId.equals("")) 
              updateData(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
            else 
              cleanUp(true); 
          } else 
            TLptsLogger.logError("A List is not Empty!", null); 
        } 
      } 
      if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE) && rowEvent.getStatement().getId().equals(primaryStatementId)) 
      { 
        if (idListSelected.isEmpty() && integerMapSelected.isEmpty() && 
            varcharacterMapSelected.isEmpty() && doubleMapSelected.isEmpty() && 
            smallintegerMapSelected.isEmpty() && bitMapSelected.isEmpty()) 
        { 
          System.out.println("############################################################"); 
          System.out.println("# Comparison Complete for statement : " + rowEvent.getStatement().getTitle() + " #"); 
          System.out.println("############################################################"); 
 
          if ((dynamicIndex == 1 && !isSubstitute) || (dynamicIndex == 2 && isSubstitute)) 
            dynamicIndex++; 
 
          if (dynamicIndex < 5) 
            dynamicStatement(((TLptsConnection) rowEvent.getStatement().getConnectionWE()), tableNames.get(dynamicIndex), columnNames.get(dynamicIndex++), isSubstitute); 
          else if (!isSubstitute) 
          { 
            isSubstitute = true; 
            dynamicIndex = 0; 
            dynamicStatement(((TLptsConnection) rowEvent.getStatement().getConnectionWE()), tableNames.get(dynamicIndex), columnNames.get(dynamicIndex++), isSubstitute); 
          } else if (isSubstitute) 
            selectData(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
        } else 
          TLptsLogger.logError("A List is not Empty!", null); 
      } 
    } 
 
    public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) 
    { 
      if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.INSERT_COMPLETE) 
      { 
        insertions++; 
        if (insertions == NO_OF_INSERTIONS) 
        { 
          System.out.println("###########################################"); 
          System.out.println("# Data Insertion for odbcTable Complete! #"); 
          System.out.println("###########################################"); 
          clearStatements(); 
          selectColumnStatement(((TLptsConnection) rowSetEvent.getStatement().getConnectionWE())); 
        } else if (insertions == MULTIPLE_LIMIT) 
        { 
          System.out.println("###############################################"); 
          System.out.println("# Data Insertion for Primary Tables Complete! #"); 
          System.out.println("###############################################"); 
          clearStatements(); 
          dynamicStatement(((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()), 
                           tableNames.get(dynamicIndex), columnNames.get(dynamicIndex++), isSubstitute); 
        } 
        return false; 
      } else if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.UPDATE_COMPLETE) 
      { 
        selectDataAfterUpdate((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()); 
        return false; 
      } else 
        return true; 
    } 
  } 
 
  private void clearStatements() 
  { 
    for (TLptsStatement stmt : TLptsFactoryStatement.getStatementList()) 
      TLptsFactoryStatement.removeStatement(stmt); 
  } 
 
  private void columnAndTableNames() 
  { 
    tableNames.add("integerTable"); 
    tableNames.add("varcharTable"); 
    tableNames.add("timestampTable"); 
    tableNames.add("doubleTable"); 
    tableNames.add("smallIntegerTable"); 
    tableNames.add("bitTable"); 
 
    columnNames.add("integerColumn"); 
    columnNames.add("varcharColumn"); 
    columnNames.add("timestampColumn"); 
    columnNames.add("doubleColumn"); 
    columnNames.add("smallintegerColumn"); 
    columnNames.add("bitColumn"); 
  } 
 
  private void cleanUp(boolean isExit) 
  { 
    try 
    { 
      System.out.println("######################"); 
      System.out.println("# Test Completed!    #"); 
      System.out.println("# Deleting Tables... #"); 
      clearStatements(); 
      TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionOdbcId); 
 
      if (connection != null) 
      { 
        Statement statement = connection.createStatement(); 
        statement.executeUpdate("DROP TABLE \"bitTable\" "); 
        statement.executeUpdate("DROP TABLE \"integerTable\" "); 
        statement.executeUpdate("DROP TABLE \"doubleTable\" "); 
        statement.executeUpdate("DROP TABLE \"smallIntegerTable\" "); 
        statement.executeUpdate("DROP TABLE \"timestampTable\" "); 
        statement.executeUpdate("DROP TABLE \"varcharTable\" "); 
        statement.executeUpdate("DROP TABLE \"odbcTable\" "); 
        System.out.println("# Tables Deleted     #"); 
      } 
 
      System.out.println("######################"); 
      if (isExit) 
        System.exit(0); 
    } catch (SQLException e) 
    { 
      TLptsLogger.logWarning("SQL Exception", e.getMessage(), e); 
      if (isExit) 
        System.exit(0); 
    } 
  } 
}