Java Code Examples for java.sql.Statement#close()

/**
 * Tests updating a Blob from a scollable resultset produced by a
 * select query with projection. Updates are made using
 * result set update methods.
 * @exception SQLException causes test to fail with error
 * @exception IOException causes test to fail with error
 */
public void testUpdateBlobFromScrollableResultSetWithProjectUsingResultSetMethods()
    throws SQLException, IOException
{
    final Statement stmt = 
        createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
    final ResultSet rs = 
        stmt.executeQuery("SELECT data,val,length from " + 
                          BLOBDataModelSetup.getBlobTableName());
    println("Last");
    rs.last();
    
    final int newVal = rs.getInt(2) + 11;
    final int newSize = rs.getInt(3) / 2;
    testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
    
    println("Verify updated blob using result set");
    verifyBlob(newVal, newSize, rs.getBlob(1));
    
    rs.close();
    stmt.close();
}
 

/**
 * Test that % matches tab characters (DERBY-1262).
 */
public void testTabs() throws SQLException {
    Statement s = createStatement();
    s.executeUpdate("insert into test values " +
                    "('asd\tp', 'asd\tp', 'asd\tp'), " +
                    "('ase\tp', 'ase\tg', 'ase\tg')");

    String[][] expected = { {"asdf"}, {"asdg"}, {"asd\tp"} };
    JDBC.assertUnorderedResultSet(
        s.executeQuery("select c10 from test where c10 like 'asd%'"),
        expected);

    PreparedStatement ps =
        prepareStatement("select c10 from test where c10 like ?");
    ps.setString(1, "asd%");
    JDBC.assertUnorderedResultSet(ps.executeQuery(), expected);

    s.close();
    ps.close();
}
 

public static Connection getConnection()
throws SQLException
{
  Connection connection = AbstractDriverIT.getConnection();

  Statement statement = connection.createStatement();
  statement.execute(
      "alter session set " +
      "TIMEZONE='America/Los_Angeles'," +
      "TIMESTAMP_TYPE_MAPPING='TIMESTAMP_LTZ'," +
      "TIMESTAMP_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'," +
      "TIMESTAMP_TZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'," +
      "TIMESTAMP_LTZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'," +
      "TIMESTAMP_NTZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'");
  statement.close();
  return connection;
}
 

/**
 * 获得单元测试配置文件信息
 * 
 * @return
 * @throws Exception
 */
public static UnitTestConfig getUnitTestConfig(String name) throws Exception {
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	try {
		conn = getConnection();
		stat = conn.createStatement();
		String sql = String.format("select * from UnitTestConfig where name='%s'", name);
		ResultSet resultSet = stat.executeQuery(sql);
		while (resultSet.next()) {
			UnitTestConfig result = JSON.parseObject(resultSet.getString("value"), UnitTestConfig.class);
			return result;
		}
	} finally {
		if (rs != null)
			rs.close();
		if (stat != null)
			stat.close();
		if (conn != null)
			conn.close();
	}
	return null;
}
 

/**
 * Closes SQL Statement object without throwing Exceptions.
 * Exceptions are still loged as errors.
 * @param statements
 */
protected void closeSilently(Statement... statements) {
	for (Statement statement : statements) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				baseDaoImplLogger.error("Error occured: " + e.getMessage(), e);
			}
		}
	}
}
 

/**
 * Tests shutdown with the only transaction was committed.
 */
public void testOnlyTransactionWasCommited() throws SQLException {
    commit();
    assertShutdownOK();
    Statement st = createStatement();
    JDBC.assertSingleValueResultSet(st.executeQuery("select " + "count(*) "
            + "from " + "TEST_TABLE "), "1000");
    st.close();
}
 

/**
 * Tests shutdown with the transaction was rollbacked, and next transaction was not
 * committed/rollbacked yet.
 */

public void testOnceRollbackedAndLeft() throws SQLException {

    rollback();
    insertIntoTestTable(1001, 999);
    assertShutdownOK();
    Statement st = createStatement();
    JDBC.assertSingleValueResultSet(st.executeQuery("select " + "count(*) "
            + "from " + "TEST_TABLE "), "0");
    st.close();
}
 

/**
 * Test that each system table has a table type of "S".
 * 
 * @throws SQLException
 */
public void testSystemCatalogTableTypes() throws SQLException {
	Statement s = createStatement();
	ResultSet rs = s.executeQuery("select TABLENAME, TABLETYPE from sys.systables order by tablename");
	
	boolean nonEmptyResultSet = false;
	while(rs.next()) {
		nonEmptyResultSet  = true;
		assertEquals("S", rs.getString(2));
	}
	assertTrue(nonEmptyResultSet);
	rs.close();
	s.close();
}
 

protected void loadSingleSQL(Connection aConnection, String aResource)
		throws IOException, SQLException {

	String theSQL = readResourceFile(aResource);

	Statement theStatement = aConnection.createStatement();
	StringTokenizer theST = new StringTokenizer(theSQL, ";");
	while (theST.hasMoreTokens()) {
		String theSingleSQL = theST.nextToken();
		if (StringUtils.isNotEmpty(theSingleSQL)) {
			theStatement.execute(theSingleSQL);
		}
	}
	theStatement.close();
}
 

@Test
public void testPartitioned() throws Exception {
  // copy data file to test dir
  File dataDir = context.getDataDir();
  File dataFile = new File(dataDir, SINGLE_TYPE_DATA_FILE_NAME);
  FileOutputStream to = new FileOutputStream(dataFile);
  Resources.copy(Resources.getResource(SINGLE_TYPE_DATA_FILE_NAME), to);
  to.close();

  String viewName = "view1";
  String db = "db1";
  String tabName = "tab1";
  policyFile
      .addPermissionsToRole("view", "server=server1->db=" + db + "->table=" + viewName)
      .addRolesToGroup(USERGROUP1, "view")
      .setUserGroupMapping(StaticUserGroup.getStaticMapping());
  policyFile.write(context.getPolicyFile());

  //admin creates a view
  Connection conn = context.createConnection(ADMIN1);
  Statement stmt = context.createStatement(conn);
  stmt.execute("DROP DATABASE IF EXISTS " + db + " CASCADE");
  stmt.execute("CREATE DATABASE " + db);

  stmt.execute("use " + db);
  stmt.execute("create table " + tabName + " (id int) partitioned by (part string)");
  stmt.execute("load data local inpath '" + dataFile + "' into table " + tabName + " PARTITION (part=\"a\")");
  stmt.execute("load data local inpath '" + dataFile + "' into table " + tabName + " PARTITION (part=\"b\")");
  ResultSet res = stmt.executeQuery("select count(*) from " + tabName);
  Assert.assertThat(res, notNullValue());
  while(res.next()) {
    Assume.assumeTrue(res.getInt(1) == Integer.valueOf(1000));
  }
  stmt.execute("create view " + viewName + " as select * from " + tabName + " where id<100");
  res = stmt.executeQuery("select count(*) from " + viewName);
  Assert.assertThat(res, notNullValue());
  int rowsInView = 0;
  while(res.next()) {
    rowsInView = res.getInt(1);
  }
  stmt.close();
  conn.close();

  Connection userConn = context.createConnection(USER1_1);
  Statement userStmt = context.createStatement(userConn);
  userStmt.execute("use " + db);
  res = userStmt.executeQuery("select count(*) from " + viewName);
  Assert.assertThat(res, notNullValue());
  while(res.next()) {
    Assert.assertThat(res.getInt(1), is(rowsInView));
  }
  userStmt.close();
  userConn.close();

  // user2 hasn't the privilege for the view
  userConn = context.createConnection(USER2_1);
  userStmt = context.createStatement(userConn);
  try {
    userStmt.executeQuery("select count(*) from " + viewName);
    Assert.fail("Expected SQL exception");
  } catch (SQLException e) {
    // ignore the exception
  }
  userStmt.close();
  userConn.close();
}
 

/**
 * Execute and check the ODBC variant of getImported/Exported keys, which
 * uses the SQLFOREIGNKEYS system procedure to provide the same information
 * to ODBC clients.  Note that for "correctness" we just compare the results
 * to those of the equivalent JDBC calls; this fixture assumes that the
 * the JDBC calls return correct results (testing of the JDBC results occurs
 * elsewhere, see fixtures testGetXXportedKeys()
 */
public void testGetXXportedKeysODBC() throws SQLException, IOException
{
    Statement st = createStatement();

    // Create some simple tables with primary/foreign keys.

    st.execute("create table pkt1 (i int not null, c char(1) not null)");
    st.execute("create table pkt2 (i int not null, c char(1) not null)");
    st.execute("create table pkt3 (i int not null, c char(1) not null)");

    st.execute("alter table pkt1 add constraint pk1 primary key (i)");
    st.execute("alter table pkt2 add constraint pk2 primary key (c)");
    st.execute("alter table pkt3 add constraint pk3 primary key (i, c)");

    st.execute("create table fkt1 (fi int, fc char(1), vc varchar(80))");
    st.execute("create table fkt2 (fi int, fc char(1), vc varchar(80))");

    st.execute("alter table fkt1 add constraint fk1 foreign key (fi) " +
        "references pkt1(i)");

    st.execute("alter table fkt1 add constraint fk2 foreign key (fc) " +
        "references pkt2(c)");

    st.execute("alter table fkt2 add constraint fk3 foreign key (fi, fc) " +
        "references pkt3(i, c)");

    /* Check for all arguments NULL; SQLFOREIGNKEYS allows this, though
     * there is no equivalent in JDBC.
     */
    checkODBCKeys(null, null, null, null, null, null);

    /* Run equivalent of getImportedKeys(), getExportedKeys(),
     * and getCrossReference for each of the primary/foreign
     * key pairs.
     */

    checkODBCKeys(null, null, null, null, null, "FKT1");
    checkODBCKeys(null, null, "PKT1", null, null, null);
    checkODBCKeys(null, null, "PKT1", null, null, "FKT1");

    checkODBCKeys(null, null, null, null, null, "FKT2");
    checkODBCKeys(null, null, "PKT2", null, null, null);
    checkODBCKeys(null, null, "PKT2", null, null, "FKT2");

    checkODBCKeys(null, null, null, null, null, "FKT3");
    checkODBCKeys(null, null, "PKT3", null, null, null);
    checkODBCKeys(null, null, "PKT3", null, null, "FKT3");

    // Reverse primary and foreign tables.

    checkODBCKeys(null, null, "FKT1", null, null, null);
    checkODBCKeys(null, null, null, null, null, "PKT3");
    checkODBCKeys(null, null, "FKT1", null, null, "PKT1");
    checkODBCKeys(null, null, "FKT2", null, null, "PKT2");
    checkODBCKeys(null, null, "FKT3", null, null, "PKT3");

    // Mix-and-match primary key tables and foreign key tables.

    checkODBCKeys(null, null, "PKT1", null, null, "FKT2");
    checkODBCKeys(null, null, "PKT1", null, null, "FKT3");
    checkODBCKeys(null, null, "PKT2", null, null, "FKT3");

    checkODBCKeys(null, null, "FKT1", null, null, "PKT2");
    checkODBCKeys(null, null, "FKT1", null, null, "PKT3");
    checkODBCKeys(null, null, "FKT2", null, null, "PKT3");

    // Cleanup.

    st.execute("drop table fkt1");
    st.execute("drop table fkt2");
    st.execute("drop table pkt1");
    st.execute("drop table pkt2");
    st.execute("drop table pkt3");
    st.close();
}
 

/**
 * Validates a connection.
 * @param validateAction the action used. One of {@link #VALIDATE_BORROW}, {@link #VALIDATE_IDLE},
 * {@link #VALIDATE_INIT} or {@link #VALIDATE_RETURN}
 * @param sql the SQL to be used during validation. If the {@link PoolConfiguration#setInitSQL(String)} has been called with a non null
 * value and the action is {@link #VALIDATE_INIT} the init SQL will be used for validation.
 *
 * @return true if the connection was validated successfully. It returns true even if validation was not performed, such as when
 * {@link PoolConfiguration#setValidationInterval(long)} has been called with a positive value.
 * <p>
 * false if the validation failed. The caller should close the connection if false is returned since a session could have been left in
 * an unknown state during initialization.
 */
public boolean validate(int validateAction,String sql) {
    if (this.isDiscarded()) {
        return false;
    }

    if (!doValidate(validateAction)) {
        //no validation required, no init sql and props not set
        return true;
    }

    //Don't bother validating if already have recently enough
    long now = System.currentTimeMillis();
    if (validateAction!=VALIDATE_INIT &&
        poolProperties.getValidationInterval() > 0 &&
        (now - this.lastValidated) <
        poolProperties.getValidationInterval()) {
        return true;
    }

    if (poolProperties.getValidator() != null) {
        if (poolProperties.getValidator().validate(connection, validateAction)) {
            this.lastValidated = now;
            return true;
        } else {
            if (getPoolProperties().getLogValidationErrors()) {
                log.error("Custom validation through "+poolProperties.getValidator()+" failed.");
            }
            return false;
        }
    }

    String query = sql;

    if (validateAction == VALIDATE_INIT && poolProperties.getInitSQL() != null) {
        query = poolProperties.getInitSQL();
    }

    if (query == null) {
        query = poolProperties.getValidationQuery();
    }

    Statement stmt = null;
    try {
        stmt = connection.createStatement();

        int validationQueryTimeout = poolProperties.getValidationQueryTimeout();
        if (validationQueryTimeout > 0) {
            stmt.setQueryTimeout(validationQueryTimeout);
        }

        stmt.execute(query);
        stmt.close();
        this.lastValidated = now;
        return true;
    } catch (Exception ex) {
        if (getPoolProperties().getLogValidationErrors()) {
            log.warn("SQL Validation error", ex);
        } else if (log.isDebugEnabled()) {
            log.debug("Unable to validate object:",ex);
        }
        if (stmt!=null)
            try { stmt.close();} catch (Exception ignore2){/*NOOP*/}
    }
    return false;
}
 

public void execUpdate(String update) throws SQLException {
    Statement stmt = connection.createStatement();
    stmt.execute(update);
    stmt.close();
}
 

@Test
public void testOnFailureHookLoading() throws Exception {

  // Do not run this test if run with external HiveServer2
  // This test checks for a static member, which will not
  // be set if HiveServer2 and the test run in different JVMs
  String hiveServer2Type = System.getProperty(
      HiveServerFactory.HIVESERVER2_TYPE);
  if (hiveServer2Type != null &&
      !HiveServerFactory.isInternalServer(HiveServerFactory.HiveServer2Type
          .valueOf(hiveServer2Type.trim()))) {
    return;
  }

  context = createContext(testProperties);

  File dataDir = context.getDataDir();
  //copy data file to test dir
  File dataFile = new File(dataDir, SINGLE_TYPE_DATA_FILE_NAME);
  FileOutputStream to = new FileOutputStream(dataFile);
  Resources.copy(Resources.getResource(SINGLE_TYPE_DATA_FILE_NAME), to);
  to.close();

  policyFile
      .addRolesToGroup(USERGROUP1, "all_db1", "load_data")
      .addPermissionsToRole("all_db1", "server=server1->db=DB_1")
      .setUserGroupMapping(StaticUserGroup.getStaticMapping())
      .write(context.getPolicyFile());

  // setup db objects needed by the test
  Connection connection = context.createConnection(ADMIN1);
  Statement statement = context.createStatement(connection);
  statement.execute("DROP DATABASE IF EXISTS DB_1 CASCADE");
  statement.execute("DROP DATABASE IF EXISTS DB_2 CASCADE");
  statement.execute("CREATE DATABASE DB_1");
  statement.execute("CREATE DATABASE DB_2");
  statement.close();
  connection.close();

  // test execution
  connection = context.createConnection(USER1_1);
  statement = context.createStatement(connection);

  //negative test case: user can't drop another user's database
  assertFalse(DummySentryOnFailureHook.invoked);
    try {
    statement.execute("DROP DATABASE DB_2 CASCADE");
    Assert.fail("Expected SQL exception");
  } catch (SQLException e) {
    assertTrue(DummySentryOnFailureHook.invoked);
  }

  statement.close();
  connection.close();

  //test cleanup
  connection = context.createConnection(ADMIN1);
  statement = context.createStatement(connection);
  statement.execute("DROP DATABASE DB_1 CASCADE");
  statement.execute("DROP DATABASE DB_2 CASCADE");
  statement.close();
  connection.close();
  context.close();
}
 

/**
 * Test various default store properties for the system tables.
 *
 * @throws SQLException
 */
//FIXME
// GemFireXD returns different values for this function, need to tweak this testcase
// but comment out for now
//public void testSystemCatalogStoreProperties() throws SQLException{
public void _testSystemCatalogStoreProperties() throws SQLException{
  Statement s = createStatement();
  s.execute("create function gatp(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllTableProperties' LANGUAGE JAVA PARAMETER STYLE JAVA");
  s.execute("create function gaip(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllIndexProperties' LANGUAGE JAVA PARAMETER STYLE JAVA");

  // get the properties for the heaps
  ResultSet rs = s.executeQuery("select tablename,gatp('SYS', tablename) from sys.systables order by tablename");
  boolean nonEmptyResultSet = false;
  String tablename = null;
  String sysdummy = "SYSDUMMY1";
  while(rs.next()) {
    nonEmptyResultSet  = true;
    tablename = rs.getString(1);
    if (tablename.equals(sysdummy)) {
      assertTrue(rs.getString(2).startsWith("{  }"));
    } else {
      String expected = "{ GEMFIRE_REGION_ATTRIBUTES=MISSING_VALUE, ddIndex=MISSING_VALUE, gemfirexd.schema-name=SYS, gemfirexd.table-name="+tablename+" }";
      assertTrue(rs.getString(2).startsWith(expected));
    }
  }
  assertTrue(nonEmptyResultSet);
  rs.close();

  // get the properties for the indexes
  rs = s.executeQuery("select conglomeratename, gaip('SYS',	conglomeratename) from sys.sysconglomerates where isindex order by conglomeratename");
  nonEmptyResultSet = false;
  // GemStone changes BEGIN
  //String expected = "{ baseConglomerateId=MISSING_VALUE, ddIndex=MISSING_VALUE, indexComparator=MISSING_VALUE, gemfirexd.schema-name=SYS, gemfirexd.table-name=";
  ///String expectedRegex = "\\{ baseConglomerateId=MISSING_VALUE, ddIndex=MISSING_VALUE, gemfirexd.schema-name=SYS, gemfirexd.table-name=\\d*, indexComparator=MISSING_VALUE \\}";
  String expectedRegex = "\\{ baseConglomerateId=MISSING_VALUE, ddIndex=MISSING_VALUE, indexComparator=MISSING_VALUE, gemfirexd.schema-name=SYS, gemfirexd.table-name=\\d* \\}";
  // GemStone changes END
  while(rs.next()) {
    nonEmptyResultSet  = true;
    // GemStone changes BEGIN
    //assertTrue(rs.getString(2).startsWith(expected));
    if (!Pattern.matches(expectedRegex, rs.getString(2))) {
      fail("Conglomerate row "+rs.getString(2)+" does not match " + expectedRegex);
    }
    // GemStone changes END
  }
  assertTrue(nonEmptyResultSet);
    rs.close();
    s.close();
}
 

/**
 * Transactions with indexes.
 */
public void testTransactionsAndIndexMaintenance() throws Exception {
  Connection conn= getConnection();
  Statement st = conn.createStatement();
  st.execute("create schema test");
  st.execute("create table test.t1 ( PkCol1 int not null, PkCol2 int not null , " +
      "col3 int, col4 int, col5 varchar(10), Primary Key (PkCol1) ) "+
      "Partition by column (PkCol1)"+getSuffix());
  conn.commit();
  st.execute("create index IndexCol4 on test.t1 (col4)");
  conn.commit();
  conn.setTransactionIsolation(getIsolationLevel());
  final int rows = 1000;
  
  final CheckIndexOperations checkIndex = new CheckIndexOperations("Test.IndexCol4");
  try {
    GemFireXDQueryObserverHolder.putInstance(checkIndex);
    PreparedStatement psInsert = conn.prepareStatement("insert into test.t1 "
        + "values (?,10,10,10,'XXXX1')");
    for (int i = 0; i < rows; i++) {
      psInsert.setInt(1, i);
      psInsert.executeUpdate();
      conn.commit();
    }
    checkIndex.checkNumInserts(rows);
    
  } finally {      
    GemFireXDQueryObserverHolder.removeObserver(checkIndex);      
  }
  //conn.commit();
  ResultSet rs = st.executeQuery("select * from test.t1 where col4 = 10");
  int numRows = 0;
  while (rs.next()) {
    assertEquals("Should return correct result ", 10, rs.getInt("COL4"));
    numRows++;
  }
  assertEquals("Should return 1000 rows ", rows, numRows);
  rs.close();
  PreparedStatement psUpdate = conn.prepareStatement("update test.t1 set " +
  "col3 = 20, col4 = 20, col5 = 'changed' where PkCol1=?");
  try {
    GemFireXDQueryObserverHolder.putInstance(checkIndex);
  for(int i = 0; i < rows ; i++) {
    psUpdate.setInt(1, i);
    psUpdate.executeUpdate();
    conn.commit();
  }
  } finally {
    GemFireXDQueryObserverHolder.removeObserver(checkIndex);      
  }
  // TODO fix the observer index maintenance increment logic with the new model.
  //checkIndex.checkNumInserts(2 * rows);
  //checkIndex.checkNumDeletes(rows);
  rs = st.executeQuery("select * from test.t1 where col4 = 20 " +
  		"order by PkCol1 asc");
  numRows = 0;
  while (rs.next()) {
    assertEquals("Should return correct result ", 20, rs.getInt("COL4"));
    assertEquals("Should return correct result ", 20, rs.getInt("COL3"));
    assertEquals("Should return correct result ", "changed",
        rs.getString("COL5").trim());
    assertEquals("Should return correct result ", numRows,
        rs.getInt("PKCOL1"));
    numRows++;
  }
  assertEquals("Should return 1000 rows ", rows, numRows);
  st.close();
  conn.commit();
}
 

/**
 * Test multiple commit and rollback.
 * 
 * @throws Exception
 */
public void testCommitAndRollBack() throws Exception {
  startVMs(1, 2);
  // TestUtil.loadDriver();
  
  Properties props = new Properties();
  props.setProperty(Attribute.TX_SYNC_COMMITS, "true");
  final Connection conn = TestUtil.getConnection(props);
  conn.setAutoCommit(false);
  Statement st = conn.createStatement();
  st.execute("create table T1 ( PkCol1 int not null, PkCol2 int not null , "
      + "col3 int, col4 int, col5 varchar(10), Primary Key (PkCol1, PkCol2)) "
      + "Partition by column (PkCol1)"+ getSuffix());
  conn.commit();
  
  conn.setTransactionIsolation(getIsolationLevel());
  PreparedStatement psInsert = conn.prepareStatement("insert into t1 "
      + "values(?, ?, ?, ?, ?)");
  for (int i = 0; i < 1000; i++) {
    psInsert.setInt(1, i);
    psInsert.setInt(2, i);
    psInsert.setInt(3, i);
    psInsert.setInt(4, i);
    psInsert.setString(5, "XXXX" + i);
    psInsert.executeUpdate();
    if ((i % 2) == 0) {
      conn.commit();
    }
    else {
      conn.rollback();
    }
  }
  
  // approx. 240 commits/rollbacks gets distributed across 2 nodes. adjust these numbers to a little lower value
  // if unbalanced commits/rollbacks happen.
  checkTxStatistics("commit-afterInserts", 240, 240, 240, 240, 500, 501); 
  
  ResultSet rs = st.executeQuery("select * from t1");
  int numRows = 0;
  while (rs.next()) {
    numRows++;
  }
  
  assertEquals("Table should have 500 rows", 500, numRows);
  rs.close();
  st.close();
  conn.commit();
  conn.close();
  checkTxStatistics("commit-afterSelects", 240, 240, 240, 240, 500, 501);
}
 

/** Add an user to the DB.
 * @return String */
public String addUser() {
	HttpSession session = ServletActionContext.getRequest().getSession();
	User us = new User();
	
	// Save fields
	us.setUsername(this.usernameForm);
	us.setPassword(this.passwordForm);
	us.setRepeatPassword(this.repeatPasswordForm);
	us.setEmail(this.emailForm);
	us.setRole(String.valueOf(this.roleForm));
	us.setType(String.valueOf(this.typeForm));
	us.setOrganisation(String.valueOf(this.organisationForm));
	
	session.setAttribute("newUser", us);
	
    Connection connection = null;
    try {
        connection = new DBConnectionManager().getConnection();
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM aliada.user WHERE user_name='" + getUsernameForm() + "'");
        if (!rs.next()) {
            statement = connection.createStatement();
            
            if (this.passwordForm.equals(this.repeatPasswordForm)) {
            	// Encrypt password
             StrongPasswordEncryptor passwordEncryptor = new StrongPasswordEncryptor();
             String encryptedPassword = passwordEncryptor.encryptPassword(this.passwordForm);
             
             statement.executeUpdate("INSERT INTO aliada.user VALUES ('" + this.usernameForm + "', '"
               + encryptedPassword + "', '" + this.emailForm + "', '" + this.typeForm + "', '" + this.roleForm + "', '" + this.organisationForm + "')");
             addActionMessage(getText("user.save.ok"));
             rs.close();
             statement.close();
             connection.close(); 
             getUsersDb();
             session.setAttribute("newUser", null);
            } else {
            	rs.close();
             statement.close();
             connection.close();
            	addFieldError("passwordForm", getText("userPassword.not.equals"));
            	addFieldError("repeatPasswordForm", getText("userPassword.not.equals"));
            	return ERROR;
            }
        } else {
            rs.close();
            statement.close();
            connection.close();
            addFieldError("usernameForm", getText("err.duplicate.user"));
            return ERROR;
        }         	
    } catch (SQLException e) {
        logger.error(MessageCatalog._00011_SQL_EXCEPTION, e);
        return ERROR;
    }
    return SUCCESS;        
}
 

/**
 * Test 7 - Online compress test for fetching more rows than buffer limit.
 * <p>
 * For smaller row size, if number of rows per page is more than max buffer
 * size, then check if the remaining rows are also fetched for Compress 
 * Operation
 * <p>
 **/
private void test7(
Connection  conn,
String      test_name,
String      table_name)
    throws SQLException 
{
    beginTest(conn, test_name);

    Statement s = conn.createStatement();

    s.execute("create table " + table_name + "(keycol int)");
    s.close();
    PreparedStatement insert_stmt = 
        conn.prepareStatement("insert into " + table_name + " values(?)");
    try
    {
        for (int i = 0; i < 1200; i++)
        {
            insert_stmt.setInt(1, i);

            insert_stmt.execute();
        }
    }
    catch (SQLException sqle)
    {
        System.out.println(
            "Exception while trying to insert a row");
        throw sqle;
    }
    conn.commit();

    // delete the front rows leaving the last 200.  Post commit may reclaim
    // space on pages where all rows are deleted.  
    executeQuery(
        conn, "delete from " + table_name + " where keycol < 1000", true);

    conn.commit();

    if (verbose)
        testProgress("deleted first 1000 rows, now calling compress.");

    callCompress(conn, "APP", table_name, true, true, true, true);

    testProgress("delete rows case succeeded.");

    executeQuery(conn, "drop table " + table_name, true);

    endTest(conn, test_name);
}
 

/**
 * Test the firing order of triggers. Should be:
 * 
 * Before operations
 * after operations
 * 
 * For multiple triggers within the same group (before or after)
 * firing order is determined by create order.
 * @throws SQLException 
 *
 */
public void testFiringOrder() throws SQLException
{
    Statement s = createStatement();
    s.executeUpdate("CREATE TABLE T(ID INT)");
    
    int triggerCount = createRandomTriggers()[0];
    
    List info = new ArrayList();
    TRIGGER_INFO.set(info);
    
    // Check ordering with a single row.
    s.execute("INSERT INTO T VALUES 1");
    commit();
    int fireCount = assertFiringOrder("INSERT", 1);
    info.clear();
    
    s.execute("UPDATE T SET ID = 2");
    commit();
    fireCount += assertFiringOrder("UPDATE", 1);
    info.clear();
    
    s.execute("DELETE FROM T");
    commit();
    fireCount += assertFiringOrder("DELETE", 1);
    info.clear();
       
    assertEquals("All triggers fired?", triggerCount, fireCount);

    // and now with multiple rows
    s.execute("INSERT INTO T VALUES 1,2,3");
    commit();
    fireCount = assertFiringOrder("INSERT", 3);
    info.clear();
    
    s.execute("UPDATE T SET ID = 2");
    commit();
    fireCount += assertFiringOrder("UPDATE", 3);
    info.clear();
    
    s.execute("DELETE FROM T");
    commit();
    fireCount += assertFiringOrder("DELETE", 3);
    info.clear();
    
    // cannot assume row triggers were created so can only
    // say that at least all the triggers were fired.
    assertTrue("Sufficient triggers fired?", fireCount >= triggerCount);
    
    
    // and then with no rows
    assertTableRowCount("T", 0);
    s.execute("INSERT INTO T SELECT ID FROM T");
    commit();
    fireCount = assertFiringOrder("INSERT", 0);
    info.clear();
    
    s.execute("UPDATE T SET ID = 2");
    commit();
    fireCount += assertFiringOrder("UPDATE", 0);
    info.clear();
    
    s.execute("DELETE FROM T");
    commit();
    fireCount += assertFiringOrder("DELETE", 0);
    info.clear();
    
    // can't assert anthing about fireCount, could be all row triggers.
        
    s.close();

}