Re: Exception: Invalid Column Name in getString() (but query works)

From:

OccasionalFlyer <klitwak@apu.edu>

Newsgroups:

comp.lang.java.databases

Date:

Fri, 9 Jul 2010 12:24:39 -0700 (PDT)

Message-ID:

<ec092a5c-3b8c-4909-ae54-b07bc02c81a4@y32g2000prc.googlegroups.com>

Sorry, I mixed attempts at fixing the error. This message should have:
"I've tried this as getString("COUNTRY"), getString("B.COUNTRY"), and
"PS_LORG_LOC_ASOF.COUNTRY"). None of these works. They all produce
the
same exception."

On Jul 9, 12:20 pm, OccasionalFlyer <klit...@apu.edu> wrote:

I'm accessing Oracle through JDBC. I've done lots of JDBC but this is
the first time with 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 no 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.createState=

ment();

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' AN=
D 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.getMetaDa=

ta();

logger.debug("Column Name: " +rms.getC=

olumnName(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 th=

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.jav=

1854)
at oracle.jdbc.driver.OracleResultSet.getString(OracleRes=

ultSet.java:

1559)
at
org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet=

..java:

224)
at
edu.apu.schoollistmaintenance.SchoolListBuilder.getCountries(SchoolListBu=

ilder.java:

390)

Any ideas on what I need to do? Thanks.