Exception: Invalid Column Name in getString() (but query works)
OccasionalFlyer
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.
OccasionalFlyer
"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,
Arne Vajhøj
String countrySelect = "select distinct B.COUNTRY as BCOUNTRY" +
and rs.getString("BCOUNTRY")!
Arne
PS: Or get the columns by index instead of by name.
joe.we...@gmail.com
Get the ResultSetMetaData object and find out what it
thinks the column names are. That will clear the
mystery, but as Aren said, the best way usually is to
just get the data by column number.
OccasionalFlyer
wrote:
Thanks. I used ResultSetMetaData and got the response that the column
name is "COUNTRY." However, that doesn't work, causing the same
"INVALID COLUMN NAME."
Using column numbers is problematic in my view in case the table
underneath gets modified and it is not made known generally.
joe.we...@gmail.com
did you call getColumnName() or getColumnLabel() etc?
Martin Gregorie
Its not so bad in this case because the numbers refer to the result
columns as defined in your query. Of course if you'd written
"SELECT * FROM..." then you should be taken out and shot because, as you
say, then you'd be at the mercy of the DBA adding, removing or
rearranging the columns of either table.
Without knowing where the inSqlClause string comes from and how its
content is created I have more concerns about the way the SQL statement
is concatenated round it and then passed to executeQuery() rather than
using a PreparedStatement. Of course, it may be perfectly safe because
there is no user-supplied content in that string; however, if there is
any, then its potentially open to an SQL injection attack.
--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |