TestSqlOperationsOnMsSQLAdvanced.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.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.math.BigDecimal; 
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 the MS-SQL database, but there are other similar modules for Oracle, Postgres and MySQL. 
// 
// The test starts by connecting {@link #connectToDB} and creating a main table {@link #createAllTableStatements} through 
// {@link ConnectionStatementListener} called "msSqltable" 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 "msSqltable". 
// 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}. 
// 
// The data is then deleted {@link #deleteData} with multiple criteria, as with the update. It is then verified. 
// 
// 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 TestSqlOperationsOnMsSQLAdvanced extends TestCase 
{ 
  private String connectionCreateTableMsSqlId = ""; 
  private String selectAfterUpdateStatementId = ""; 
  private String selectAfterDeleteStatementId = ""; 
  private String connectionMsSqlId = ""; 
  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, BigDecimal> decimalMap = new HashMap<Integer, BigDecimal>(); 
  private Map<Integer, Timestamp> timestampMap = new HashMap<Integer, Timestamp>(); 
  private Map<Integer, Long> bigintegerMap = new HashMap<Integer, Long>(); 
  private Map<Integer, Float> floatMap = new HashMap<Integer, Float>(); 
  private Map<Integer, Short> smallintegerMap = new HashMap<Integer, Short>(); 
  private Map<Integer, Byte> tinyintegerMap = new HashMap<Integer, Byte>(); 
  private Map<Integer, Character> charMap = new HashMap<Integer, Character>(); 
  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, BigDecimal> decimalMapSelected = new HashMap<Integer, BigDecimal>(); 
  private Map<Integer, Timestamp> timestampMapSelected = new HashMap<Integer, Timestamp>(); 
  private Map<Integer, Long> bigintegerMapSelected = new HashMap<Integer, Long>(); 
  private Map<Integer, Float> floatMapSelected = new HashMap<Integer, Float>(); 
  private Map<Integer, Short> smallintegerMapSelected = new HashMap<Integer, Short>(); 
  private Map<Integer, Byte> tinyintegerMapSelected = new HashMap<Integer, Byte>(); 
  private Map<Integer, Character> charMapSelected = new HashMap<Integer, Character>(); 
  private Map<Integer, Boolean> bitMapSelected = new HashMap<Integer, Boolean>(); 
 
  private String idStatementId = ""; 
  private String integerStatementId = ""; 
  private String varcharStatementId = ""; 
  private String decimalStatementId = ""; 
  private String timestampStatementId = ""; 
  private String bigIntegerStatementId = ""; 
  private String floatStatementId = ""; 
  private String smallIntegerStatementId = ""; 
  private String tinyIntegerStatementId = ""; 
  private String charStatementId = ""; 
  private String bitStatementId = ""; 
  private String tinyIntegerDeleteStatementId = ""; 
  private String characterDeleteStatementId = ""; 
  private String bitDeleteStatementId = ""; 
 
  private String primaryStatementId = ""; 
  private String subStatementId = ""; 
 
  private int insertions = 0; 
  private int createdTables = 0; 
  private int dynamicIndex = 0; 
  private boolean isSubstitute = false; 
  private boolean lastCols = false; 
 
  private List<String> columnNames = new LinkedList<String>(); 
  private List<String> tableNames = new LinkedList<String>(); 
 
  private final static int NO_OF_RECORDS_TO_INSERT = 200; 
 
  static public void main(String[] args) 
  { 
    TestSqlOperationsOnMsSQLAdvanced sqlOperationsOnAdvanced = new TestSqlOperationsOnMsSQLAdvanced(); 
    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);  // deletes the unwanted tables. 
          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 MS SQL 
    connectionType = connectToDB(); 
    if (connectionType != null) 
    { 
      connectionCreateTableMsSqlId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
  } 
 
 
  // #################################################################################### 
  //            C O N N E C T I O N 
  // #################################################################################### 
 
  private TLptsDBConnectionType connectToDB() 
  { 
    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("MS-SQL DB"); 
 
    dbName = bundle.getString("Testing.database.mssql.testdb2"); 
    userName = bundle.getString("Testing.database.mssql.user"); 
    password = bundle.getString("Testing.database.mssql.password"); 
    driverName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"; 
 
    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 
    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; 
  } 
 
 
//  ############################################################################################################### 
//  ####                     C R E A T E      F U N C T I O N S 
//  ############################################################################################################### 
 
  // below are functions for creating tables for the data types to be used for insertion. 
  // the functions are called in a daisy chain style (one calls the other) 
 
  private void createAllTableStatements(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create msSqlTable"); 
    TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("msSqlTable"); 
    rootType.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 decimalColumn = new TLptsDBCreateStatementColumnType(); 
    decimalColumn.setColumn("decimalColumn"); 
    TLptsDBCreateStatementColumnSqlType decimalColumnSqlType = new TLptsDBCreateStatementColumnSqlType(decimalColumn); 
    decimalColumnSqlType.setSqlType(java.sql.Types.DECIMAL); 
    decimalColumn.setSqlType(decimalColumnSqlType); 
    TLptsDBCreateStatementColumnSizeType decimalColumnSize = new TLptsDBCreateStatementColumnSizeType(decimalColumn); 
    decimalColumnSize.setSize1("10"); 
    decimalColumnSize.setSize2("2"); 
    decimalColumn.setSizes(decimalColumnSize); 
    table.addColumn(decimalColumn); 
 
    TLptsDBCreateStatementColumnType timestampColumn = new TLptsDBCreateStatementColumnType(); 
    timestampColumn.setColumn("timestampColumn"); 
    TLptsDBCreateStatementColumnSqlType timestampSqlType = new TLptsDBCreateStatementColumnSqlType(timestampColumn); 
    timestampSqlType.setSqlType(TLptsFactoryStatement.DATETIME); 
    timestampColumn.setSqlType(timestampSqlType); 
    table.addColumn(timestampColumn); 
 
    TLptsDBCreateStatementColumnType bigintegerColumn = new TLptsDBCreateStatementColumnType(); 
    bigintegerColumn.setColumn("bigintegerColumn"); 
    TLptsDBCreateStatementColumnSqlType bigintegerColumnSqlType = new TLptsDBCreateStatementColumnSqlType(bigintegerColumn); 
    bigintegerColumnSqlType.setSqlType(java.sql.Types.BIGINT); 
    bigintegerColumn.setSqlType(bigintegerColumnSqlType); 
    table.addColumn(bigintegerColumn); 
 
    TLptsDBCreateStatementColumnType floatColumn = new TLptsDBCreateStatementColumnType(); 
    floatColumn.setColumn("floatColumn"); 
    TLptsDBCreateStatementColumnSqlType floatColumnSqlType = new TLptsDBCreateStatementColumnSqlType(floatColumn); 
    floatColumnSqlType.setSqlType(java.sql.Types.FLOAT); 
    floatColumn.setSqlType(floatColumnSqlType); 
    table.addColumn(floatColumn); 
 
    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 tinyintegerColumn = new TLptsDBCreateStatementColumnType(); 
    tinyintegerColumn.setColumn("tinyintegerColumn"); 
    TLptsDBCreateStatementColumnSqlType tinyintegerColumnSqlType = new TLptsDBCreateStatementColumnSqlType(tinyintegerColumn); 
    tinyintegerColumnSqlType.setSqlType(java.sql.Types.TINYINT); 
    tinyintegerColumn.setSqlType(tinyintegerColumnSqlType); 
    table.addColumn(tinyintegerColumn); 
 
    TLptsDBCreateStatementColumnType charColumn = new TLptsDBCreateStatementColumnType(); 
    charColumn.setColumn("charColumn"); 
    TLptsDBCreateStatementColumnSqlType charColumnSqlType = new TLptsDBCreateStatementColumnSqlType(charColumn); 
    charColumnSqlType.setSqlType(java.sql.Types.CHAR); 
    charColumn.setSqlType(charColumnSqlType); 
    table.addColumn(charColumn); 
 
    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(rootType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createIntegerTableStatement(connection); 
  } 
 
 
  private void createIntegerTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create integerTable"); 
    TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("integerTable"); 
    rootType.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(rootType); 
 
    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 rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("varcharTable"); 
    rootType.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(rootType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createDecimalTableStatement(connection); 
  } 
 
  private void createDecimalTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create decimalTable"); 
    TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("decimalTable"); 
    rootType.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 decimalColumn = new TLptsDBCreateStatementColumnType(); 
    decimalColumn.setColumn("decimalColumn"); 
 
    TLptsDBCreateStatementColumnSqlType decimalColumnSqlType = new TLptsDBCreateStatementColumnSqlType(decimalColumn); 
    decimalColumnSqlType.setSqlType(java.sql.Types.DECIMAL); 
    decimalColumn.setSqlType(decimalColumnSqlType); 
 
    TLptsDBCreateStatementColumnSizeType decimalColumnSize = new TLptsDBCreateStatementColumnSizeType(decimalColumn); 
    decimalColumnSize.setSize1("10"); 
    decimalColumnSize.setSize2("2"); 
    decimalColumn.setSizes(decimalColumnSize); 
 
    table.addColumn(decimalColumn); 
    createStatement.setCreateRootItem(rootType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createBigIntegerTableStatement(connection); 
  } 
 
  private void createBigIntegerTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create bigIntegerTable"); 
    TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("bigIntegerTable"); 
    rootType.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 bigintegerColumn = new TLptsDBCreateStatementColumnType(); 
    bigintegerColumn.setColumn("bigintegerColumn"); 
 
    TLptsDBCreateStatementColumnSqlType bigintegerColumnSqlType = new TLptsDBCreateStatementColumnSqlType(bigintegerColumn); 
    bigintegerColumnSqlType.setSqlType(java.sql.Types.BIGINT); 
    bigintegerColumn.setSqlType(bigintegerColumnSqlType); 
 
    table.addColumn(bigintegerColumn); 
    createStatement.setCreateRootItem(rootType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createFloatTableStatement(connection); 
  } 
 
  private void createFloatTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create floatTable"); 
    TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("floatTable"); 
    rootType.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 floatColumn = new TLptsDBCreateStatementColumnType(); 
    floatColumn.setColumn("floatColumn"); 
 
    TLptsDBCreateStatementColumnSqlType floatColumnSqlType = new TLptsDBCreateStatementColumnSqlType(floatColumn); 
    floatColumnSqlType.setSqlType(java.sql.Types.FLOAT); 
    floatColumn.setSqlType(floatColumnSqlType); 
 
    table.addColumn(floatColumn); 
    createStatement.setCreateRootItem(rootType); 
 
    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 rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("smallIntegerTable"); 
    rootType.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(rootType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createTinyIntegerTableStatement(connection); 
  } 
 
  private void createTinyIntegerTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create tinyIntegerTable"); 
    TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("tinyIntegerTable"); 
    rootType.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 tinyintegerColumn = new TLptsDBCreateStatementColumnType(); 
    tinyintegerColumn.setColumn("tinyintegerColumn"); 
 
    TLptsDBCreateStatementColumnSqlType tinyintegerColumnSqlType = new TLptsDBCreateStatementColumnSqlType(tinyintegerColumn); 
    tinyintegerColumnSqlType.setSqlType(java.sql.Types.TINYINT); 
    tinyintegerColumn.setSqlType(tinyintegerColumnSqlType); 
 
    table.addColumn(tinyintegerColumn); 
    createStatement.setCreateRootItem(rootType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createCharTableStatement(connection); 
  } 
 
  private void createCharTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create charTable"); 
    TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("charTable"); 
    rootType.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 charColumn = new TLptsDBCreateStatementColumnType(); 
    charColumn.setColumn("charColumn"); 
 
    TLptsDBCreateStatementColumnSqlType charColumnSqlType = new TLptsDBCreateStatementColumnSqlType(charColumn); 
    charColumnSqlType.setSqlType(java.sql.Types.CHAR); 
    charColumn.setSqlType(charColumnSqlType); 
 
    table.addColumn(charColumn); 
    createStatement.setCreateRootItem(rootType); 
 
    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 rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("bitTable"); 
    rootType.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(rootType); 
 
    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 rootType = new TLptsDBCreateStatementRootType(); 
    rootType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("timestampTable"); 
    rootType.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 bitColumnSqlType = new TLptsDBCreateStatementColumnSqlType(timeStampColumn); 
    bitColumnSqlType.setSqlType(TLptsFactoryStatement.DATETIME); 
    timeStampColumn.setSqlType(bitColumnSqlType); 
 
    table.addColumn(timeStampColumn); 
    createStatement.setCreateRootItem(rootType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
  } 
 
 
//  ############################################################################################################### 
//  ####                  I N S E R T       F U N C T I O N S 
//  ############################################################################################################### 
 
  private void insertDataIntoAllTables(TLptsConnection connection) 
  { 
    char[] character = {'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'}; 
    for (int i = 0; i < NO_OF_RECORDS_TO_INSERT; i++) 
    { 
      idList.add(i); 
      integerMap.put(i, new Integer("1354" + i)); 
      varcharacterMap.put(i, "my var character" + i + "column"); 
      decimalMap.put(i, new BigDecimal("1" + i + ".2" + i % 10)); 
      timestampMap.put(i, new Timestamp(110, i % 12, i % 28, i % 24, i % 60, i % 33, i)); 
      bigintegerMap.put(i, new Long("123456" + i)); 
      floatMap.put(i, new Float(+i + ".23")); 
      smallintegerMap.put(i, new Short("" + i)); 
      tinyintegerMap.put(i, new Byte("" + i % 128)); 
      charMap.put(i, character[i % 24]); 
      bitMap.put(i, i % 2 == 0); 
      executeInsertStatement(connection, i); 
    } 
  } 
 
  private void executeInsertStatement(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("msSqltable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnId = statement.getColumnItemByName("msSqltable", "id"); 
    if (columnId != null) 
      columnId.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, idList.get(index).toString())); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("msSqltable", "integerColumn"); 
    if (columnInteger != null) 
      columnInteger.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, integerMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnVarchar = statement.getColumnItemByName("msSqltable", "varcharColumn"); 
    if (columnVarchar != null) 
      columnVarchar.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, varcharacterMap.get(index))); 
 
    XLptsDBStatementColumnType columnDecimal = statement.getColumnItemByName("msSqltable", "decimalColumn"); 
    if (columnDecimal != null) 
      columnDecimal.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, decimalMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnTimestamp = statement.getColumnItemByName("msSqltable", "timestampColumn"); 
    if (columnTimestamp != null) 
      columnTimestamp.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, timestampMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnBigInt = statement.getColumnItemByName("msSqltable", "bigintegerColumn"); 
    if (columnBigInt != null) 
      columnBigInt.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, bigintegerMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnFloat = statement.getColumnItemByName("msSqltable", "floatColumn"); 
    if (columnFloat != null) 
      columnFloat.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, floatMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnSmallInt = statement.getColumnItemByName("msSqltable", "smallintegerColumn"); 
    if (columnSmallInt != null) 
      columnSmallInt.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, smallintegerMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnTiny = statement.getColumnItemByName("msSqltable", "tinyintegerColumn"); 
    if (columnTiny != null) 
      columnTiny.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, tinyintegerMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnChar = statement.getColumnItemByName("msSqltable", "charColumn"); 
    if (columnChar != null) 
      columnChar.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, charMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnBit = statement.getColumnItemByName("msSqltable", "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, "decimalColumn"); 
    createStatement(connection, "timestampColumn"); 
    createStatement(connection, "bigintegerColumn"); 
    createStatement(connection, "floatColumn"); 
    createStatement(connection, "smallintegerColumn"); 
    createStatement(connection, "tinyintegerColumn"); 
    createStatement(connection, "charColumn"); 
    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("msSqltable"); 
    table.setSelected(true); 
    statement.getColumnItemByName("msSqltable", 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("decimalColumn")) 
      decimalStatementId = statement.getId(); 
    else if (columnName.equals("timestampColumn")) 
      timestampStatementId = statement.getId(); 
    else if (columnName.equals("bigintegerColumn")) 
      bigIntegerStatementId = statement.getId(); 
    else if (columnName.equals("floatColumn")) 
      floatStatementId = statement.getId(); 
    else if (columnName.equals("smallintegerColumn")) 
      smallIntegerStatementId = statement.getId(); 
    else if (columnName.equals("tinyintegerColumn")) 
      tinyIntegerStatementId = statement.getId(); 
    else if (columnName.equals("charColumn")) 
      charStatementId = 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, "decimalTable", "decimalColumn", decimalStatementId); 
    setMultipleInserts(connection, "timestampTable", "timestampColumn", timestampStatementId); 
    setMultipleInserts(connection, "bigIntegerTable", "bigintegerColumn", bigIntegerStatementId); 
    setMultipleInserts(connection, "floatTable", "floatColumn", floatStatementId); 
    setMultipleInserts(connection, "smallIntegerTable", "smallintegerColumn", smallIntegerStatementId); 
    setMultipleInserts(connection, "tinyIntegerTable", "tinyintegerColumn", tinyIntegerStatementId); 
    setMultipleInserts(connection, "charTable", "charColumn", charStatementId); 
    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); 
      return; 
    } 
    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("tinyIntegerTable")) 
      selectDynamicData(4); 
    else if (tableName.equals("charTable")) 
      selectDynamicData(3); 
    else if (tableName.equals("bitTable")) 
      selectDynamicData(2); 
    else 
      selectDynamicData(NO_OF_RECORDS_TO_INSERT); 
 
    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("tinyIntegerTable")) 
        spit.addStaticStringListItem("4"); 
      else if (tableName.equals("charTable")) 
        spit.addStaticStringListItem("3"); 
      else 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 msSqlTableStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    msSqlTableStatement.getTableItemByName("msSqlTable").setSelected(true); 
    msSqlTableStatement.setSelectOnAllColumns("msSqlTable", true); 
    msSqlTableStatement.setPrimaryStatement(statement.getId(), isSubstitute); 
    msSqlTableStatement.addPrimaryRelationship(dynamicIndex + 1, new int[]{1}, TLptsFactoryStatement.OPERATION_EQUAL); 
    msSqlTableStatement.setTitle("msSqlTable sub of " + tableName); 
    subStatementId = msSqlTableStatement.getId(); 
    System.out.println("SQL Statement " + msSqlTableStatement.getTitle() + ": " + msSqlTableStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, msSqlTableStatement); 
  } 
 
  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)); 
      decimalMapSelected.put(i, decimalMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      bigintegerMapSelected.put(i, bigintegerMap.get(i)); 
      floatMapSelected.put(i, floatMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      tinyintegerMapSelected.put(i, tinyintegerMap.get(i)); 
      charMapSelected.put(i, charMap.get(i)); 
      bitMapSelected.put(i, bitMap.get(i)); 
    } 
  } 
 
 
//  ############################################################################################################### 
//  ####                  D E L E T E       F U N C T I O N S 
//  ############################################################################################################### 
 
  private void deleteTinyIntegerStatement(TLptsConnection connection) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.DELETE); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName("msSqlTable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("msSqlTable", "id"); 
    if (columnInteger != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, "4"); 
      where.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN_EQUAL); 
      where.setAndConnector(true); 
      columnInteger.setUpInsDelParameterItem(where); 
    } 
 
    tinyIntegerDeleteStatementId = statement.getId(); 
    statement.setTitle("Delete Statement for Tiny Integer"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicDelete(statement); 
  } 
 
  private void deleteCharStatement(TLptsConnection connection) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.DELETE); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName("msSqlTable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("msSqlTable", "id"); 
    if (columnInteger != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, "3"); 
      where.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN_EQUAL); 
      where.setAndConnector(true); 
      columnInteger.setUpInsDelParameterItem(where); 
    } 
 
    characterDeleteStatementId = statement.getId(); 
    statement.setTitle("Delete Statement for Character"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicDelete(statement); 
  } 
 
  private void deleteBitStatement(TLptsConnection connection) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.DELETE); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName("msSqlTable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("msSqlTable", "id"); 
    if (columnInteger != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, "2"); 
      where.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN_EQUAL); 
      where.setAndConnector(true); 
      columnInteger.setUpInsDelParameterItem(where); 
    } 
 
    bitDeleteStatementId = statement.getId(); 
    statement.setTitle("Delete Statement for Bit"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicDelete(statement); 
  } 
 
 
//  ############################################################################################################### 
//  ####                  S E L E C T       F U N C T I O N S 
//  ############################################################################################################### 
 
  private void selectData(TLptsConnection connection) 
  { 
    Integer[] ids = {100, 101, 102, 103, 104, 105, 106, 120, 121, 122, 123, 124, 
                     125, 126, 127, 128, 129, 130, 144, 145, 146, 147, 148, 149, 
                     150, 151, 152, 153, 154, 168, 169, 170, 171, 172, 173, 174, 
                     175, 176, 177, 178, 192, 193, 194, 195, 196, 197, 198, 199}; 
 
    for (Integer i : ids) 
    { 
      idListSelected.add(i); 
      integerMapSelected.put(i, integerMap.get(i)); 
      varcharacterMapSelected.put(i, varcharacterMap.get(i)); 
      decimalMapSelected.put(i, decimalMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      bigintegerMapSelected.put(i, bigintegerMap.get(i)); 
      floatMapSelected.put(i, floatMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      tinyintegerMapSelected.put(i, tinyintegerMap.get(i)); 
      charMapSelected.put(i, charMap.get(i)); 
      bitMapSelected.put(i, bitMap.get(i)); 
    } 
    selectStatement(connection); 
  } 
 
  private void selectStatement(TLptsConnection connection) 
  { 
    TLptsStatement selectStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    selectStatement.getTableItemByName("msSqlTable").setSelected(true); 
    selectStatement.setSelectOnAllColumns("msSqlTable", true); 
    selectStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
 
    TLptsDBStatementCriteriaItemType spitInteger = new TLptsDBStatementCriteriaItemType(); 
    spitInteger.setColumnItem(selectStatement.getColumnItemByName("msSqlTable", "integerColumn")); 
    spitInteger.setOperation(TLptsFactoryStatement.OPERATION_NOT_EQUAL); 
    spitInteger.addStaticStringListItem("13549"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitInteger); 
 
    TLptsDBStatementCriteriaItemType spitVarchar = new TLptsDBStatementCriteriaItemType(); 
    spitVarchar.setColumnItem(selectStatement.getColumnItemByName("msSqlTable", "varcharColumn")); 
    spitVarchar.setOperation(TLptsFactoryStatement.OPERATION_LIKE); 
    spitVarchar.addStaticStringListItem("my var character1%"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitVarchar); 
 
    TLptsDBStatementCriteriaItemType spitDecimal = new TLptsDBStatementCriteriaItemType(); 
    spitDecimal.setColumnItem(selectStatement.getColumnItemByName("msSqlTable", "decimalColumn")); 
    spitDecimal.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN_EQUAL); 
    spitDecimal.addStaticStringListItem("12.22"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitDecimal); 
 
    TLptsDBStatementCriteriaItemType spitBiginteger = new TLptsDBStatementCriteriaItemType(); 
    spitBiginteger.setColumnItem(selectStatement.getColumnItemByName("msSqlTable", "bigintegerColumn")); 
    spitBiginteger.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN); 
    spitBiginteger.addStaticStringListItem("12345611"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitBiginteger); 
 
    TLptsDBStatementCriteriaItemType spitChar = new TLptsDBStatementCriteriaItemType(); 
    spitChar.setColumnItem(selectStatement.getColumnItemByName("msSqlTable", "charColumn")); 
    spitChar.setOperation(TLptsFactoryStatement.OPERATION_IN); 
    spitChar.addStaticStringListItem("A"); 
    spitChar.addStaticStringListItem("B"); 
    spitChar.addStaticStringListItem("C"); 
    spitChar.addStaticStringListItem("D"); 
    spitChar.addStaticStringListItem("E"); 
    spitChar.addStaticStringListItem("F"); 
    spitChar.addStaticStringListItem("G"); 
    spitChar.addStaticStringListItem("H"); 
    spitChar.addStaticStringListItem("I"); 
    spitChar.addStaticStringListItem("J"); 
    spitChar.addStaticStringListItem("K"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitChar); 
 
    TLptsDBStatementCriteriaItemType spitFloat = new TLptsDBStatementCriteriaItemType(); 
    spitFloat.setColumnItem(selectStatement.getColumnItemByName("msSqlTable", "floatColumn")); 
    spitFloat.setOperation(TLptsFactoryStatement.OPERATION_EQUAL); 
    spitFloat.addStaticStringListItem("321541"); 
    spitFloat.setAndConnector(false); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitFloat); 
 
    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("12.22"); 
    data.add("2010-07-27 10:40:00"); 
    data.add("12345611"); 
    data.add("321541"); 
    data.add("200"); 
    data.add("72"); 
    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("msSqlTable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("msSqlTable", "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("msSqlTable", "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 columnDecimal = statement.getColumnItemByName("msSqlTable", "decimalColumn"); 
    if (columnDecimal != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(2)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN_EQUAL); 
      where.setAndConnector(true); 
      columnDecimal.setUpInsDelParameterItem(where); 
    } 
 
    XLptsDBStatementColumnType columnTimestamp = statement.getColumnItemByName("msSqlTable", "timestampColumn"); 
    if (columnTimestamp != null) 
    { 
      columnTimestamp.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(3))); 
    } 
 
    XLptsDBStatementColumnType columnBigInt = statement.getColumnItemByName("msSqlTable", "bigintegerColumn"); 
    if (columnBigInt != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(4)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN); 
      where.setAndConnector(true); 
      columnBigInt.setUpInsDelParameterItem(where); 
    } 
 
    XLptsDBStatementColumnType columnFloat = statement.getColumnItemByName("msSqlTable", "floatColumn"); 
    if (columnFloat != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(5)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_EQUAL); 
      where.setAndConnector(false); 
      columnFloat.setUpInsDelParameterItem(where); 
    } 
 
    XLptsDBStatementColumnType columnSmallInt = statement.getColumnItemByName("msSqlTable", "smallintegerColumn"); 
    if (columnSmallInt != null) 
    { 
      columnSmallInt.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(6))); 
    } 
 
    XLptsDBStatementColumnType columnTiny = statement.getColumnItemByName("msSqlTable", "tinyintegerColumn"); 
    if (columnTiny != null) 
    { 
      columnTiny.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(7))); 
    } 
 
    XLptsDBStatementColumnType columnBit = statement.getColumnItemByName("msSqlTable", "bitColumn"); 
    if (columnBit != null) 
    { 
      columnBit.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(8))); 
    } 
 
    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_RECORDS_TO_INSERT; i += 2) 
      if (!((i > 10 && i < 20) || (i > 98 && i < NO_OF_RECORDS_TO_INSERT))) 
        ids.add(i); 
 
    for (Integer i : ids) 
    { 
      idListSelected.add(i); 
      integerMapSelected.put(i, integerMap.get(i)); 
      varcharacterMapSelected.put(i, varcharacterMap.get(i)); 
      decimalMapSelected.put(i, decimalMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      bigintegerMapSelected.put(i, bigintegerMap.get(i)); 
      floatMapSelected.put(i, floatMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      tinyintegerMapSelected.put(i, tinyintegerMap.get(i)); 
      charMapSelected.put(i, charMap.get(i)); 
      bitMapSelected.put(i, bitMap.get(i)); 
    } 
    selectAfterUpdateStatement(connection); 
  } 
 
  private void selectAfterUpdateStatement(TLptsConnection connection) 
  { 
    TLptsStatement selectStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    selectStatement.getTableItemByName("msSqlTable").setSelected(true); 
    selectStatement.setSelectOnAllColumns("msSqlTable", true); 
    selectStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
 
    TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType(); 
    spit.setColumnItem(selectStatement.getColumnItemByName("msSqlTable", "bitColumn")); 
    spit.setOperation(TLptsFactoryStatement.OPERATION_EQUAL); 
    spit.addStaticStringListItem("1"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spit); 
 
    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); 
  } 
 
  private void deleteData(TLptsConnection connection) 
  { 
    List<String> data = new LinkedList<String>(); 
    data.add("13549"); 
    data.add("my var character1%"); 
    data.add("12.22"); 
    data.add("12345611"); 
    data.add("5.23"); 
 
    deleteStatement(connection, data); 
  } 
 
  private void deleteStatement(TLptsConnection connection, List<String> data) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.DELETE); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName("msSqlTable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("msSqlTable", "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("msSqlTable", "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 columnDecimal = statement.getColumnItemByName("msSqlTable", "decimalColumn"); 
    if (columnDecimal != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(2)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN_EQUAL); 
      where.setAndConnector(true); 
      columnDecimal.setUpInsDelParameterItem(where); 
    } 
 
    XLptsDBStatementColumnType columnBigInt = statement.getColumnItemByName("msSqlTable", "bigintegerColumn"); 
    if (columnBigInt != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(3)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN); 
      where.setAndConnector(true); 
      columnBigInt.setUpInsDelParameterItem(where); 
    } 
 
    XLptsDBStatementColumnType columnFloat = statement.getColumnItemByName("msSqlTable", "floatColumn"); 
    if (columnFloat != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(4)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_EQUAL); 
      where.setAndConnector(false); 
      columnFloat.setUpInsDelParameterItem(where); 
    } 
 
    statement.setTitle("Delete Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicDelete(statement); 
  } 
 
  private void selectDataAfterDelete(TLptsConnection connection) 
  { 
    List<Integer> ids = new LinkedList<Integer>(); 
    for (int i = 1; i < NO_OF_RECORDS_TO_INSERT; i += 2) 
      if (!((i > 3 && i < 7) || (i > 11 && i < 21) || (i > 99 && i < NO_OF_RECORDS_TO_INSERT))) 
        ids.add(i); 
 
    for (Integer i : ids) 
    { 
      idListSelected.add(i); 
      integerMapSelected.put(i, integerMap.get(i)); 
      varcharacterMapSelected.put(i, varcharacterMap.get(i)); 
      decimalMapSelected.put(i, decimalMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      bigintegerMapSelected.put(i, bigintegerMap.get(i)); 
      floatMapSelected.put(i, floatMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      tinyintegerMapSelected.put(i, tinyintegerMap.get(i)); 
      charMapSelected.put(i, charMap.get(i)); 
      bitMapSelected.put(i, bitMap.get(i)); 
    } 
    selectAfterDeleteStatement(connection); 
  } 
 
  private void selectAfterDeleteStatement(TLptsConnection connection) 
  { 
    TLptsStatement selectStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    selectStatement.getTableItemByName("msSqlTable").setSelected(true); 
    selectStatement.setSelectOnAllColumns("msSqlTable", true); 
    selectStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
 
    TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType(); 
    spit.setColumnItem(selectStatement.getColumnItemByName("msSqlTable", "bitColumn")); 
    spit.setOperation(TLptsFactoryStatement.OPERATION_EQUAL); 
    spit.addStaticStringListItem("0"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spit); 
 
    selectStatement.setTitle("Select After Delete Statement"); 
    selectStatement.setExecutable(true); 
    selectAfterDeleteStatementId = 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(connectionCreateTableMsSqlId)) 
        createAllTableStatements(connection); 
      else if (connection.getId().equals(connectionMsSqlId)) 
        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 == 11) 
        { 
          TLptsFactoryConnection.removeConnection(connection, true); 
          TLptsDBConnectionType connectionType; 
 
          //Connect To MS SQL 
          connectionType = connectToDB(); 
          if (connectionType != null) 
          { 
            connectionMsSqlId = connectionType.getId(); 
            TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
          } 
        } 
      } else if (statement.getTypeOfStatement() == XLptsDBTypeOfStatementType.SELECT) 
      { 
        if (!integerStatementId.isEmpty() && !varcharStatementId.isEmpty() && 
            !decimalStatementId.isEmpty() && !timestampStatementId.isEmpty() && 
            !bigIntegerStatementId.isEmpty() && !floatStatementId.isEmpty() && 
            !smallIntegerStatementId.isEmpty() && !tinyIntegerStatementId.isEmpty() && 
            !charStatementId.isEmpty() && !bitStatementId.isEmpty() && insertions != 210) 
          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; 
 
          // this verifies the whole row of the main table 
          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 (decimalMapSelected.get(id) != null) 
                decimalMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Decimal is not in list : " + obj, null); 
            } else if (index == 4) 
            { 
              if (timestampMapSelected.get(id) != null) 
                timestampMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Timestamp is not in list : " + obj, null); 
            } else if (index == 5) 
            { 
              if (bigintegerMapSelected.get(id) != null) 
                bigintegerMapSelected.remove(id); 
              else 
                TLptsLogger.logError("BigInteger is not in list : " + obj, null); 
            } else if (index == 6) 
            { 
              if (floatMapSelected.get(id) != null) 
                floatMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Float is not in list : " + obj, null); 
            } else if (index == 7) 
            { 
              if (smallintegerMapSelected.get(id) != null) 
                smallintegerMapSelected.remove(id); 
              else 
                TLptsLogger.logError("SmallInteger is not in list : " + obj, null); 
            } else if (index == 8) 
            { 
              if (tinyintegerMapSelected.get(id) != null) 
                tinyintegerMapSelected.remove(id); 
              else 
                TLptsLogger.logError("TinyInteger is not in list : " + obj, null); 
            } else if (index == 9) 
            { 
              if (charMapSelected.get(id) != null) 
                charMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Character is not in list : " + obj, null); 
            } else if (index == 10) 
            { 
              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() && decimalMapSelected.isEmpty() && 
              timestampMapSelected.isEmpty() && bigintegerMapSelected.isEmpty() && 
              floatMapSelected.isEmpty() && smallintegerMapSelected.isEmpty() && 
              tinyintegerMapSelected.isEmpty() && charMapSelected.isEmpty() && 
              charMapSelected.isEmpty() && bitMapSelected.isEmpty()) 
          { 
            System.out.println("#######################"); 
            System.out.println("# Comparison Complete #"); 
            System.out.println("#######################"); 
 
            if (selectAfterUpdateStatementId.equals("")) 
              updateData(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
            else if (selectAfterDeleteStatementId.equals("")) 
              deleteData(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
            else 
              deleteTinyIntegerStatement(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
          } 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() && decimalMapSelected.isEmpty() && 
            bigintegerMapSelected.isEmpty() && floatMapSelected.isEmpty() && 
            smallintegerMapSelected.isEmpty() && tinyintegerMapSelected.isEmpty() && 
            charMapSelected.isEmpty() && charMapSelected.isEmpty() && bitMapSelected.isEmpty()) 
        { 
          System.out.println("############################################################"); 
          System.out.println("# Comparison Complete for statement : " + rowEvent.getStatement().getTitle() + " #"); 
          System.out.println("############################################################"); 
 
          if (!lastCols) 
          { 
            if (dynamicIndex == 1) 
              dynamicIndex++; 
            if (dynamicIndex < 7) 
              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 if (dynamicIndex == 8 && !isSubstitute) 
          { 
            isSubstitute = true; 
            dynamicStatement(((TLptsConnection) rowEvent.getStatement().getConnectionWE()), tableNames.get(dynamicIndex - 1), columnNames.get(dynamicIndex - 1), isSubstitute); 
          } else if (dynamicIndex == 8 && isSubstitute) 
          { 
            dynamicIndex++; 
            deleteBitStatement(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
          } else 
            cleanUp(true); 
        } 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_RECORDS_TO_INSERT) 
        { 
          System.out.println("###########################################"); 
          System.out.println("# Data Insertion for msSqlTable Complete! #"); 
          System.out.println("###########################################"); 
          selectColumnStatement(((TLptsConnection) rowSetEvent.getStatement().getConnectionWE())); 
        } else if (insertions == 210) 
        { 
          System.out.println("###############################################"); 
          System.out.println("# Data Insertion for Primary Tables Complete! #"); 
          System.out.println("###############################################"); 
          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 if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.DELETE_COMPLETE) 
      { 
        if (rowSetEvent.getStatement().getId().equals(tinyIntegerDeleteStatementId)) 
        { 
          lastCols = true; 
          isSubstitute = false; 
          dynamicStatement(((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()), 
                           tableNames.get(dynamicIndex), columnNames.get(dynamicIndex++), 
                           isSubstitute); 
        } else if (rowSetEvent.getStatement().getId().equals(characterDeleteStatementId)) 
        { 
          isSubstitute = false; 
          dynamicStatement(((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()), 
                           tableNames.get(dynamicIndex), columnNames.get(dynamicIndex++), 
                           isSubstitute); 
        } else if (rowSetEvent.getStatement().getId().equals(bitDeleteStatementId)) 
        { 
          isSubstitute = false; 
          dynamicStatement(((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()), 
                           tableNames.get(dynamicIndex), columnNames.get(dynamicIndex++), 
                           isSubstitute); 
        } else 
          selectDataAfterDelete((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()); 
        return false; 
      } else 
        return true; 
    } 
  } 
 
  private void columnAndTableNames() 
  { 
    tableNames.add("integerTable"); 
    tableNames.add("varcharTable"); 
    tableNames.add("decimalTable"); 
    tableNames.add("timestampTable"); 
    tableNames.add("bigIntegerTable"); 
    tableNames.add("floatTable"); 
    tableNames.add("smallIntegerTable"); 
 
    tableNames.add("tinyIntegerTable"); 
    tableNames.add("charTable"); 
    tableNames.add("bitTable"); 
 
    columnNames.add("integerColumn"); 
    columnNames.add("varcharColumn"); 
    columnNames.add("decimalColumn"); 
    columnNames.add("timestampColumn"); 
    columnNames.add("bigintegerColumn"); 
    columnNames.add("floatColumn"); 
    columnNames.add("smallintegerColumn"); 
 
    columnNames.add("tinyintegerColumn"); 
    columnNames.add("charColumn"); 
    columnNames.add("bitColumn"); 
  } 
 
  private void cleanUp(boolean isExit) 
  { 
    try 
    { 
      System.out.println("######################"); 
      System.out.println("# Test Completed!    #"); 
      System.out.println("# Deleting Tables... #"); 
 
      TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMsSqlId); 
      if (connection != null) 
      { 
        Statement statement = connection.createStatement(); 
        statement.executeUpdate("DROP TABLE [bigintegerTable] "); 
        statement.executeUpdate("DROP TABLE [bitTable] "); 
        statement.executeUpdate("DROP TABLE [charTable] "); 
        statement.executeUpdate("DROP TABLE [decimalTable] "); 
        statement.executeUpdate("DROP TABLE [floatTable] "); 
        statement.executeUpdate("DROP TABLE [integerTable] "); 
        statement.executeUpdate("DROP TABLE [msSqlTable] "); 
        statement.executeUpdate("DROP TABLE [smallintegerTable] "); 
        statement.executeUpdate("DROP TABLE [tinyintegerTable] "); 
        statement.executeUpdate("DROP TABLE [varcharTable] "); 
        statement.executeUpdate("DROP TABLE [timestampTable] "); 
        System.out.println("# Tables Deleted     #"); 
      } else 
        System.out.println("# Tables NOT Deleted #"); 
 
      System.out.println("######################"); 
      if (isExit) 
        System.exit(0); 
    } catch (SQLException e) 
    { 
      TLptsLogger.logWarning("SQL Exception", e.getLocalizedMessage(), e); 
    } 
  } 
} 
// 
 
 
//