Java Code Examples for java.sql.PreparedStatement#executeQuery()

protected int getSidByList(Connection conn, int tid) {
	String sql = "select sec_id from trade.securities where tid = ?";
	try {
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, tid);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			return rs.getInt(1);
		}
		rs.close();
	} catch (SQLException se) {
		if (!SQLHelper.checkGFXDException(conn, se)) return getSid(); //return random sid
		SQLHelper.handleSQLException(se);
	}
	return getSid(); //random
}
 

private Object executePreparedStatement(PreparedStatement pstmt)
    throws SQLException {
  try {
    logger.info("Executing query " + pstmt.toString());
    ResultSet result = pstmt.executeQuery();
    // even if this result set is empty (i.e. no row found), just return
    // the empty result set
    logger.info("Query succeeded");
    recyclePooledStatement(pstmt);
    return result;
  } catch (SQLException e) {
    // throw away the pooled statement, just in case it was the problem
    releasePooledStatement(pstmt);
    logGetRowError(e);
    throw new SQLException("Error executing query from archive database", e
        .getSQLState(), VENDOR_CODE_ARCHIVE_ERROR, e);
  }
}
 

MgmtEntry.Requestor createRequestor(String name) throws CaMgmtException {
  final String sql = sqlSelectRequestor;
  PreparedStatement stmt = null;
  ResultSet rs = null;

  try {
    stmt = prepareStatement(sql);
    stmt.setString(1, name);
    rs = stmt.executeQuery();

    if (!rs.next()) {
      throw new CaMgmtException("unknown Requestor " + name);
    }

    return new MgmtEntry.Requestor(new NameId(rs.getInt("ID"), name),
        rs.getString("TYPE"), rs.getString("CONF"));
  } catch (SQLException ex) {
    throw new CaMgmtException(datasource.translate(sql, ex));
  } finally {
    datasource.releaseResources(stmt, rs);
  }
}
 

@Test
public void testDivideByIntegerZero() throws Exception {
    long ts = nextTimestamp();
    String query = "SELECT a_integer/0 FROM atable";
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Connection conn = DriverManager.getConnection(url);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.executeQuery();
        fail();
    } catch (SQLException e) { // expected
        assertTrue(e.getMessage().contains("Divide by zero"));
    } finally {
        conn.close();
    }
}
 

/**
*@author Warley Rodrigues
*Criação e inserção de Pais, Estado, Cidade, Usuario e Postagem no banco de dados
*/ 
@Override
public Cidade selecionar(int id) throws Exception {
    Connection conexao = getConexao();
    
    PreparedStatement pstmt;
    pstmt = conexao.prepareStatement("select * from cidades where id = ?");
    
    pstmt.setInt(1, id);
    
    ResultSet rs = pstmt.executeQuery();
    
    if(rs.next()){
        Cidade p = new Cidade();
        EstadoDAO estadoDAO = new EstadoDAO();
        
        p.setId(rs.getInt("id"));
        p.setEstado(estadoDAO.selecionar(rs.getInt("estado")));
        p.setNome(rs.getString("nome"));
        
        return p;
    } else {
        return null;
    }
}
 

public Set<String> getActiveTokensForConsumerKey(String consumerKey) throws IdentityOAuth2Exception {
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;
    Set<String> accessTokens = new HashSet<>();
    try {
        String sqlQuery = SQLQueries.GET_ACCESS_TOKENS_FOR_CONSUMER_KEY;
        ps = connection.prepareStatement(sqlQuery);
        ps.setString(1, consumerKey);
        ps.setString(2, OAuthConstants.TokenStates.TOKEN_STATE_ACTIVE);
        rs = ps.executeQuery();
        while (rs.next()) {
            accessTokens.add(rs.getString(1));
        }
        connection.commit();
    } catch (SQLException e) {
        IdentityDatabaseUtil.rollBack(connection);
        throw new IdentityOAuth2Exception("Error occurred while getting access tokens from acces token table for " +
                "the application with consumer key : " + consumerKey, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, ps);
    }
    return accessTokens;
}
 

@Test
public void testMixedTypeInListStatement() throws Exception {
    String query = "SELECT entity_id FROM " + tableName + " WHERE organization_id=? AND x_long IN (5, ?)";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        long l = Integer.MAX_VALUE + 1L;
        statement.setLong(2, l);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(ROW7, rs.getString(1));
        assertTrue(rs.next());
        assertEquals(ROW9, rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Override
public void loadSubscription(Node node, String subId)
{
    Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	Map<Node.UniqueIdentifier, Node> nodes = new HashMap<>();
	nodes.put(node.getUniqueIdentifier(), node);

	try
	{
		con = DbConnectionManager.getConnection();

		// Get subscriptions to all nodes
		pstmt = con.prepareStatement(LOAD_NODE_SUBSCRIPTION);
		pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
		pstmt.setString(2, node.getNodeID());
		pstmt.setString(3, subId);
		rs = pstmt.executeQuery();

		// Add to each node the corresponding subscription
		if (rs.next())
		{
			loadSubscriptions(nodes, rs);
		}
	}
	catch (SQLException sqle)
	{
           log.error("An exception occurred while loading a subscription ({}) for a node ({}) from the database.", subId, node.getUniqueIdentifier(), sqle);
	}
	finally
	{
		DbConnectionManager.closeConnection(rs, pstmt, con);
	}
}
 

/**
 * Test that when a nested routine drops a role/schema, the
 * current value is correctly reset. For roles, the current role
 * is unchanged, since it is lazily checked (and potentially reset
 * to NONE if it no longer exists or it is no longer granted to
 * session user) only when it is attempted used for anything. For
 * schema, the current schema should revert back to the session's
 * default schema. This holds for all frames on the session
 * context stack (see also caller's check).
 */
public static void dropper() throws SQLException
{
    Connection conn1 = null;

    try {
        conn1 = DriverManager.getConnection("jdbc:default:connection");

        // Drop current contexts
        Statement stm = conn1.createStatement();
        stm.executeUpdate("drop role outermost");
        stm.executeUpdate("drop schema outermost restrict");
        stm.close();

        String[] expected = new String[]{null, "TEST_DBO"};

        // check that we revert correctly
        for (int i= 0; i < variableKeywords.length; i++) {
            String curr = currentPrefix[i] + variableKeywords[i];

            PreparedStatement ps =
                conn1.prepareStatement("values " + curr);

            ResultSet rs = ps.executeQuery();
            assertCurrent(variableKeywords[i], rs, expected[i]);
            rs.close();
            ps.close();
        }
    } finally {
        if (conn1 != null) {
            try {
                conn1.close();
            } catch (Exception e) {
            }
        }
    }
}
 

@Override
public Principal authenticateLocal(Session session, String name, String password,
                                   byte[] salt) {
    User user = null;
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = openConnection();
        stmt = conn.prepareStatement(GET_USER_SQL);
        stmt.setString(1, name);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            String md5 = rs.getString(5);
            if ((salt == null) ?
                md5Password(name, password).equals(md5) :
                password.equals(salted(md5, salt))) {
                user = getUser(rs);
            }
        }
        rs.close();
        conn.commit();
    }
    catch (SQLException ex) {
        throw new SecurityException("Error adding role", ex);
    }
    finally {
        cleanup(conn, stmt);
    }
    if (user == null) {
        throw new AuthenticationFailedException("invalid username or password");
    }
    if (session != null) {
        session.put(SESSION_PRINCIPAL_KEY, user);
        session.put(SESSION_ROLES_KEY, user.getRoles());
    }
    if (monitor.getUserMonitor(user.getName()) == null) {
        monitor.registerUserMonitor(new UserMonitorImpl(user.getName()));
    }
    return user;
}
 

public String serverTestMultipleSetDatabase()
        throws SQLException
{
    Connection con = this.getServerSideConnection();
    String sql = "select DESCRIPTION from APP.ORDERS where ORDER_ID = ?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, 1);
    ResultSet rs = ps.executeQuery();
    rs.next();
    String result = rs.getString(1);
    rs.close();
    ps.close();
    con.close();
    return result;
}
 

public ArrayList<Announcement> getAnnouncements() {
	ArrayList<Announcement> announcements = new ArrayList<>();
	try (final Connection connection = slaveInfo.getSource().getConnection()) {
		String announcementTableName = String.format("%sannouncements", slaveInfo.getSettings().getPrefix());

		PreparedStatement stmt = connection.prepareStatement("SELECT * FROM " + announcementTableName);
		ResultSet res = stmt.executeQuery();

		while (res.next()) {
			if (res.getString("ANNOUNCEMENT_ID") != null) {
				Announcement announcement = new Announcement(UUID.fromString(res.getString("ANNOUNCEMENT_ID")), Snowflake.of(res.getString("GUILD_ID")));
				announcement.setSubscriberRoleIdsFromString(res.getString("SUBSCRIBERS_ROLE"));
				announcement.setSubscriberUserIdsFromString(res.getString("SUBSCRIBERS_USER"));
				announcement.setAnnouncementChannelId(res.getString("CHANNEL_ID"));
				announcement.setAnnouncementType(AnnouncementType.valueOf(res.getString("ANNOUNCEMENT_TYPE")));
				announcement.setEventId(res.getString("EVENT_ID"));
				announcement.setEventColor(EventColor.fromNameOrHexOrID(res.getString("EVENT_COLOR")));
				announcement.setHoursBefore(res.getInt("HOURS_BEFORE"));
				announcement.setMinutesBefore(res.getInt("MINUTES_BEFORE"));
				announcement.setInfo(res.getString("INFO"));
				announcement.setEnabled(res.getBoolean("ENABLED"));
				announcement.setInfoOnly(res.getBoolean("INFO_ONLY"));

				announcements.add(announcement);
			}
		}

		stmt.close();
	} catch (SQLException e) {
		Logger.getLogger().exception(null, "Failed to get all announcements.", e, true, this.getClass());
	}

	return announcements;
}
 

/**
 * Retrieve all the Workflows for a tenant
 *
 * @param tenantId Tenant ID
 * @return List<Workflow>
 * @throws InternalWorkflowException
 */
public List<Workflow> listWorkflows(int tenantId) throws InternalWorkflowException {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    List<Workflow> workflowList = new ArrayList<>();
    String query = SQLConstants.LIST_WORKFLOWS_QUERY;
    try {
        prepStmt = connection.prepareStatement(query);
        prepStmt.setInt(1, tenantId);
        rs = prepStmt.executeQuery();
        while (rs.next()) {
            String id = rs.getString(SQLConstants.ID_COLUMN);
            String name = rs.getString(SQLConstants.WF_NAME_COLUMN);
            String description = rs.getString(SQLConstants.DESCRIPTION_COLUMN);
            String templateId = rs.getString(SQLConstants.TEMPLATE_ID_COLUMN);
            String templateImplId = rs.getString(SQLConstants.TEMPLATE_IMPL_ID_COLUMN);
            Workflow workflowDTO = new Workflow();
            workflowDTO.setWorkflowId(id);
            workflowDTO.setWorkflowName(name);
            workflowDTO.setWorkflowDescription(description);
            workflowDTO.setTemplateId(templateId);
            workflowDTO.setWorkflowImplId(templateImplId);
            workflowList.add(workflowDTO);
        }
    } catch (SQLException e) {
        throw new InternalWorkflowException(errorMessage, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
    return workflowList;
}
 

public String getServiceProviderNameByClientId(String clientId, String clientType,
                                               String tenantDomain) throws IdentityApplicationManagementException {
    int tenantID = -123;

    if (tenantDomain != null) {
        try {
            tenantID = ApplicationManagementServiceComponentHolder.getInstance().getRealmService()
                    .getTenantManager().getTenantId(tenantDomain);
        } catch (UserStoreException e1) {
            throw new IdentityApplicationManagementException("Error while reading application");
        }
    }

    String applicationName = null;

    // Reading application name from the database
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement storeAppPrepStmt = null;
    ResultSet appNameResult = null;
    try {
        storeAppPrepStmt = connection
                .prepareStatement(ApplicationMgtDBQueries.LOAD_APPLICATION_NAME_BY_CLIENT_ID_AND_TYPE);
        storeAppPrepStmt.setString(1, clientId);
        storeAppPrepStmt.setString(2, clientType);
        storeAppPrepStmt.setInt(3, tenantID);
        storeAppPrepStmt.setInt(4, tenantID);
        appNameResult = storeAppPrepStmt.executeQuery();
        if (appNameResult.next()) {
            applicationName = appNameResult.getString(1);
        }
        connection.commit();
    } catch (SQLException e) {
        throw new IdentityApplicationManagementException("Error while reading application", e);
    } finally {
        IdentityApplicationManagementUtil.closeResultSet(appNameResult);
        IdentityApplicationManagementUtil.closeStatement(storeAppPrepStmt);
        IdentityApplicationManagementUtil.closeConnection(connection);
    }

    return applicationName;
}
 

public String getItemSQL(String id ) {

        Connection c = null;

        // Define a list in which all work items are stored
        String query = "";
        String status="" ;
        String description="";

        try {
            // Create a Connection object
            c = ConnectionHelper.getConnection();

            ResultSet rs = null;
            Statement s = c.createStatement();
            Statement scount = c.createStatement();

            // Use prepared statements
            PreparedStatement pstmt = null;
            PreparedStatement ps = null;

            //Specify the SQL Statement to query data
            query = "Select description, status FROM work where idwork ='" +id + "' ";
            pstmt = c.prepareStatement(query);
            rs = pstmt.executeQuery();

            while (rs.next()) {

                description = rs.getString(1);
                status = rs.getString(2);
            }
            return convertToString(toXmlItem(id,description,status));


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionHelper.close(c);
        }
        return null;
    }
 

private void fixBasicZoneNicCount(Connection conn) {
    try {
        PreparedStatement pstmt = conn.prepareStatement("SELECT id from data_center where networktype='Basic'");
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            Long zoneId = rs.getLong(1);
            Long networkId = null;
            Long vmCount = 0L;
            s_logger.debug("Updating basic zone id=" + zoneId + " with correct nic count");

            pstmt = conn.prepareStatement("SELECT id from networks where data_center_id=? AND guest_type='Direct'");
            pstmt.setLong(1, zoneId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                networkId = rs.getLong(1);
            } else {
                continue;
            }

            pstmt = conn.prepareStatement("SELECT count(*) from vm_instance where name like 'i-%' and (state='Running' or state='Starting' or state='Stopping')");
            rs = pstmt.executeQuery();

            if (rs.next()) {
                vmCount = rs.getLong(1);
            }

            pstmt = conn.prepareStatement("UPDATE op_networks set nics_count=? where id=?");
            pstmt.setLong(1, vmCount);
            pstmt.setLong(2, networkId);
            pstmt.executeUpdate();

        }

        s_logger.debug("Basic zones are updated with correct nic counts successfully");
        rs.close();
        pstmt.close();
    } catch (SQLException e) {
        throw new CloudRuntimeException("Unable to drop 'path' index for 'domain' table due to:", e);
    }
}
 

@Test
public void testSelfJoin() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
    String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i2.\"item_id\", i1.name FROM " + tableName1 + " i1 JOIN " 
        + tableName1 + " i2 ON i1.\"item_id\" = i2.\"item_id\" ORDER BY i1.\"item_id\"";
    String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i1.name, i2.name FROM " + tableName1 + " i1 JOIN " 
        + tableName1 + " i2 ON i1.\"item_id\" = i2.\"supplier_id\" ORDER BY i1.name, i2.name";
    try {
        PreparedStatement statement = conn.prepareStatement(query1);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000001");
        assertEquals(rs.getString(2), "T1");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000002");
        assertEquals(rs.getString(2), "T2");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000003");
        assertEquals(rs.getString(2), "T3");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000004");
        assertEquals(rs.getString(2), "T4");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000005");
        assertEquals(rs.getString(2), "T5");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000006");
        assertEquals(rs.getString(2), "T6");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "invalid001");
        assertEquals(rs.getString(2), "INVALID-1");
        
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("EXPLAIN " + query1);
        assertPlansEqual(plans[2], QueryUtil.getExplainPlan(rs));

        statement = conn.prepareStatement(query2);
        rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "T1");
        assertEquals(rs.getString(2), "T1");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "T1");
        assertEquals(rs.getString(2), "T2");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "T2");
        assertEquals(rs.getString(2), "T3");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "T2");
        assertEquals(rs.getString(2), "T4");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "T5");
        assertEquals(rs.getString(2), "T5");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "T6");
        assertEquals(rs.getString(2), "T6");
        
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testStarJoin() throws Exception {
    String[] query = new String[5];
    query[0] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN " 
        + JOIN_CUSTOMER_TABLE_FULL_NAME + " c ON o.\"customer_id\" = c.\"customer_id\" JOIN " 
        + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\"";
    query[1] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o, " 
            + JOIN_CUSTOMER_TABLE_FULL_NAME + " c, " 
            + JOIN_ITEM_TABLE_FULL_NAME + " i WHERE o.\"item_id\" = i.\"item_id\" AND o.\"customer_id\" = c.\"customer_id\" ORDER BY \"order_id\"";
    query[2] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN " 
            + JOIN_CUSTOMER_TABLE_FULL_NAME + " c ON o.\"customer_id\" = c.\"customer_id\" JOIN " 
            + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\"";
    query[3] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM (" + JOIN_ORDER_TABLE_FULL_NAME + " o, " 
            + JOIN_CUSTOMER_TABLE_FULL_NAME + " c), " 
            + JOIN_ITEM_TABLE_FULL_NAME + " i WHERE o.\"item_id\" = i.\"item_id\" AND o.\"customer_id\" = c.\"customer_id\" ORDER BY \"order_id\"";
    query[4] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o, (" 
            + JOIN_CUSTOMER_TABLE_FULL_NAME + " c, " 
            + JOIN_ITEM_TABLE_FULL_NAME + " i) WHERE o.\"item_id\" = i.\"item_id\" AND o.\"customer_id\" = c.\"customer_id\" ORDER BY \"order_id\"";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        for (int i = 0; i < query.length; i++) {
            PreparedStatement statement = conn.prepareStatement(query[i]);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), "000000000000001");
            assertEquals(rs.getString("\"order_id\""), "000000000000001");
            assertEquals(rs.getString(2), "C4");
            assertEquals(rs.getString("C.name"), "C4");
            assertEquals(rs.getString(3), "T1");
            assertEquals(rs.getString("iName"), "T1");
            assertEquals(rs.getInt(4), 1000);
            assertEquals(rs.getInt("Quantity"), 1000);
            assertNotNull(rs.getDate(5));
            assertTrue (rs.next());
            assertEquals(rs.getString(1), "000000000000002");
            assertEquals(rs.getString(2), "C3");
            assertEquals(rs.getString(3), "T6");
            assertEquals(rs.getInt(4), 2000);
            assertNotNull(rs.getDate(5));
            assertTrue (rs.next());
            assertEquals(rs.getString(1), "000000000000003");
            assertEquals(rs.getString(2), "C2");
            assertEquals(rs.getString(3), "T2");
            assertEquals(rs.getInt(4), 3000);
            assertNotNull(rs.getDate(5));
            assertTrue (rs.next());
            assertEquals(rs.getString(1), "000000000000004");
            assertEquals(rs.getString(2), "C4");
            assertEquals(rs.getString(3), "T6");
            assertEquals(rs.getInt(4), 4000);
            assertNotNull(rs.getDate(5));
            assertTrue (rs.next());
            assertEquals(rs.getString(1), "000000000000005");
            assertEquals(rs.getString(2), "C5");
            assertEquals(rs.getString(3), "T3");
            assertEquals(rs.getInt(4), 5000);
            assertNotNull(rs.getDate(5));

            assertFalse(rs.next());
        }
    } finally {
        conn.close();
    }
}
 

public static ResultSet getNonUniqQuery(Connection conn, int whichQuery, int cid,
    BigDecimal price1, BigDecimal price2, int tid, boolean[] success) {
  PreparedStatement stmt;
  ResultSet rs = null;
  success[0] = true;
  try {
    
    Boolean hasHdfs = TestConfig.tab().booleanAt(SQLPrms.hasHDFS, false);      
    String database = SQLHelper.isDerbyConn(conn)?"Derby - " :"gemfirexd - ";        
    String query = (! SQLHelper.isDerbyConn(conn) && hasHdfs ) ? " QUERY: " +  nonUniqSelectHdfs[whichQuery] : " QUERY: " +  nonUniqSelect[whichQuery];        
    stmt = (! SQLHelper.isDerbyConn(conn) && hasHdfs )  ? conn.prepareStatement(nonUniqSelectHdfs[whichQuery]) :  conn.prepareStatement(nonUniqSelect[whichQuery]) ; 
       
    
    switch (whichQuery){
    case 0:
      //"select * from trade.securities s, trade.portfolio f where sec_id = f.sid and f.tid = ?",
      Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with no data" + query);
      break;

    case 1:
      //"select cid, sid, symbol, price, qty from trade.securities s, trade.portfolio f where sec_id = f.sid and cid >? and f.tid = ? ",
      Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with CID:" + cid + query);
      stmt.setInt(1, cid);
      break;

    case 2:
      //"select cid, sid, symbol, exchange, qty, price from trade.securities s, trade.portfolio f where sec_id = f.sid and cid<? and(qty=availQty or price > ?) and f.tid = ?",
      Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with CID:" + cid + ",PRICE:"+ price1 + query );
      stmt.setInt(1, cid); //set cid<=?
      stmt.setBigDecimal(2, price1);
      break;

    case 3:
      //"select cid, sid, symbol, exchange, price, subtotal from trade.securities s, trade.portfolio f where sec_id = f.sid and cid >? and (subtotal >10000 or (price >= ? and price <= ?) and f.tid = ?"
      Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with CID:" + cid + ",1_PRICE:"+ price1 + ",2_PRICE:" + price2 + query);
      stmt.setInt(1, cid); //set cid>?
      stmt.setBigDecimal(2, price1);
      stmt.setBigDecimal(3, price2);
      break;
      
    case 4:
      //Left outer join
      Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with LEFT OUTER JOIN " + query);
      break;

    default:
      throw new TestException("incorrect select statement, should not happen");
    }
    rs = stmt.executeQuery();
  } catch (SQLException se) {
    if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
    else if (se.getSQLState().equals("0A000") && se.getMessage().matches(".*disk.*")) {
      SQLHelper.printSQLException(se);
      Log.getLogWriter().info("got the unsupported exception, need to remove this once bug#40348 is fixed, continuing test");
      useDisk[0] = true;
      return null;
    }
    else      SQLHelper.handleSQLException(se);
  }
  return rs;
}
 

public static void main(String[] args) throws Exception {
    TDataSource ds = new TDataSource();

    ds.setAppName("ICBU_DA_PBSVR_DEV_APP");

    ds.setDynamicRule(true);

    Map cp = new HashMap();
    cp.put(ConnectionProperties.ALLOW_TEMPORARY_TABLE, "true");
    cp.put(ConnectionProperties.CHOOSE_TEMPORARY_TABLE, "true");

    ds.setConnectionProperties(cp);
    ds.init();

    System.out.println("init done");
    Connection conn = ds.getConnection();
    // insert a record
    // conn.prepareStatement("replace into sample_table (id,name,address) values (1,'sun','hz')").executeUpdate();
    System.out.println("insert done");
    // select all records
    PreparedStatement ps = conn.prepareStatement("replace into tddl_category (id,name,gmt_modified,gmt_create) values (1,'aa',now(),now())");
    // ps.executeUpdate();

    ps = conn.prepareStatement("select ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.STAT_DATE,ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.SUM_PROD_SHOW_NUM,ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.SUM_PROD_CLICK_NUM,ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.SUM_PROD_FB_NUM,ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.SUM_PROD_VISITOR_CNT from adl_dm_mdm_mem_prod_effect_sdt0 ADL_DM_MDM_MEM_PROD_EFFECT_SDT0 order by ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.STAT_DATE asc  limit 0,100");
    // ps.setDate(1, new Date(System.currentTimeMillis()));
    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");
}