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

private void refreshProperties(Connection serverDatabaseConnection, int newVersion) throws SQLException {
    String dropStatementString = "DROP TABLE IF EXISTS " + TABLE_PROPERTIES;
    Statement dropStatement = serverDatabaseConnection.createStatement();
    dropStatement.executeUpdate(dropStatementString);
    dropStatement.close();

    String createPropertiesStatementString = "CREATE TABLE IF NOT EXISTS " + TABLE_PROPERTIES
            + " (" + COLUMN_PROPERTIES_ROWID + " integer PRIMARY KEY,  "
            + COLUMN_PROPERTIES_VERSION + " text );";

    Statement createPropertiesStatement = serverDatabaseConnection.createStatement();
    createPropertiesStatement.execute(createPropertiesStatementString);
    createPropertiesStatement.close();

    String insertStatementString = "INSERT INTO " + TABLE_PROPERTIES+ "(" + COLUMN_PROPERTIES_VERSION + ") VALUES (?)";
    PreparedStatement insertStatement = serverDatabaseConnection.prepareStatement(insertStatementString);
    insertStatement.setInt(1, newVersion);

    insertStatement.executeUpdate();
    insertStatement.close();
}
 

public void DEBUGtestOuterJoinMultipleTables_ForDebuggingOuterJoin3() throws SQLException {
  Connection conn = getConnection();
  Statement s = conn.createStatement();
  s.execute("create table bdg(name varchar(30), bid int not null) replicate");
  s.execute("create table res(person varchar(30), bid int not null, rid int not null)");
  s.execute("create table dom(domain varchar(30), bid int not null, rid int not null)");
  
  s.execute("insert into bdg values('404', 1), ('405', 2)");
  s.execute("insert into res values('graham', 1, 101), ('lisa', 1, 102)");
  s.execute("insert into dom values('www.grahamellis.co.uk', 1, 101), ('www.sheepbingo.co.uk', 2, 102 )");
  
  s.execute("select * from " +
  		"bdg left outer join " +
  		"     res left outer join dom on res.rid = dom.rid " +
  		"on bdg.bid = res.bid");
  ResultSet rs = s.getResultSet();
  while(rs.next()) {
    System.out.println("rs.colname: " + rs.getMetaData() + " and value: "
        + rs.getObject(1) + ", " + rs.getObject(2) + ", " + rs.getObject(3)
        + ", " + rs.getObject(4) + ", " + rs.getObject(5) + ", "
        + rs.getObject(6) + ", " + rs.getObject(7) + ", " + rs.getObject(8));
  }
}
 

public void testIdentityColumnWithWAN() throws Exception{
  Connection conn = getConnection();
  
  Statement stmt = conn.createStatement();
  
  try{
    stmt.execute(
        "create table TESTTABLE (ID int unique not null GENERATED ALWAYS AS IDENTITY ,"
            + " phone int not null) gatewaysender (MYSENDER) ");  
  }catch(Exception e){
    if(!e.getMessage().contains("When GatewaySender is attached, only valid types for identity column")){
      fail("Unexpected exception", e);
    }
  }
  
}
 

@Override
public void createColocatedTableFunction(Connection conn, String tabname,
    int redundancy) throws SQLException {
  // We create a table...
  String tablename = schemaName + "." + tabname;
  Statement s = conn.createStatement();

  if (redundancy == 0) {
    s.execute("create table " + tablename
        + "(id int not null , cust_name varchar(200), vol int, "
        + "security_id varchar(10), num int, addr varchar(100)"
        + ", primary key (id))  partition by column (vol)"
        + " colocate with (" + mainTableName + ")"); // no redundancy
  }
  else {
    s.execute("create table " + tablename
        + "(id int not null , cust_name varchar(200), vol int, "
        + "security_id varchar(10), num int, addr varchar(100)"
        + ", primary key (id))  partition by column (vol)" 
        + " colocate with (" + mainTableName + ")"
        + " redundancy " + redundancy); // redundancy x?
  }
}
 

private void createTables_Derby() throws SQLException {
	Statement st = getConnection().createStatement();
	try {
		String usr = "CREATE TABLE users ("
				    +"id INT NOT NULL,"
				    +"firstname VARCHAR(64),"
				    +"lastname VARCHAR(64),"
				    +"city VARCHAR(64),"
				    +"state VARCHAR(2),"
				    +"CONSTRAINT p_users PRIMARY KEY (id)"
		   		    +")";
		st.execute(usr);
		String states = "CREATE TABLE states ("
		    +"state VARCHAR(2) NOT NULL,"
		    +"label VARCHAR(64),"
		    +"CONSTRAINT p_states PRIMARY KEY (state)"
   		    +")";
		st.execute(states);
	} finally {
		st.close();
	}
}
 

@Test
public void testMissingPKColumn() throws Exception {
    long ts = nextTimestamp();
    ensureTableCreated(getUrl(),PTSDB_NAME,null, ts-2);

    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts; // Insert at timestamp 0
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    conn.setAutoCommit(true);
    Statement stmt = conn.createStatement();
    try {
        stmt.execute("upsert into PTSDB(INST,HOST,VAL) VALUES ('abc', 'abc-def-ghi', 0.5)");
        fail();
    } catch (ConstraintViolationException e) {
        assertTrue(e.getMessage().contains("may not be null"));
    } finally {
        conn.close();
    }
}
 

/**
 * Test 'use default' statement. It should work as long as the user as privilege to assess any object in system
 * @throws Exception
 */
@Test
public void testDefaultDb() throws Exception {
  policyFile
      .addRolesToGroup(USERGROUP1, "select_tbl1")
      .addPermissionsToRole("select_tbl1", "server=server1->db=" + DB1 +"->table=tbl1->action=select")
      .setUserGroupMapping(StaticUserGroup.getStaticMapping())
      .write(context.getPolicyFile());

  // user_1 should be able to access default
  Connection connection = context.createConnection(USER1_1);
  Statement statement = context.createStatement(connection);
  statement.execute("USE default");
  statement.close();
  connection.close();

  // user_2 should NOT be able to access default since it does have access to any other object
  connection = context.createConnection(USER2_1);
  statement = context.createStatement(connection);
  context.assertAuthzException(statement, "USE default");
  statement.close();
  connection.close();

}
 

/**
 * Requests generated keys after doing a one-row insert into a table 
 * that has a generated column, but the insert is via a subquery with
 * a "where 1=2" clause.
 * Old harness Test 5B.
 * Expected result: ResultSet has one row with a NULL key.
 * @throws SQLException 
 */
public void testInsertSubqueryWhere1is2() throws SQLException
{
    // Setup
    Statement s = createStatement();
    s.execute("insert into t21_noAutoGen values(21, 'true')");
    s.close();

    String sql = 
        "insert into t11_AutoGen(c11) select c21 from t21_noAutoGen " +
        "where 1=2";
    runInsertFourWaysKeyIsNull (sql);
}
 

public void testMultiColumnForeignKeyViolation_1_Bug39987() throws Exception {
  GemFireXDQueryObserver old = null;
  Connection conn = getConnection();
  Statement s = conn.createStatement();
  s.execute("create table INSTRUMENTS (id1 int , sector_id1 int, subsector_id1 int , primary key(id1,sector_id1))" +
  		" PARTITION BY Primary key "+ getSuffix());
  s.execute("create table Child ( id2 int primary key, sector_id2 int, subsector_id2 int, " +
  		"foreign key (subsector_id2,sector_id2) references instruments (id1,sector_id1) ) partition by primary key "+ getSuffix());
  s.execute("insert into instruments values (1,1,1)");
  s.execute("insert into instruments values (2,2,1)");
  s.execute("insert into Child values (1,1,1)");
  String query = "update Child set sector_id2 = ? where id2 = ?";
  
  PreparedStatement ps = conn.prepareStatement(query);
  ps.setInt(1, 2);
  ps.setInt(2, 1);
  addExpectedException(FunctionException.class);
  try {
    ps.executeUpdate();
    fail("Update should have failed as it is violation of foreign key constraint");
  } catch (SQLException sqle) {
    assertEquals(sqle.toString(), "23503", sqle.getSQLState());
  } finally {
    removeExpectedException(FunctionException.class);
    if (old != null) {
      GemFireXDQueryObserverHolder.setInstance(old);
    }
    this.callbackInvoked = false;
  }
  
}
 

public void testChangeIsoLevelPreparedFunctionSQL() throws SQLException {
    Connection c = getConnection();
    c.setAutoCommit(true);
    Statement s = createStatement();
    s.execute("CREATE TABLE T1(SQLNAME VARCHAR(2))");
    PreparedStatement ps = prepareStatement("INSERT INTO T1 VALUES " +
            "GET_CYCLE_ISOLATION_SQL()");
    for (int i = 0; i < 4; ++i) {
        ps.executeUpdate();
        verifyCachedIsolation(c);
    }
    ps.close();
}
 

public void testInsertSubSelect() throws Exception {
  startVMs(1, 3);
  Connection conn = TestUtil.getConnection();
  Statement s = conn.createStatement();
  
  s.execute("create schema trade");
  
  s.execute("create table trade.account "
      + "(id int not null primary key, name varchar(20) not null, "
      + "addr varchar(20) not null, balance int not null) " +
                      "partition by column(name) "+ getSuffix());
  s.execute("insert into trade.account values (1, 'name1', 'addr1', 100), (2, 'name2', 'addr2', 200),"
      + "(3, 'name3', 'addr3', 300), (4, 'name4', 'addr4', 400) ");
  
  s.execute("create table trade.account2 "
      + "(id int not null primary key, name varchar(20) not null, "
      + "addr varchar(20) not null, balance int not null) " +
                      "partition by column(name) "+ getSuffix());
  
  s.execute("insert into trade.account2 select * from trade.account where name = 'name3'");
  s.execute("select * from trade.account2");
  ResultSet rs = s.getResultSet();
  assertTrue(rs.next());
  
  assertEquals(300, rs.getInt(4));
  assertEquals(3, rs.getInt(1));
  assertEquals("name3", rs.getString(2));
  assertEquals("addr3", rs.getString(3));
  assertEquals(300, rs.getInt(4));
  s.execute("drop table trade.account");
  s.execute("drop table trade.account2");
  s.execute("drop schema trade RESTRICT");
}
 

@Override
public void transactionMarker() throws Exception {
    Statement statement = connection.createStatement();
    try {
        statement.execute("select * from employee");
        ResultSet rs = statement.getResultSet();
        while (rs.next()) {
            rs.getString(1);
        }
    } finally {
        statement.close();
    }
}
 

private static void executeStatement(String sql, IDataSource dataSource, int queryTimeout) throws Exception {
	logger.debug("IN");
	Connection connection = null;
	String dialect = dataSource.getHibDialectClass();
	try {
		connection = dataSource.getConnection();
		if (!dialect.contains("VoltDB")) {
			connection.setAutoCommit(false);
		}
		Statement stmt = connection.createStatement();
		if (queryTimeout > 0) {
			stmt.setQueryTimeout(queryTimeout);
		}

		logger.debug("Executing sql " + sql);
		stmt.execute(sql);
		if (!dialect.contains("VoltDB")) {
			connection.commit();
		}
		logger.debug("Sql " + sql + " executed successfully");
	} catch (Exception e) {
		if (connection != null && !dialect.contains("VoltDB")) {
			connection.rollback();
		}
		throw e;
	} finally {
		if (connection != null && !connection.isClosed()) {
			connection.close();
		}
		logger.debug("OUT");
	}
}
 

/**
   * tx operations with RC and Without Eviction
   * 1. Do tx-insert
   * 2. check the row it should be availalble
   * 4. delete the row in tx( with table scan)
   * 5. check the row
   * 6. commit
   * 7. Nothing should be in the table.
   */

  public void testRCTransactionalInsertDeleteWithoutEvictionCriteria() throws Exception {
    setupConnection();
    
    Connection conn = jdbcConn;
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    conn.setAutoCommit(false);
    Statement st = conn.createStatement();
    
   st.execute("create hdfsstore myhdfs namenode 'localhost' homedir './myhdfs' queuepersistent true");
    
    st.execute("create schema tran");
    st.execute("Create table tran.t1 (c1 int not null , c2 int not null, "
        + "primary key(c1))" + " persistent hdfsstore (myhdfs)");
    conn.commit();
    ResultSet rs;
//    ResultSet rs = st.executeQuery("Select * from tran.t1");
//    assertFalse("ResultSet should be empty ", rs.next());
//    rs.close();
    //insert
    st.execute("insert into tran.t1 values (10, 10)");
    
    rs = st.executeQuery("Select * from tran.t1 -- GEMFIREXD-PROPERTIES queryHDFS=true \n ");
    int numRows = 0;
    while (rs.next()) {
      numRows++;
    }
    assertEquals("ResultSet should contain one row ", 1, numRows);
    rs.close();
    
    //update
    st.executeUpdate("update tran.t1 set c2=20 where c2=10");
    
    rs = st.executeQuery("Select * from tran.t1 -- GEMFIREXD-PROPERTIES queryHDFS=true \n");
    assertTrue(rs.next());
    assertEquals(20,rs.getInt("c2"));
    rs.close();
    //delete
    st.execute("delete from tran.t1 where c2 =20");
    conn.commit();

    rs = st.executeQuery("Select * from tran.t1");
    assertFalse("ResultSet should be empty ", rs.next());
    rs.close();
    
    // Close connection, resultset etc...
    st.execute("drop table tran.t1");
    st.execute("drop hdfsstore myhdfs");
    st.close();
    conn.commit();
    conn.close();
  }
 

/**
 * Test that named constraints and unnamed constraints are recorded in the system tables properly.
 * 
 * @throws SQLException
 */
public void testPrimaryAndUniqueKeysInSystemCatalogs() throws SQLException {
	Statement s = createStatement();
	String getNamedConstraintsQuery = "select c.constraintname, c.type from sys.sysconstraints c, sys.systables t "
           + "where c.tableid = t.tableid and not t.tablename like 'UNNAMED%' order by c.constraintname";
	
	s.execute("create table primkey1 (c1 int not null constraint prim1 primary key)");
	String [][] expected = new String[][] {{"PRIM1", "P"}};
	ResultSet rs = s.executeQuery(getNamedConstraintsQuery);
	JDBC.assertFullResultSet(rs, expected, true);
	rs.close();
	
	s.execute("create table unnamed_primkey2 (c1 int not null primary key)");
	rs = s.executeQuery("select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and t.tablename = 'UNNAMED_PRIMKEY2' order by c.constraintname");
	assertTrue(rs.next());
	assertEquals("P", rs.getString(2));
	assertFalse(rs.next());
	rs.close();
	rs = s.executeQuery(getNamedConstraintsQuery);
	JDBC.assertFullResultSet(rs, expected);
	rs.close();
	
	s.execute("create table primkey3 (c1 int not null, c2 int not null, constraint prim3 primary key(c2, c1))");
	expected = new String[][] {{"PRIM1", "P"}, {"PRIM3", "P"}};
	rs = s.executeQuery(getNamedConstraintsQuery);
	JDBC.assertFullResultSet(rs, expected);
	rs.close();

	s.execute("create table uniquekey1 (c1 int not null constraint uniq1 unique)");
	expected = new String[][] {{"PRIM1", "P"}, {"PRIM3", "P"}, {"UNIQ1", "U"}};
	rs = s.executeQuery(getNamedConstraintsQuery);
	JDBC.assertFullResultSet(rs, expected);
	rs.close();

	s.execute("create table unnamed_uniquekey2 (c1 int not null unique)");
	rs = s.executeQuery("select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and t.tablename = 'UNNAMED_UNIQUEKEY2' order by c.constraintname");
	assertTrue(rs.next());
	assertEquals("U", rs.getString(2));
	assertFalse(rs.next());
	rs.close();
    	rs = s.executeQuery(getNamedConstraintsQuery);
	JDBC.assertFullResultSet(rs, expected);
	rs.close();
	
	s.execute("create table uniquekey3 (c1 int not null, c2 int not null, constraint uniq3 unique(c2, c1))");
	expected = new String[][] {{"PRIM1", "P"}, {"PRIM3", "P"}, {"UNIQ1", "U"}, {"UNIQ3", "U"}};
	rs = s.executeQuery(getNamedConstraintsQuery);
	JDBC.assertFullResultSet(rs, expected);
	rs.close();

    s.execute("drop table primkey1");
    s.execute("drop table unnamed_primkey2");
    s.execute("drop table primkey3");
    s.execute("drop table uniquekey1");
    s.execute("drop table unnamed_uniquekey2");
    s.execute("drop table uniquekey3");
    
    s.close();
}
 

public void testAllDataTypesDiskWrite() throws SQLException {
    EmbedConnection conn = (EmbedConnection)TestUtil.getConnection();

    Statement st = conn.createStatement();
    try {
      st.execute("drop table test");
    } catch (Exception i) {
    }

    try {
      st
          .execute("create table test (id int primary key, "
              + "lv bigint, si smallint, fl float, dbl double, "
              + "nmr numeric(10,2), dml decimal(30,20), "
              + "dt date, tm time, ts timestamp, cr char(254), vcr varchar(8192), id2 int, id3 int )"); // rl
      // real

      PreparedStatement ps = conn
          .prepareStatement("insert into test values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");

      String vstr = "ঙ চ ছ জ ঝ ঞ ট This is varchar max upto 8k bytes ... so I suppose this should test long " +
      		" enought string for now .... with some more bytes added for randomization ";
      for (int row = 1; row < 1000; row++) {
        ps.setInt(1, row);
        ps.setLong(2, row);
        ps.setShort(3, (short)row);
        ps.setFloat(4, row * 1.3f);
        ps.setDouble(5, row + 1.2 / 0.9);
        ps.setBigDecimal(6, BigDecimal.valueOf(row / 1.34d));
        ps.setBigDecimal(7, BigDecimal.valueOf(row / 1.43d * row));
        final int d = ((row % 20) + 1);
        ps.setDate(8, java.sql.Date
            .valueOf("2011-04-" + (d < 10 ? "0" + d : d)));
        ps.setTime(9, java.sql.Time.valueOf("12:00:01"));
        ps.setTimestamp(10, java.sql.Timestamp
            .valueOf("2011-02-28 11:59:59.99999"));
        ps.setString(11, "This is char fixed width upto 254 ...  ঋ এ ঐ ও ");
        int begin = AvailablePort.rand.nextInt(vstr.length()-1);
        ps
            .setString(
                12, vstr.substring(begin, vstr.length()));
        ps.setInt(13, row+1);
        ps.setInt(14, row+2);
        
        ps.executeUpdate();
      }

      GemFireXDQueryObserverHolder
          .setInstance(new GemFireXDQueryObserverAdapter() {
            private static final long serialVersionUID = 1L;

            public int overrideSortBufferSize(ColumnOrdering[] columnOrdering,
                int sortBufferMax) {
              return 100;
            }

            public boolean avoidMergeRuns() {
              return false;
            }
          });

//      ResultSet rs = st
//          .executeQuery("select cr, tm, vcr, dbl, lv, fl, dml, ts, nmr, dt, id from test order by ts, vcr, dt");
//      while (rs.next()) {
//        System.out.println(rs.getInt("id"));
//      }

      ResultSet rs = st
          .executeQuery("select id, id2, id3, dml, ts from test order by id2 desc, dml");
      while (rs.next()) {
        System.out.println(rs.getInt("id"));
//        TestUtil.getLogger().fine(Integer.toString(rs.getInt("id"));
      }
      
    } finally {
      GemFireXDQueryObserverHolder
          .setInstance(new GemFireXDQueryObserverAdapter());
      TestUtil.shutDown();
    }
  }
 

public void testStatistics() throws SQLException {
    Statement stmt = createStatement();

String [][] expectedValues = { {"1", "hhhh"},
                   {"2", "uuuu"},
             {"3", "yyyy"},
             {"4", "aaaa"},
                   {"5", "jjjj"},
             {"6", "rrrr"},
                   {"7", "iiii"},
                   {"8", "wwww"},
                   {"9", "rrrr"},
              {"10", "cccc"},
                   {"11", "hhhh"},
             {"12", "rrrr"} };
stmt.execute("call SYSCS_UTIL.SET_RUNTIMESTATISTICS(1)");
JDBC.assertFullResultSet(stmt.executeQuery("select i, b from t3 FOR UPDATE"), expectedValues);
RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(stmt);
assertTrue(rtsp.usedTableScan());
assertFalse(rtsp.usedDistinctScan());
commit();

PreparedStatement p = prepareStatement("select i, b from t3  where i = ? FOR UPDATE");
            p.setString(1, "7");
            p.executeQuery();
String [][] expectedValues1 = { {"7", "iiii" } };
JDBC.assertFullResultSet(p.getResultSet(), expectedValues1);
RuntimeStatisticsParser rtsp2 = SQLUtilities.getRuntimeStatisticsParser(stmt);
assertFalse(rtsp2.usedTableScan());
assertFalse(rtsp2.usedDistinctScan());
p.close();
commit();


p = prepareStatement("select i, b from t3 where i < ? FOR UPDATE");
            p.setString(1, "7");
            p.executeQuery();
String[][] expectedValues2 =  { {"1", "hhhh" },
        {"2", "uuuu" },
        {"3", "yyyy" },
        {"4", "aaaa" },
        {"5", "jjjj" },
        {"6", "rrrr" } };
JDBC.assertFullResultSet(p.getResultSet(), expectedValues2);
RuntimeStatisticsParser rtsp3 = SQLUtilities.getRuntimeStatisticsParser(stmt);
assertFalse(rtsp3.usedTableScan());
assertFalse(rtsp3.usedDistinctScan());
p.close();
commit();


p = prepareStatement("select i, b from t3  where b = ? FOR UPDATE");
            p.setString(1, "cccc");
            p.executeQuery();
String[][] expectedValues3 = { {"10", "cccc" } };
JDBC.assertFullResultSet(p.getResultSet(), expectedValues3);
RuntimeStatisticsParser rtsp4 = SQLUtilities.getRuntimeStatisticsParser(stmt);
assertFalse(rtsp4.usedTableScan());
assertFalse(rtsp4.usedDistinctScan());
p.close();
commit();

      stmt.execute("call SYSCS_UTIL.SET_RUNTIMESTATISTICS(0)");
        stmt.close();
    }
 

@Test
public void testPostQuery() throws Exception {
    // remove previous test database, if any
    final File dbLocation = new File(DB_LOCATION);
    dbLocation.delete();

    // load test data to database
    final Connection con = ((DBCPService) runner.getControllerService("dbcp")).getConnection();
    Statement stmt = con.createStatement();

    try {
        stmt.execute("drop table TEST_NULL_INT");
    } catch (final SQLException sqle) {
    }

    stmt.execute("create table TEST_NULL_INT (id integer not null, val1 integer, val2 integer, constraint my_pk primary key (id))");
    stmt.execute("insert into TEST_NULL_INT values(1,2,3)");

    runner.setIncomingConnection(true);
    runner.setProperty(ExecuteSQL.SQL_PRE_QUERY, "CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)");
    runner.setProperty(ExecuteSQL.SQL_SELECT_QUERY, "select * from TEST_NULL_INT");
    runner.setProperty(ExecuteSQL.SQL_POST_QUERY, "CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0)");
    runner.enqueue("test".getBytes());
    runner.run();

    runner.assertAllFlowFilesTransferred(ExecuteSQL.REL_SUCCESS, 1);
    MockFlowFile firstFlowFile = runner.getFlowFilesForRelationship(ExecuteSQL.REL_SUCCESS).get(0);
    firstFlowFile.assertAttributeEquals(ExecuteSQL.RESULT_ROW_COUNT, "1");

    final InputStream in = new ByteArrayInputStream(firstFlowFile.toByteArray());
    final DatumReader<GenericRecord> datumReader = new GenericDatumReader<>();
    try (DataFileStream<GenericRecord> dataFileReader = new DataFileStream<>(in, datumReader)) {
        GenericRecord record = null;
        long recordsFromStream = 0;
        while (dataFileReader.hasNext()) {
            // Reuse record object by passing it to next(). This saves us from
            // allocating and garbage collecting many objects for files with
            // many items.
            record = dataFileReader.next(record);
            recordsFromStream += 1;
        }

        assertEquals(1, recordsFromStream);
    }
}
 

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));
}
 

private void alterTableSetEvictionStartTime(Connection gConn, Map<String, String>  hdfsExtnMap){
  //reset eviction counters
  HDFSSqlBB.getBB().getSharedCounters().zero(HDFSSqlBB.evictionCount);
 
  // update start time so that eviction starts for tables having EVICTION FREQUENCY 
  for (String table : tables){
    String frequency  = hdfsExtnMap.get(table.toUpperCase() + HDFSSqlTest.FREQUENCY);
    if (frequency != null){
      // read cache time as per #48780
      long timeMill = ((GemFireCacheImpl)Misc.getGemFireCache()).cacheTimeMillis();

      if (random.nextInt(100) < 10){
        timeMill -= 10 * 60 * 1000;
        Log.getLogWriter().info("Setting starttime to 10 mins earlier than current time");
      } else if (random.nextInt(100) < 10){
        timeMill += 30 * 1000;
        Log.getLogWriter().info("Setting starttime to 30 sec later than current time");
      }
      
      // Should use the GMT time as per #49562
      // Syntax:  START { D 'date-constant' | T 'time-constant' | TS 'timestamp-constant' } 
      // date-constant : yyyy-mm-dd
      // timestamp-constant: yyyy-mm-dd hh:mm:ss
      // time-constant : hh:mm:ss

      int w = random.nextInt(3);
      String format = (w==0) ? "yyyy-MM-dd HH:mm:ss" : ((w==1) ? "HH:mm:ss" : "yyyy-MM-dd" );
      String lit    = (w==0) ? "TS"                  : ((w==1) ? "T"        : "D" );
      
      SimpleDateFormat gmtFormat = new SimpleDateFormat(format);
      gmtFormat.setTimeZone(TimeZone.getTimeZone("GMT+00"));        
      String timeStr = lit + " '" + gmtFormat.format(new Date(timeMill)) + "'";    
      int freq = random.nextInt(10) + 1; 
      String sql = "ALTER TABLE " + table + " SET EVICTION FREQUENCY " + freq + " SECONDS START " + timeStr ;
      
      Log.getLogWriter().info("Time converted from '" + new Date(timeMill)  + "' to " + timeStr);
      
      try {
        Log.getLogWriter().info("executing alter table to set eviction start time " + sql );
        Statement statement = gConn.createStatement();
        statement.execute(sql);
        statement.close();          
        Log.getLogWriter().info("executed alter table to set eviction start time : " + sql);
      }catch (SQLException se) {
        SQLHelper.handleSQLException(se);
      }
    }
  }
}