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

/**
 * Do some basic verification on a connection obtained from the data source.
 *
 * @param cDs data source to get connection from
 * @throws SQLException if a JDBC operation fails
 */
private void verifyConnection(ClientConnectionPoolDataSource cDs)
        throws SQLException {
    J2EEDataSource.setBeanProperty(cDs, "createDatabase", "create");
    PooledConnection pc = cDs.getPooledConnection();
    // Get a connection and make sure we can access the database.
    Connection con = pc.getConnection();
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("select * from sys.systables");
    JDBC.assertDrainResultsHasData(rs);
    PreparedStatement ps1 = con.prepareStatement("values 31");
    JDBC.assertSingleValueResultSet(ps1.executeQuery(), "31");
    ps1.close();
    PreparedStatement ps2 = con.prepareStatement("values 31");
    // The physical statement is supposed to be the same, but not the
    // logical prepared statements (if pooling is used).
    assertNotSame(ps1, ps2);
    JDBC.assertSingleValueResultSet(ps2.executeQuery(), "31");
    // Close everything
    stmt.close();
    ps2.close();
    con.close();
    pc.close();
}
 

/**
 * Since inventory is not using FK - need to clean items
 */
@Override
public boolean deletePlayerItems(final int playerId) {
	Connection con = null;
	try {
		con = DatabaseFactory.getConnection();
		PreparedStatement stmt = con.prepareStatement(DELETE_CLEAN_QUERY);
		stmt.setInt(1, playerId);
		stmt.execute();
		stmt.close();
	}
	catch (Exception e) {
		log.error("Error Player all items. PlayerObjId: " + playerId, e);
		return false;
	}
	finally {
		DatabaseFactory.close(con);
	}
	return true;
}
 

private ResultSet getNonUniqQuery0(Connection conn, int whichQuery,  
    boolean[] success) {
  PreparedStatement stmt;
  ResultSet rs = null;
  success[0] = true;
  try {
    Log.getLogWriter().info("which query is -- " + nonUniqSelect[whichQuery]);
    stmt = conn.prepareStatement(nonUniqSelect[whichQuery]);      
    Log.getLogWriter().info("no bound data used in query");
    rs = stmt.executeQuery();
  } catch (SQLException se) {
  	SQLHelper.printSQLException(se);
    if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
    else if (!SQLHelper.checkGFXDException(conn, se)) success[0] = false; //hand X0Z01 and #41471
    else SQLHelper.handleSQLException(se);
  }
  return rs;
}
 

private ResultSet getUniqQuery3(Connection conn, int whichQuery,  
    int cid, BigDecimal price, int tid, boolean[] success) {
  PreparedStatement stmt;
  ResultSet rs = null;
  success[0] = true;
  try {
    Log.getLogWriter().info("which query is -- " + uniqSelect[whichQuery]);
    stmt = conn.prepareStatement(uniqSelect[whichQuery]);      
    Log.getLogWriter().info("data used in query price " + price + " and tid: "+ tid
    		+ " and cid: " + cid);
    stmt.setInt(1, cid);
    stmt.setBigDecimal(3, price);
    stmt.setInt(2, tid);
    stmt.setInt(4, tid);
    rs = stmt.executeQuery();
  } catch (SQLException se) {
  	SQLHelper.printSQLException(se);
    if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
    else if (!SQLHelper.checkGFXDException(conn, se)) success[0] = false; //hand X0Z01 and #41471
    else if (se.getSQLState().equalsIgnoreCase("0A000") && whichQuery == 3) success[0] = true;
    else SQLHelper.handleSQLException(se);
  }
  return rs;
}
 

public static void insertSingleKeyTable(String tableName, int pk1) throws SQLException {
  Connection conn = getDefaultConnection();
 
  PreparedStatement ps = conn.prepareStatement(insertsql);

  ps.setString(1, tableName);
  ps.setInt(2, pk1);
  ps.setInt(3, -1);
  ps.setInt(4, 1);
  ps.setInt(5, 0);
  ps.setInt(6, 0);
  Log.getLogWriter().info("insert into trade.monitor values("
      + tableName + ", " + pk1 + ", -1, 1, 0, 0 )");
  
  try {
    ps.execute();
  } catch (SQLException se) {
    if (se.getSQLState().equals("X0Z02")) {
      throw new TestException("Got unexpected conflict exception in trigger" 
          + TestHelper.getStackTrace(se));
    } else throw se;
  }

  closeConnection(conn);
}
 

@Test
public void testInListSkipScan() throws Exception {
    String query = "SELECT entity_id, b_string FROM aTable WHERE organization_id=? and entity_id IN (?,?)";
    Properties props = new Properties(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setString(2, ROW2);
        statement.setString(3, ROW4);
        ResultSet rs = statement.executeQuery();
        Set<String> expectedvals = new HashSet<String>();
        expectedvals.add(ROW2+"_"+C_VALUE);
        expectedvals.add(ROW4+"_"+B_VALUE);
        Set<String> vals = new HashSet<String>();
        assertTrue (rs.next());
        vals.add(rs.getString(1) + "_" + rs.getString(2));
        assertTrue (rs.next());
        vals.add(rs.getString(1) + "_" + rs.getString(2));
        assertFalse(rs.next());
        assertEquals(expectedvals, vals);
    } finally {
        conn.close();
    }
}
 

@Override
public int getGroupCount(int tenantId) throws GroupManagementDAOException {
    PreparedStatement stmt = null;
    ResultSet resultSet = null;
    try {
        Connection conn = GroupManagementDAOFactory.getConnection();
        String sql = "SELECT COUNT(ID) AS GROUP_COUNT FROM DM_GROUP WHERE TENANT_ID = ?";
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, tenantId);
        resultSet = stmt.executeQuery();
        if (resultSet.next()) {
            return resultSet.getInt("GROUP_COUNT");
        } else {
            return 0;
        }
    } catch (SQLException e) {
        throw new GroupManagementDAOException("Error occurred while getting group count'", e);
    } finally {
        GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
    }
}
 

@Override
public boolean sequenceExists(final String sequenceName) {
    ReturningWork<Boolean> work = new ReturningWork<Boolean>() {
        @Override
        public Boolean execute(Connection connection) throws SQLException {
            DialectResolver dialectResolver = new StandardDialectResolver();
            Dialect dialect = dialectResolver.resolveDialect(getResolutionInfo(connection));
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                preparedStatement = connection.prepareStatement(dialect.getQuerySequencesString());
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    if (sequenceName.equals(resultSet.getString(1))) {
                        return true;
                    }
                }
            } catch (SQLException e) {
                throw e;
            } finally {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
            }
            return false;

        }
    };

    Session session = (Session) entityManager.getDelegate();
    SessionFactory sessionFactory = session.getSessionFactory();
    return sessionFactory.getCurrentSession().doReturningWork(work);
}
 

@Test
public void testInListParameterMetaData2() throws Exception {
    String query = "SELECT a_string, b_string FROM atable WHERE ? IN (2.2, 3)";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(1, pmd.getParameterCount());
    assertEquals(BigDecimal.class.getName(), pmd.getParameterClassName(1));
}
 

/**
 * 查询UIC使用情况
 * 
 * @param uic
 * @return
 * @throws SQLException
 */
public UicBean queryUIC(String uic) throws SQLException {
	long startTime = System.currentTimeMillis();
	String sql = "SELECT uic,site_user_id,status,create_time,use_time FROM " + UIC_TABLE + " WHERE uic=?;";

	UicBean bean = null;
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try {
		conn = DatabaseConnection.getSlaveConnection();

		pst = conn.prepareStatement(sql);
		pst.setString(1, uic);

		rs = pst.executeQuery();
		if (rs.next()) {
			bean = new UicBean();
			bean.setUic(rs.getString(1));
			bean.setSiteUserId(rs.getString(2));
			bean.setStatus(rs.getInt(3));
			bean.setCreateTime(rs.getLong(4));
			bean.setUseTime(rs.getLong(5));
		}
	} catch (Exception e) {
		throw e;
	} finally {
		DatabaseConnection.returnConnection(conn, pst, rs);
	}

	LogUtils.dbDebugLog(logger, startTime, bean, sql, uic);
	return bean;
}
 

/**
 * @param identityProviderName
 * @param connectorType
 * @param provisioningEntity
 * @param tenantId
 * @throws IdentityApplicationManagementException
 */
public void deleteProvisioningEntity(String identityProviderName, String connectorType,
                                     ProvisioningEntity provisioningEntity, int tenantId)
        throws IdentityApplicationManagementException {

    Connection dbConnection = IdentityDatabaseUtil.getDBConnection();
    try {

        PreparedStatement prepStmt = null;

        // id of the identity provider
        int idpId = getIdentityProviderIdentifier(dbConnection, identityProviderName, tenantId);

        // id of the provisioning configuration
        int provisioningConfigId = getProvisioningConfigurationIdentifier(dbConnection, idpId,
                connectorType);

        // PROVISIONING_CONFIG_ID, ENTITY_TYPE,
        // ENTITY_LOCAL_USERSTORE, ENTITY_NAME, TENANT_ID
        String sqlStmt = IdentityProvisioningConstants.SQLQueries.DELETE_PROVISIONING_ENTITY_SQL;

        prepStmt = dbConnection.prepareStatement(sqlStmt);
        prepStmt.setInt(1, provisioningConfigId);
        prepStmt.setString(2, provisioningEntity.getEntityType().toString());
        prepStmt.setString(3, IdentityUtil.extractDomainFromName(provisioningEntity.getEntityName()));
        prepStmt.setString(4, UserCoreUtil.removeDomainFromName(provisioningEntity.getEntityName()));
        prepStmt.setInt(5, tenantId);

        prepStmt.execute();
        dbConnection.commit();
    } catch (SQLException e) {
        IdentityApplicationManagementUtil.rollBack(dbConnection);
        String msg = "Error occurred while deleting Provisioning entity for tenant " + tenantId;
        throw new IdentityApplicationManagementException(msg, e);
    } finally {
        IdentityApplicationManagementUtil.closeConnection(dbConnection);
    }
}
 

protected void insertToGFXDTable(Connection conn, int[] eid, String[] emp_name,
    int[] deptid, Date[] since, String[] addr, Blob[] picture, String[] ssn,
    int size, boolean isPut) throws SQLException {
  PreparedStatement stmt = conn.prepareStatement(isPut ? put : insert);
  int tid = getMyTid();
  
  for (int i=0 ; i<size ; i++) {
    try {
      insertToTable(stmt, eid[i], emp_name[i], deptid[i], since[i], addr[i], 
          picture[i], ssn[i], tid, isPut); 
    } catch (SQLException se) {
      SQLHelper.handleSQLException(se);      
    }
  }
}
 

protected void insertToGfxdTable(Connection conn, int[] cid, String[] cust_name,
    Date[] since, String[] addr, long[] generatedCid, int[] count, int size) throws SQLException {
  PreparedStatement stmt = null;
  if (addGenIdCol) stmt =  conn.prepareStatement(insertGenId, Statement.RETURN_GENERATED_KEYS);
  else stmt =  conn.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
  int tid = getMyTid();

  if (size != 1) throw new TestException("Should only insert 1 row in the test");
  for (int i=0 ; i<size ; i++) {
    count[i] = insertToTable(stmt, cid[i], cust_name[i],since[i], addr[i], generatedCid, tid); 
    Log.getLogWriter().info("gemfirexd -  inserts " + count[i] + " rows");
  }
}
 

private void assertExecuteForPreparedStatementWithResultSetTypeAndResultSetConcurrencyAndResultSetHoldability(final Connection connection)
        throws SQLException, ParseException, JAXBException, IOException {
    try (PreparedStatement preparedStatement = connection.prepareStatement(getSql(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT)) {
        for (SQLValue each : assertion.getSQLValues()) {
            preparedStatement.setObject(each.getIndex(), each.getValue());
        }
        assertTrue("Not a DQL statement.", preparedStatement.execute());
        try (ResultSet resultSet = preparedStatement.getResultSet()) {
            assertResultSet(resultSet);
        }
    }
}
 

@Override
public List<Device> getDevicesByName(PaginationRequest request, int tenantId)
        throws DeviceManagementDAOException {
    Connection conn;
    PreparedStatement stmt = null;
    List<Device> devices = new ArrayList<>();
    try {
        conn = this.getConnection();
        String sql = "SELECT d1.ID AS DEVICE_ID, d1.DESCRIPTION, d1.NAME AS DEVICE_NAME, d1.DEVICE_TYPE, " +
                     "d1.DEVICE_IDENTIFICATION, e.OWNER, e.OWNERSHIP, e.STATUS, e.DATE_OF_LAST_UPDATE, " +
                     "e.DATE_OF_ENROLMENT, e.ID AS ENROLMENT_ID FROM DM_ENROLMENT e, (SELECT d.ID, d.NAME, " +
                     "d.DESCRIPTION, t.NAME AS DEVICE_TYPE, d.DEVICE_IDENTIFICATION FROM DM_DEVICE d, " +
                     "DM_DEVICE_TYPE t WHERE d.DEVICE_TYPE_ID = t.ID AND d.NAME LIKE ? AND d.TENANT_ID = ?) d1 " +
                     "WHERE DEVICE_ID = e.DEVICE_ID AND TENANT_ID = ? ORDER BY ENROLMENT_ID " +
                     "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, request.getDeviceName() + "%");
        stmt.setInt(2, tenantId);
        stmt.setInt(3, tenantId);
        stmt.setInt(4, request.getStartIndex());
        stmt.setInt(5, request.getRowCount());
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Device device = DeviceManagementDAOUtil.loadDevice(rs);
            devices.add(device);
        }
    } catch (SQLException e) {
        throw new DeviceManagementDAOException("Error occurred while fetching the list of devices that matches " +
                                               "'" + request.getDeviceName() + "'", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, null);
    }
    return devices;
}
 

@Test
public void should_bootstrap_schema() throws SQLException {
    Connection connection = pool.getConnection();

    PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM PIZZAS");
    ResultSet resultSet = statement.executeQuery();
    resultSet.first();
    int count = resultSet.getInt(1);
    assertThat(count).isEqualTo(0);
}
 

/**
 * add a record in snapshots table for a given timestamp and retrieve the snapid 
 * @param ts
 * @return
 */
public int getNextSnapshotId(long ts)
{
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String insertSQL = "insert into SNAPSHOTS (START_TS) values(?)";
String retrieveSQL = "select SNAP_ID from SNAPSHOTS where START_TS=?";
try
{
  conn = this.createConnection(true);
  stmt = conn.prepareStatement(insertSQL);
  stmt.setLong(1, ts);
  stmt.execute();
  stmt.close();
  stmt = conn.prepareStatement(retrieveSQL);
  stmt.setLong(1, ts);
  rs = stmt.executeQuery();
  if(rs!=null && rs.next())
  {
	  return rs.getInt(1);
  }
}catch(Exception ex)
{
}finally
{
  DBUtils.close(rs);
  DBUtils.close(stmt);
  DBUtils.close(conn);
}
 return -1;//error
}
 

public static void main(String[] args) throws TddlException, SQLException {

        TDataSource ds = new TDataSource();

        // init a datasource with local config file
        ds.setAppName("tddl5_sample");
        ds.setRuleFile("classpath:sample_rule.xml");
        ds.setTopologyFile("sample_topology.xml");
        ds.setSchemaFile("demo_repo_schema.xml");
        ds.init();

        System.out.println("init done");

        Connection conn = ds.getConnection();

        // insert a record
        conn.prepareStatement("replace into _tddl_ (id,name) values (1,'sun1')").executeUpdate();
        conn.prepareStatement("replace into _tddl_ (id,name) values (2,'sun1')").executeUpdate();

        conn.prepareStatement("replace into _tddl_ (id,name) values (3,'sun1')").executeUpdate();

        conn.prepareStatement("replace into _tddl_ (id,name) values (4,'sun2')").executeUpdate();

        conn.prepareStatement("replace into _tddl_ (id,name) values (5,'sun2')").executeUpdate();

        System.out.println("insert done");

        // select all records
        PreparedStatement ps = conn.prepareStatement("SELECT id from _tddl_ order by id");
        // PreparedStatement ps =
        // conn.prepareStatement("SELECT * from _tddl_ t1 join _tddl_ t2 where t1.name=t2.name and t2.name='sun1' and t1.id=1");

        // PreparedStatement ps =
        // conn.prepareStatement("SELECT * from _tddl_ t1 where name='sun'");

        // PreparedStatement ps =
        // conn.prepareStatement("SELECT count(*)+1 from _tddl_ t1 where t1.id=1");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            StringBuilder sb = new StringBuilder();
            int count = rs.getMetaData().getColumnCount();
            for (int i = 1; i <= count; i++) {

                String key = rs.getMetaData().getColumnLabel(i);
                Object val = rs.getObject(i);
                sb.append("[" + rs.getMetaData().getTableName(i) + "." + key + "->" + val + "]");
            }
            System.out.println(sb.toString());
        }

        rs.close();
        ps.close();
        conn.close();

        System.out.println("query done");
    }
 

private void runTransactionalBehaviourOfCacheLoader_Bug42914(
    final boolean isReplicated) throws Exception {
  // Start one client and some servers
  startVMs(1, 3);

  // Create a schema
  clientSQLExecute(1, "create schema EMP");

  // Controller VM
  final String suffix = isReplicated ? "replicate" : "redundancy 1";
  String createTable = "create table EMP.TESTTABLE (ID int primary key, "
      + "DESCRIPTION varchar(1024) not null, ADDRESS varchar(1024), ID1 int) "
      + suffix;
  clientSQLExecute(1, createTable);
  GfxdCallbacksTest.addLoader("EMP", "TESTTABLE",
      "com.pivotal.gemfirexd.dbsync.DBSynchronizerTestBase$GfxdTestRowLoader",
      "");
  // Test insert propagation by inserting in a data store node of DS.DS0
  Connection conn = TestUtil.getConnection();
  conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  conn.setAutoCommit(false);
  String query = "select * from emp.testtable where ID = ?";
  PreparedStatement ps = conn.prepareStatement(query);
  Statement stmt = conn.createStatement();
  ResultSet rs;

  for (int i = 0; i < 20; i++) {
    ps.setInt(1, i);
    rs = ps.executeQuery();
    rs.next();
    assertEquals(rs.getInt(1), i);
    assertFalse(rs.next());
  }
  sqlExecuteVerify(null, new int[] { 1, 2, 3 },
      "select count(*) from emp.testtable", null, "0");
  // rollback and check no data
  conn.rollback();
  rs = stmt.executeQuery("select * from emp.testtable");
  assertFalse(rs.next());
  sqlExecuteVerify(null, new int[] { 1, 2, 3 },
      "select count(*) from emp.testtable", null, "0");

  // now populate again and check successful loads
  for (int i = 0; i < 20; i++) {
    ps.setInt(1, i);
    rs = ps.executeQuery();
    rs.next();
    assertEquals(rs.getInt(1), i);
    assertFalse(rs.next());
  }
  sqlExecuteVerify(null, new int[] { 1, 2, 3 },
      "select count(*) from emp.testtable", null, "0");
  rs = stmt.executeQuery("select count(*) from emp.testtable");
  assertTrue(rs.next());
  assertEquals(20, rs.getInt(1));
  assertFalse(rs.next());
  conn.commit();
  conn.close();

  // now check commit of loaded data
  conn = TestUtil.getConnection();
  stmt = conn.createStatement();
  ps = conn.prepareStatement(query);

  rs = stmt.executeQuery("select id from emp.testtable");
  TreeSet<Integer> ids = new TreeSet<Integer>();
  for (int i = 0; i < 20; i++) {
    assertTrue("failed next for i=" + i, rs.next());
    ids.add(Integer.valueOf(rs.getInt(1)));
  }
  assertFalse(rs.next());
  assertEquals(20, ids.size());
  assertEquals(0, ids.first().intValue());
  assertEquals(19, ids.last().intValue());

  for (int i = 0; i < 20; i++) {
    ps.setInt(1, i);
    rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals(i, rs.getInt(1));
    assertFalse(rs.next());
  }
  rs = stmt.executeQuery("select count(*) from emp.testtable");
  assertTrue(rs.next());
  assertEquals(20, rs.getInt(1));
  assertFalse(rs.next());
  sqlExecuteVerify(null, new int[] { 1, 2, 3 },
      "select count(*) from emp.testtable", null, "20");
}
 

protected void helpTestUpdate(boolean localIndex) throws Exception {
    String dataTableName = MUTABLE_INDEX_DATA_TABLE;
    String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        conn.setAutoCommit(false);
        populateDataTable(conn, dataTableName);

        // create an expression index
        String ddl = "CREATE "
                + (localIndex ? "LOCAL" : "")
                + " INDEX IDX ON "
                + fullDataTableName
                + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2)),"
                + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )"
                + " INCLUDE (long_col1, long_col2)";
        PreparedStatement stmt = conn.prepareStatement(ddl);
        stmt.execute();

        // update index pk column and covered column
        String upsert = "UPSERT INTO "
                + fullDataTableName
                + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, varchar_col1, long_col1) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

        stmt = conn.prepareStatement(upsert);
        stmt.setString(1, "varchar1");
        stmt.setString(2, "char1");
        stmt.setInt(3, 1);
        stmt.setLong(4, 1l);
        stmt.setBigDecimal(5, new BigDecimal(0.5));
        stmt.setDate(6, DateUtil.parseDate("2015-01-01 00:00:00"));
        stmt.setString(7, "a.varchar_updated");
        stmt.setLong(8, 101);
        stmt.executeUpdate();
        conn.commit();

        // verify only one row was updated in the data table
        String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), long_col1 from "
                + fullDataTableName;
        ResultSet rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " + selectSql);
        assertTrue(rs.next());
        assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1   ", rs.getString(1));
        assertEquals(101, rs.getLong(2));
        assertTrue(rs.next());
        assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2   ", rs.getString(1));
        assertEquals(2, rs.getLong(2));
        assertFalse(rs.next());

        // verify that the rows in the index table are also updated
        rs = conn.createStatement().executeQuery("SELECT " + selectSql);
        assertTrue(rs.next());
        assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1   ", rs.getString(1));
        assertEquals(101, rs.getLong(2));
        assertTrue(rs.next());
        assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2   ", rs.getString(1));
        assertEquals(2, rs.getLong(2));
        assertFalse(rs.next());
        conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName);
    } finally {
        conn.close();
    }
}