Exception: Invalid Column Name in getString() (but query works)
I'm accessing Oracle through JDBC. I've done lots of JDBC but this is
the first time iwth Oracle. I have a select statement that represents
a join. The query runs okay (and I've verified it in a tool outside
of Java). When I try to get a column value, however, I get a SQL
Exception: Invalid Column Name. Since I know the query works, that
makes o sense to me at all. Here's the code and info I've captured,
along with the stack trace.
try
{
conn =
DBConnectionFactory.getPooledConnection(DBConstants.DB_PS_STU);
stmt = conn.createStatement();
String countrySelect = "select distinct B.COUNTRY " +
"from sysadm.PS_EXT_ORG_TBL A,
sysadm.PS_ORG_LOC_ASOF B " +
"where A.EXT_ORG_TYPE = 'SCHL' AND A.EXT_ORG_ID =
B.EXT_ORG_ID " +
"AND B.COUNTRY NOT " +
inSqlClause +
"order by B.COUNTRY";
logger.debug("SQL statement for get Countries(): "+
countrySelect.toString());
rs = stmt.executeQuery(countrySelect);
ResultSetMetaData rms = rs.getMetaData();
logger.debug("Column Name: " +rms.getColumnName(1));
while(rs.next()==true)
{
countryList.add(rs.getString("sysadm.PS_ORG_LOC_ASOF.COUNTRY"));
}
I've tried this as getString("STATE"), getString("B.STATE"), and
"PS_LORG_LOC_ASOF.STATE"). None of these works. They all produce the
same exception.
2010-07-09 11:52:18,795 [DEBUG] - SQL statement for get Countries():
select distinct B.COUNTRY from sysadm.PS_EXT_ORG_TBL A,
sysadm.PS_ORG_LOC_ASOF B where A.EXT_ORG_TYPE = 'SCHL' AND
A.EXT_ORG_ID = B.EXT_ORG_ID AND B.COUNTRY NOT IN ('CAN','USA')order by
B.COUNTRY
2010-07-09 11:52:19,181 [DEBUG] - Column Name: COUNTRY
2010-07-09 11:52:19,199 [ERROR] - Failed to get countries in
SchoolListBuilder.getCountries
java.sql.SQLException: Invalid column name
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
125)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
162)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
227)
at
oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:
3099)
at
oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:
1854)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:
1559)
at
org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:
224)
at
edu.apu.schoollistmaintenance.SchoolListBuilder.getCountries(SchoolListBuilder.java:
390)
Any ideas on what I need to do? Thanks.