Java Code Examples for java.sql.Connection#commit()

@Test
public void testArrayConstructorWithMultipleRows4() throws Exception {

    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String table = generateUniqueName();
    String ddl = "CREATE TABLE  " + table + "  (region_name VARCHAR PRIMARY KEY, a VARCHAR, b VARCHAR)";
    conn.createStatement().execute(ddl);
    conn.commit();
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    PreparedStatement stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('a', 'foo', 'abc')");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('b', 'abc', 'dfg')");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('c', 'foo', 'abc')");
    stmt.execute();
    conn.commit();
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT COUNT(DISTINCT ARRAY[a,b]) from  " + table);
    assertTrue(rs.next());
    assertEquals(2, rs.getInt(1));
}
 

private static void deleteBORawData(Connection pCtx) throws SQLException {
  int currentBatchSize = 0;
  ResultSet result = getExpiredBOIds(pCtx);
  PreparedStatement deleteStmt=pCtx.prepareStatement(DELETE_BO_RAW_DATA);
  while (result.next()) {
    deleteStmt.setString(1, result.getString("BO_TXN_ID"));
    deleteStmt.addBatch();
    currentBatchSize++;
    if (currentBatchSize > maxBatchSize) {
      deleteStmt.executeBatch();
      pCtx.commit();
    }
    deleteStmt.executeBatch();
    pCtx.commit();
  }
}
 

@Test
public void shouldPass() throws Exception {
	Connection conn = DriverManager.getConnection(getUrl());

	String testTable = generateUniqueName();
	String ddl = "CREATE TABLE " + testTable
			+ " ( some_column BINARY(12) NOT NULL CONSTRAINT PK PRIMARY KEY (some_column))";

	conn.createStatement().execute(ddl);
	PreparedStatement ps = conn.prepareStatement(
			"UPSERT INTO " + testTable + " (some_column) VALUES (?)");

	byte[] kk = Bytes.add(PUnsignedLong.INSTANCE.toBytes(2232594215l), PInteger.INSTANCE.toBytes(-8));
	ps.setBytes(1, kk);

	ps.execute();
	conn.commit();

	ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + testTable
			+ " WHERE some_column = DECODE('000000008512af277ffffff8', 'hex')");
	assertTrue(rs.next());
}
 

public void testBug42067_2() throws Exception {

    // Create the controller VM as client which belongs to default server group
    startClientVMs(1, 0, null);
    startServerVMs(2, -1, "SG1");
    Connection conn = TestUtil.jdbcConn;
    conn.setTransactionIsolation(getIsolationLevel());
    conn.setAutoCommit(false);
    // create table
    clientSQLExecute(1, "Create table t1 (c1 int not null primary key, "
        + "c2 int not null, c3 int not null, c4 int not null) "
        + "redundancy 1 partition by column (c1) "+ getSuffix());
    conn.commit();
    Statement st = conn.createStatement();
    st.execute("insert into t1 values (1, 1,1,1)");
    st.execute("insert into t1 values (114, 114,114,114)");
    conn.commit();
    st.execute("delete from t1 where c1 =1 and c3 =1");
    st.execute("update t1 set c2 =2 where c1 =1 and c3 =1");
    conn.commit();
    ResultSet rs = st.executeQuery("select * from t1");
    assertTrue(rs.next());
    assertEquals(114, rs.getInt(1));
    assertFalse(rs.next());
  }
 

/**
 * Commits the current transaction.
 * @param ds
 * @return
 * @throws SQLException
 */
public boolean commitTransaction(final DataSource ds) throws SQLException {
	final Connection c = transactionConnections.remove(ds);
	if (c == null) return false;
	if (Constants.DB_TYPE.detect(ds)==Constants.DB_TYPE.SQLITE3) {
		Statement stmt = c.createStatement();
		try {
			String sql = "commit";
			Util.log(sql, null);
			stmt.execute(sql);
		} finally {
			stmt.close();
		}
	} else {
		Util.log("connection.commit()", null);
		c.commit();
	}
	c.close();
	return true;
}
 

public void testJSON_TX() throws Exception {
  startVMs(1, 3, 0, null, null);
  Connection cxn = TestUtil.getConnection();
  cxn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  cxn.setAutoCommit(false);
  boolean isOffHeap = (getSuffix() != "");
  JsonTest.simpleJSONOps(cxn, true, isOffHeap);
  cxn.commit();
}
 

public String getAuthzCodeByCodeId(String codeId) throws IdentityOAuth2Exception {

        Connection connection = IdentityDatabaseUtil.getDBConnection();

        PreparedStatement prepStmt = null;
        ResultSet resultSet = null;
        try {
            String sql = SQLQueries.RETRIEVE_AUTHZ_CODE_BY_CODE_ID;

            prepStmt = connection.prepareStatement(sql);
            prepStmt.setString(1, codeId);
            resultSet = prepStmt.executeQuery();

            if (resultSet.next()) {
                return resultSet.getString("AUTHORIZATION_CODE");
            }
            connection.commit();
            return null;

        } catch (SQLException e) {
            String errorMsg = "Error occurred while retrieving 'Authorization Code' for " +
                    "authorization code : " + codeId;
            throw new IdentityOAuth2Exception(errorMsg, e);
        } finally {
            IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
        }

    }
 

@BeforeClass
public static void createTable() throws SQLException, ClassNotFoundException {
    Class.forName("org.hsqldb.jdbcDriver");

    final Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
    final Statement statement = connection.createStatement();
    statement.execute("CREATE TABLE " + TABLE + "(ID INTEGER)");
    statement.close();
    connection.commit();
    connection.close();
}
 

@BeforeClass
public static void initDatabase() throws Exception {
  Connection conn = null;

  try {
    Class.forName("org.hsqldb.jdbcDriver");
    conn = DriverManager.getConnection("jdbc:hsqldb:mem:cglib_lazy_error", "sa",
        "");

    Reader reader = Resources.getResourceAsReader("org/apache/ibatis/submitted/cglib_lazy_error/CreateDB.sql");

    ScriptRunner runner = new ScriptRunner(conn);
    runner.setLogWriter(null);
    runner.setErrorLogWriter(null);
    runner.runScript(reader);
    conn.commit();
    reader.close();

    reader = Resources.getResourceAsReader("org/apache/ibatis/submitted/cglib_lazy_error/ibatisConfigLazy.xml");
    sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    sqlSessionFactory.getConfiguration().setLazyLoadingEnabled(true);
    sqlSessionFactory.getConfiguration().setAggressiveLazyLoading(false);
    reader.close();
  } finally {
    if (conn != null) {
      conn.close();
    }
  }
}
 

public void persistAuthorizationCode(String authzCode, String consumerKey, String callbackUrl,
                                     AuthzCodeDO authzCodeDO) throws IdentityOAuth2Exception {

    if (!enablePersist) {
        return;
    }

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    try {
        prepStmt = connection.prepareStatement(SQLQueries.STORE_AUTHORIZATION_CODE);
        prepStmt.setString(1, authzCodeDO.getAuthzCodeId());
        prepStmt.setString(2, persistenceProcessor.getProcessedAuthzCode(authzCode));
        prepStmt.setString(3, callbackUrl);
        prepStmt.setString(4, OAuth2Util.buildScopeString(authzCodeDO.getScope()));
        prepStmt.setString(5, authzCodeDO.getAuthorizedUser().getUserName());
        prepStmt.setString(6, authzCodeDO.getAuthorizedUser().getUserStoreDomain());
        int tenantId = OAuth2Util.getTenantId(authzCodeDO.getAuthorizedUser().getTenantDomain());
        prepStmt.setInt(7, tenantId);
        prepStmt.setTimestamp(8, authzCodeDO.getIssuedTime(),
                              Calendar.getInstance(TimeZone.getTimeZone(UTC)));
        prepStmt.setLong(9, authzCodeDO.getValidityPeriod());
        prepStmt.setString(10, authzCodeDO.getAuthorizedUser().getAuthenticatedSubjectIdentifier());
        prepStmt.setString(11, persistenceProcessor.getProcessedClientId(consumerKey));
        prepStmt.execute();
        connection.commit();
    } catch (SQLException e) {
        throw new IdentityOAuth2Exception("Error when storing the authorization code for consumer key : " +
                consumerKey, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
}
 

public static void commitAndRelease() throws SQLException {
	Connection con = getCurrentConnection();
	if (con != null) {
		con.commit(); // 事务提交
		con.close();// 关闭资源
		tl.remove();// 从线程绑定中移除
	}
}
 

public void testConcConnectionsTwo() throws Exception {
  System.out.println("Conc Connections Test Two starting");

  //Open 1st  connection
  Connection conn1 = getConnection();
  Connection conn4 = getConnection();
  Connection conn5 = getConnection();
  Connection conn6 = getConnection();
  Connection conn7 = getConnection();
  if (conn1 == null
      || conn4 == null
      || conn5 == null
      || conn6 == null
      || conn7 == null
  ) {
    fail("Concurrent connections test two - Failed to open a connection");
  }
  System.out.println("Conc Connections Test Two opened 5 connections");
  Connection conn8 = getConnection();
  Connection conn9 = getConnection();
  Connection conn10 = getConnection();
  metadataCalls(conn1);

  Connection conn2= getConnection();

  metadataCalls(conn2);

  Connection conn3 = getConnection();
  metadataCalls(conn3);
  if (conn2 == null
      || conn3 == null
      || conn8 == null
      || conn9 == null
      || conn10 == null
  ) {
    fail("Concurrent connections test two - Failed to open a connection");
  }
  System.out.println("Conc Connections Test Two opened 10 connections");

  conn1.commit();
  conn2.commit();
  conn3.commit();
  conn4.commit();
  conn5.commit();
  conn6.commit();
  conn7.commit();
  conn8.commit();
  conn9.commit();
  conn10.commit();
  System.out.println("Conc Connections Test Two committed  10 connections");

  System.out.println("Conc Connections Test Two finishes.");
}
 

@Test
public void testSkipScanFilterWhenTableHasMultipleColumnFamilies() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(false);
    try {
        createMultiCFTestTable(TestUtil.DEFAULT_DATA_TABLE_FULL_NAME);
        populateMultiCFTestTable(TestUtil.DEFAULT_DATA_TABLE_FULL_NAME);
        String upsert = "UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_FULL_NAME
                + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = conn.prepareStatement(upsert);
        stmt.setString(1, "varchar4");
        stmt.setString(2, "char1");
        stmt.setInt(3, 1);
        stmt.setLong(4, 1L);
        stmt.setBigDecimal(5, new BigDecimal("1.1"));
        stmt.setString(6, "varchar_a");
        stmt.setString(7, "chara");
        stmt.setInt(8, 2);
        stmt.setLong(9, 2L);
        stmt.setBigDecimal(10, new BigDecimal("2.1"));
        stmt.setString(11, "varchar_b");
        stmt.setString(12, "charb");
        stmt.setInt(13, 3);
        stmt.setLong(14, 3L);
        stmt.setBigDecimal(15, new BigDecimal("3.1"));
        stmt.setDate(16, null);
        stmt.executeUpdate();
        
        stmt.setString(1, "varchar5");
        stmt.setString(2, "char2");
        stmt.setInt(3, 2);
        stmt.setLong(4, 2L);
        stmt.setBigDecimal(5, new BigDecimal("2.2"));
        stmt.setString(6, "varchar_a");
        stmt.setString(7, "chara");
        stmt.setInt(8, 3);
        stmt.setLong(9, 3L);
        stmt.setBigDecimal(10, new BigDecimal("3.2"));
        stmt.setString(11, "varchar_b");
        stmt.setString(12, "charb");
        stmt.setInt(13, 4);
        stmt.setLong(14, 4L);
        stmt.setBigDecimal(15, new BigDecimal("4.2"));
        stmt.setDate(16, null);
        stmt.executeUpdate();
        
        stmt.setString(1, "varchar6");
        stmt.setString(2, "char3");
        stmt.setInt(3, 3);
        stmt.setLong(4, 3L);
        stmt.setBigDecimal(5, new BigDecimal("3.3"));
        stmt.setString(6, "varchar_a");
        stmt.setString(7, "chara");
        stmt.setInt(8, 4);
        stmt.setLong(9, 4L);
        stmt.setBigDecimal(10, new BigDecimal("4.3"));
        stmt.setString(11, "varchar_b");
        stmt.setString(12, "charb");
        stmt.setInt(13, 5);
        stmt.setLong(14, 5L);
        stmt.setBigDecimal(15, new BigDecimal("5.3"));
        stmt.setDate(16, null);
        stmt.executeUpdate();
        conn.commit();
        String query = "SELECT char_col1, int_col1, long_col2 from " + TestUtil.DEFAULT_DATA_TABLE_FULL_NAME + " where varchar_pk in ('varchar3','varchar6')";
        ResultSet rs = conn.createStatement().executeQuery(query);
        assertTrue(rs.next());
        assertEquals("chara", rs.getString(1));
        assertEquals(4, rs.getInt(2));
        assertEquals(5L, rs.getLong(3));
        assertTrue(rs.next());
        assertEquals("chara", rs.getString(1));
        assertEquals(4, rs.getInt(2));
        assertEquals(5L, rs.getLong(3));
        assertFalse(rs.next());
        
    } finally {
        conn.close();
    }
}
 

private boolean insertItems(Connection con, Collection<Item> items, Integer playerId, Integer accountId, Integer legionId) {

		if (GenericValidator.isBlankOrNull(items)) {
			return true;
		}

		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(INSERT_QUERY);

			for (Item item : items) {
				stmt.setInt(1, item.getObjectId());
				stmt.setInt(2, item.getItemTemplate().getTemplateId());
				stmt.setLong(3, item.getItemCount());
				stmt.setInt(4, item.getItemColor());
				stmt.setInt(5, item.getColorExpireTime());
				stmt.setString(6, item.getItemCreator());
				stmt.setInt(7, item.getExpireTime());
				stmt.setInt(8, item.getActivationCount());
				stmt.setInt(9, getItemOwnerId(item, playerId, accountId, legionId));
				stmt.setBoolean(10, item.isEquipped());
				stmt.setInt(11, item.isSoulBound() ? 1 : 0);
				stmt.setLong(12, item.getEquipmentSlot());
				stmt.setInt(13, item.getItemLocation());
				stmt.setInt(14, item.getItemTemplate().getMaxAuthorize() > 0 ? 0 : item.getEnchantOrAuthorizeLevel());
				stmt.setInt(15, item.getItemSkinTemplate().getTemplateId());
				stmt.setInt(16, item.getFusionedItemId());
				stmt.setInt(17, item.getOptionalSocket());
				stmt.setInt(18, item.getOptionalFusionSocket());
				stmt.setInt(19, item.getChargePoints());
				stmt.setInt(20, item.getBonusNumber());
				stmt.setInt(21, item.getRandomCount());
				stmt.setInt(22, item.getPackCount());
				stmt.setInt(23, item.getItemTemplate().getMaxAuthorize() > 0 ? item.getEnchantOrAuthorizeLevel() : 0);
				stmt.setBoolean(24, item.isPacked());
				stmt.setBoolean(25, item.isAmplified());
				stmt.setInt(26, item.getAmplificationSkill());
				stmt.setInt(27, item.getReductionLevel());
				stmt.setBoolean(28, item.isLunaReskin());
				stmt.setBoolean(29, item.isEnhance());
				stmt.setInt(30, item.getEnhanceSkillId());
				stmt.setInt(31, item.getEnhanceEnchantLevel());
				stmt.setInt(32, item.getUnSeal());
				stmt.setInt(33, item.getItemSkinSkill());
				stmt.addBatch();
			}

			stmt.executeBatch();
			con.commit();
		}
		catch (Exception e) {
			log.error("Failed to execute insert batch", e);
			return false;
		}
		finally {
			DatabaseFactory.close(stmt);
		}
		return true;
	}
 

/**
 * Initialize the tables
 *
 * @throws BlockStoreException
 *             Unable to initialize the database tables
 */
private void initTables() throws BlockStoreException {
	Connection conn = getConnection();
	try {
		conn.setAutoCommit(false);
		//
		// Initialize the block chain with the genesis block
		//
		Block genesisBlock = new Block(BTCLoader.GENESIS_BLOCK_BYTES, 0, BTCLoader.GENESIS_BLOCK_BYTES.length,
				false);
		chainHead = genesisBlock.getHash();
		prevChainHead = Sha256Hash.ZERO_HASH;
		chainHeight = 0;
		chainWork = BigInteger.ONE;
		targetDifficulty = NetParams.MAX_TARGET_DIFFICULTY;
		blockFileNumber = 0;
		chainTime = genesisBlock.getTimeStamp();
		//
		// Initialize the Settings table
		//
		try (PreparedStatement s = conn
				.prepareStatement("INSERT INTO Settings (schema_name,schema_version) VALUES(?,?)")) {
			s.setString(1, schemaName);
			s.setInt(2, schemaVersion);
			s.executeUpdate();
		}
		//
		// Add the genesis block to the Blocks table
		//
		try (PreparedStatement s = conn
				.prepareStatement("INSERT INTO Blocks(block_hash_index,block_hash,prev_hash_index,prev_hash,"
						+ "block_height,timestamp,chain_work,on_hold,"
						+ "file_number,file_offset,header) VALUES(?,?,?,?,0,?,?,false,0,0,?)")) {
			s.setLong(1, getHashIndex(chainHead));
			s.setBytes(2, chainHead.getBytes());
			s.setLong(3, getHashIndex(prevChainHead));
			s.setBytes(4, prevChainHead.getBytes());
			s.setLong(5, chainTime);
			s.setBytes(6, chainWork.toByteArray());
			s.setBytes(7, genesisBlock.getHeaderBytes());
			s.executeUpdate();
		}
		//
		// Copy the genesis block as the initial block file
		//
		File blockFile = new File(String.format("%s%sBlocks%sblk00000.dat", dataPath, LSystem.FS, LSystem.FS));
		if (!existsBlock) {
			existsBlock = blockFile.exists();
			if (!existsBlock) {
				FileUtils.makedirs(blockFile);
			}
		}
		try (FileOutputStream outFile = new FileOutputStream(blockFile)) {
			byte[] prefixBytes = new byte[8];
			Helper.uint32ToByteArrayLE(NetParams.MAGIC_NUMBER, prefixBytes, 0);
			Helper.uint32ToByteArrayLE(BTCLoader.GENESIS_BLOCK_BYTES.length, prefixBytes, 4);
			outFile.write(prefixBytes);
			outFile.write(BTCLoader.GENESIS_BLOCK_BYTES);
		}
		//
		// All done - commit the updates
		//
		conn.commit();
		conn.setAutoCommit(true);
		BTCLoader.info(String.format("Database initialized with schema version %d.%d", schemaVersion / 100,
				schemaVersion % 100));
	} catch (IOException | SQLException | VerificationException exc) {
		BTCLoader.error("Unable to initialize the database tables", exc);
		rollback();
		throw new BlockStoreException("Unable to initialize the database tables");
	}
}
 

@Test
public void testDropCoveredColumn() throws Exception {
    ResultSet rs;
    PreparedStatement stmt;

    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(false);

    // make sure that the tables are empty, but reachable
    conn.createStatement().execute(
      "CREATE TABLE " + DATA_TABLE_FULL_NAME
          + " (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR, v3 VARCHAR)");
    String dataTableQuery = "SELECT * FROM " + DATA_TABLE_FULL_NAME;
    rs = conn.createStatement().executeQuery(dataTableQuery);
    assertFalse(rs.next());

    conn.createStatement().execute(
      "CREATE INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1) include (v2, v3)");
    conn.createStatement().execute(
        "CREATE LOCAL INDEX " + LOCAL_INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (v1) include (v2, v3)");
    rs = conn.createStatement().executeQuery(dataTableQuery);
    assertFalse(rs.next());
    String indexTableQuery = "SELECT * FROM " + INDEX_TABLE_NAME;
    rs = conn.createStatement().executeQuery(indexTableQuery);
    assertFalse(rs.next());
    String localIndexTableQuery = "SELECT * FROM " + LOCAL_INDEX_TABLE_FULL_NAME;
    rs = conn.createStatement().executeQuery(localIndexTableQuery);
    assertFalse(rs.next());

    // load some data into the table
    stmt = conn.prepareStatement("UPSERT INTO " + DATA_TABLE_FULL_NAME + " VALUES(?,?,?,?)");
    stmt.setString(1, "a");
    stmt.setString(2, "x");
    stmt.setString(3, "1");
    stmt.setString(4, "j");
    stmt.execute();
    conn.commit();

    assertIndexExists(conn,true);
    conn.createStatement().execute("ALTER TABLE " + DATA_TABLE_FULL_NAME + " DROP COLUMN v2");
    assertIndexExists(conn,true);

    // verify data table rows
    rs = conn.createStatement().executeQuery(dataTableQuery);
    assertTrue(rs.next());
    assertEquals("a",rs.getString(1));
    assertEquals("x",rs.getString(2));
    assertEquals("j",rs.getString(3));
    assertFalse(rs.next());
    
    // verify index table rows
    rs = conn.createStatement().executeQuery(indexTableQuery);
    assertTrue(rs.next());
    assertEquals("x",rs.getString(1));
    assertEquals("a",rs.getString(2));
    assertEquals("j",rs.getString(3));
    assertFalse(rs.next());
    
    // verify local index table rows
    rs = conn.createStatement().executeQuery(localIndexTableQuery);
    assertTrue(rs.next());
    assertEquals("x",rs.getString(1));
    assertEquals("a",rs.getString(2));
    assertEquals("j",rs.getString(3));
    assertFalse(rs.next());

    // load some data into the table
    stmt = conn.prepareStatement("UPSERT INTO " + DATA_TABLE_FULL_NAME + " VALUES(?,?,?)");
    stmt.setString(1, "a");
    stmt.setString(2, "y");
    stmt.setString(3, "k");
    stmt.execute();
    conn.commit();

    // verify data table rows
    rs = conn.createStatement().executeQuery(dataTableQuery);
    assertTrue(rs.next());
    assertEquals("a",rs.getString(1));
    assertEquals("y",rs.getString(2));
    assertEquals("k",rs.getString(3));
    assertFalse(rs.next());
    
    // verify index table rows
    rs = conn.createStatement().executeQuery(indexTableQuery);
    assertTrue(rs.next());
    assertEquals("y",rs.getString(1));
    assertEquals("a",rs.getString(2));
    assertEquals("k",rs.getString(3));
    assertFalse(rs.next());
    
    // verify local index table rows
    rs = conn.createStatement().executeQuery(localIndexTableQuery);
    assertTrue(rs.next());
    assertEquals("y",rs.getString(1));
    assertEquals("a",rs.getString(2));
    assertEquals("k",rs.getString(3));
    assertFalse(rs.next());
}
 

public void testUserFunctions() throws SQLException
{
    // use valid user/pwd to set the full accessusers.
    Connection conn1 = openDefaultConnection(
        "dan", ("dan" + PASSWORD_SUFFIX));
    setDatabaseProperty(
        "gemfirexd.authz-full-access-users", 
        "francois,jeff,ames,jerry,jamie,dan,system", conn1);
    setDatabaseProperty(
        "gemfirexd.authz-default-connection-mode","NoAccess", conn1);
    conn1.commit();

    // we should still be connected as dan
    Statement stmt = conn1.createStatement();
    assertUpdateCount(stmt, 0, 
        "create table APP.t1(c1 varchar(30) check (UPPER(c1) <> 'JAMIE'))");
    assertUpdateCount(stmt, 1, "insert into APP.t1 values USER");
  
    conn1.commit();
    stmt.close();
    conn1.close();

    useUserValue(1, "jeff", "insert into APP.t1 values CURRENT_USER");
    useUserValue(1, "ames", "insert into APP.t1 values SESSION_USER");
    useUserValue(1, "jerry", "insert into APP.t1 values {fn user()}");
    assertUserValue(new String[] {"DAN","JEFF","AMES","JERRY"},
        "dan", "select * from APP.t1");
    // attempt some usage in where clause
    useUserValue(1,
        "dan", "update APP.t1 set c1 = 'edward' where c1 = USER");
    assertUserValue(new String[] {"JEFF"},"jeff",
        "select * from APP.t1 where c1 like CURRENT_USER");
    useUserValue(1, "ames", 
        "update APP.t1 set c1 = 'sema' where SESSION_USER = c1");
    useUserValue(1, "jerry", 
        "update APP.t1 set c1 = 'yrrej' where c1 like {fn user()}");
    assertUserValue(new String[] {"edward","JEFF","sema","yrrej"},
        "dan", "select * from APP.t1");
    useUserValue(4, "francois", "update APP.T1 set c1 = USER");
    assertUserValue(
        new String[] {"FRANCOIS","FRANCOIS","FRANCOIS","FRANCOIS"},
        "dan", "select * from APP.t1");

    // check that attempt to insert 'jamie' gives a check violation
    conn1 = openDefaultConnection("jamie", ("jamie" + PASSWORD_SUFFIX));
    stmt = conn1.createStatement();
    try {
        stmt.execute("insert into APP.t1 values CURRENT_USER");
    } catch (SQLException sqle) {
        assertSQLState("23513", sqle);
    }
    stmt.close();
    conn1.rollback();
    conn1.close();

    // Note: there is not much point in attempting to write with an invalid
    // user, that's already tested in the testConnectionShutdown fixture

    // reset
    conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX));
    setDatabaseProperty(
        "gemfirexd.authz-default-connection-mode","fullAccess", conn1);
    setDatabaseProperty(
        "gemfirexd.authentication.required","false", conn1);
    stmt = conn1.createStatement();
    assertUpdateCount(stmt, 0, "drop table APP.t1");
    conn1.commit();
    stmt.close();
    conn1.close();
}
 

public void testBatchInsert_FkOnPkViolation() throws Exception {
  Properties props = new Properties();
  System.clearProperty(GfxdConstants.GFXD_ENABLE_BULK_FK_CHECKS);
  props.setProperty(Attribute.ENABLE_BULK_FK_CHECKS, "true");
  startVMs(1, 3, 0, null, props);
  Connection conn = TestUtil.getConnection(props);
  Statement st = conn.createStatement();
  conn.setAutoCommit(false);
  conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

  // create tables
  st.execute("create table parent (col1 int, col2 int, col3 int not null, "
      + "constraint pk1 primary key (col1)) partition by list "
      + "(col3) (VALUES (1), VALUES (2), VALUES (3), values (4))");

  st.execute("create table child (col1 int, col2 int, col3 int not null, "
      + "constraint pk2 primary key (col1), constraint fk1 foreign key "
      + "(col2) references parent (col1)) partition by list "
      + "(col3) (VALUES (1), VALUES (2), VALUES (3), values (4))");

  st.execute("insert into parent values (1, 1, 1), (2, 2, 2), "
      + "(3, 3, 3), (4, 4, 4)");
  conn.commit();
  PreparedStatement pstmt = conn.prepareStatement("insert into child "
      + "values (?, ?, ?)");

  for (int i = 1; i <= 3; i++) {
    pstmt.setInt(1, i);
    pstmt.setInt(2, i);
    pstmt.setInt(3, i);
    pstmt.addBatch();
  }
  
  // this row to cause an FK violation 
  pstmt.setInt(1, 4);
  pstmt.setInt(2, 100); // FK violation
  pstmt.setInt(3, 4);
  pstmt.addBatch();
  // one more row with no error
  pstmt.setInt(1, 5);
  pstmt.setInt(2, 3);
  pstmt.setInt(3, 4);
  pstmt.addBatch();
  
  try {
    int[] ret = pstmt.executeBatch();
    fail("This statement should have failed due to FK violation");
  } catch (java.sql.BatchUpdateException be) {
    assertEquals("23503", be.getSQLState());
  }
  
  // no rows should be inserted
  ResultSet rs = st.executeQuery("select count(*) from child");
  assertTrue(rs.next());
  assertEquals(0, rs.getInt(1));
}
 

/**
 * Test grant statements before, during, and after a rolled-back
 * transaction
 */
public void testGrantRollbackAndCommit() throws SQLException {

	// NOTE: grantrevoke.java originally used S2.F2 for the function
	// below, but the signature on the function didn't match the
	// declaration, so was not used properly. Have substituted
	// function S1.F1 here to get the testcase to pass.
	
	// NOTE 2: executing the grant statements on the owner connection
	//         leads to a lock timeout when asserting any privilege?
	
	Connection oc = openUserConnection(users[0]);
	oc.setAutoCommit(false);
	
	// set up some privileges and check them
	grant(oc, "select", "s2", "t2", "public");
	oc.commit();

    assertSelectPrivilege(true, users[3], "S2", "T2", null);
    assertUpdatePrivilege(false, users[3], "S2", "T2", null);
    assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2"});
    assertDeletePrivilege(false, users[1], "S2", "T3");
    assertTriggerPrivilege(false, users[2], "S2", "T2");
    assertFunctionPrivilege(false, users[1], "S1", "F1", false);
	
	// alter some privileges, assert that they were granted.
    grant(oc, "select(c2),delete", "s2", "t3", users[1]);
    grant(oc, "trigger", "s2", "t2", "public");
	grant(oc, "execute", "function s1", "f1", users[1]);
    // the following asserts fail due to lock timeout
	//assertSelectPrivilege(true, users[1], "s2", "t3", new String[] {"C2"});
    //assertDeletePrivilege(true, users[1], "s2", "t3");
    //assertTriggerPrivilege(true, users[2], "S2", "T2");
    //assertFunctionPrivilege(true, users[1], "S1", "F1", false);
    
    // roll it back and assert the privileges were not granted.
    oc.rollback();
    assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2"});
    assertDeletePrivilege(false, users[1], "S2", "T3");
    assertTriggerPrivilege(false, users[2], "S2", "T2");
    assertFunctionPrivilege(false, users[1], "S1", "F1", false);
    
    // do it again... 
    grant(oc, "select(c2),delete", "s2", "t3", users[1]);
    grant(oc, "trigger", "s2", "t2", "public");
	grant(oc, "execute", "function s1", "f1", users[1]);
    // the following asserts fail due to lock timeout
	//assertSelectPrivilege(true, users[1], "S2", "T3", new String[] {"C2"});
    //assertDeletePrivilege(true, users[1], "S2", "T3");
    //assertTriggerPrivilege(true, users[2], "S2", "T2");
    //assertFunctionPrivilege(true, users[1], "S1", "F1", false);
    
    // commit and ensure the permissions are correct
    oc.commit();
    assertSelectPrivilege(true, users[1], "S2", "T3", new String[] {"C2"});
    assertDeletePrivilege(true, users[1], "S2", "T3");
    assertTriggerPrivilege( true, users[2], "S2", "T2");
    assertFunctionPrivilege( true, users[1], "S1", "F1", false);
   
	// remove any permissions we granted
	revoke(oc, "select", "s2", "t2", "public");
	revoke(oc, "select(c2),delete", "s2", "t3", users[1]);
    revoke(oc, "trigger", "s2", "t2", "public");
	revoke(oc, "execute", "function s1", "f1", users[1]);
	oc.commit();
	oc.setAutoCommit(false);
    assertSelectPrivilege(false, users[3], "S2", "T2", null);
    assertUpdatePrivilege(false, users[3], "S2", "T2", null);
    assertSelectPrivilege(false, users[1], "S2", "T3", new String[] {"C2"});
    assertDeletePrivilege(false, users[1], "S2", "T3");
    assertTriggerPrivilege(false, users[2], "S2", "T2");
    assertFunctionPrivilege(false, users[1], "S1", "F1", false);
	
	oc.close();
	
}
 

public void testMultipleInsertFromThinClient_bug44242() throws Exception {
  setupConnection();
  int port = startNetserverAndReturnPort("create schema emp");
  for (int i = 0; i < 2; i++) {
    Connection netConn1 = TestUtil.getNetConnection(port, null, null);
    
    Connection netConn2 = TestUtil.getNetConnection(port, null, null);

    Statement s = netConn1.createStatement();
    String ext = "";
    if (i == 1) {
      ext = "replicate";
    }
    s.execute("create table emp.EMPLOYEE_parent(lastname varchar(30) "
        + "primary key, depId int)" + ext +getSuffix());
    s.execute("create table emp.EMPLOYEE(lastname varchar(30) primary key, "
        + "depId int, foreign key(lastname) references "
        + "emp.EMPLOYEE_parent(lastname) on delete restrict)" + ext+getSuffix());
    s.execute("insert into emp.EMPLOYEE_parent values('Jones', 10), "
        + "('Rafferty', 50), ('Robinson', 100)");

    netConn2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    netConn2.setAutoCommit(false);
    Statement s2 = netConn2.createStatement();
    s2.execute("delete from emp.EMPLOYEE_parent");
    s2.execute("select * from emp.employee_parent");
    netConn1.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    netConn1.setAutoCommit(false);
    PreparedStatement pstmnt = netConn1
        .prepareStatement("INSERT INTO emp.employee VALUES (?, ?)");

    pstmnt.setString(1, "Jones");
    pstmnt.setInt(2, 33);
    pstmnt.addBatch();

    pstmnt.setString(1, "Rafferty");
    pstmnt.setInt(2, 31);
    pstmnt.addBatch();

    pstmnt.setString(1, "Robinson");
    pstmnt.setInt(2, 34);
    pstmnt.addBatch();

    try {
      pstmnt.executeBatch();
      netConn1.commit();
      fail("commit should have failed");
    } catch (SQLException e) {
      assertEquals("X0Z02", e.getSQLState());
    }
    netConn2.commit();

    s.execute("drop table emp.employee");
    this.waitTillAllClear();
    s.execute("drop table emp.employee_parent");
    this.waitTillAllClear();
  }
}