• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Invalid Column Name in getString()--query worked

 
Ken Duncan
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to debug a SQLException: Invalid Column Name
Here is the code:

The value of stateQuery is in the output below. It runs successfully so far as I can tell. I tried it outside of Java and that was fine. The problem is the getString(). I first used getString("B.NAME") but I got this error. Then I tried the fully qualified name getString("sysadm.PS_ORG_LOC_ASOF.STATE").

The query runs successfully (and I have verified it outside of Java using Aqua Data Studio). The error occurs when I try to do a ResultSet.getString().

Here is the stack trace:
SQL Error code for SchoolListBuilder.getCountries: 17006
SQL error status for SchoolListBuilder.getCountries: null
SQL query in SchoolListBuilder.getStatesForCountry(): select distinct B.STATE 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 = 'CAN' AND B.STATE <> ' ' order by B.STATE
failed to find states in SchoolListBuilder.getStatesForCountry() because Invalid column name
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.getStatesForCountry(SchoolListBuilder.java:241)
at edu.apu.schoollistmaintenance.SchoolListBuilder.buildStateBasedRecords(SchoolListBuilder.java:166)
at edu.apu.schoollistmaintenance.SchoolListBuilder.build(SchoolListBuilder.java:81)
at edu.apu.schoollistmaintenance.SchoolListBuilder.main(SchoolListBuilder.java:402)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)
Exception in thread "main" java.lang.NullPointerException
at edu.apu.schoollistmaintenance.SchoolListBuilder.closeSchoolXMLFile(SchoolListBuilder.java:320)
at edu.apu.schoollistmaintenance.SchoolListBuilder.buildStateBasedRecords(SchoolListBuilder.java:198)
at edu.apu.schoollistmaintenance.SchoolListBuilder.build(SchoolListBuilder.java:81)
at edu.apu.schoollistmaintenance.SchoolListBuilder.main(SchoolListBuilder.java:402)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)

If the column name "B.STATE" is valid in the query, both inside and outside of Java, why is using it in a getString() call invalid? Thanks.

Ken
 
Carol Enderlin
drifter
Ranch Hand
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Exactly which line is getting the Null pointer exception? Did you try just STATE?

rs.getString("STATE")

Also, you should consider using a prepared statement to set your values rather than using string concatenation, see for example http://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java
 
Ken Duncan
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The NullPointerException is not showing up in my runs right now but it is caused by a null OutputStreamWriter that was not created properly, which is caused by the fact that a prior SQL call, the one shown, failed. In fact, it appears that every getString() in my program is failing with "Invalid column name". I've tried
getString("STATE")
getString("B.STATE")
getString("tablename.STATE")
getString("sysadm.tablename.STATE")

Each of these fails, even though the query appears to work in Java and works if I run it in another tool that talks to Oracle.

Any suggestions? Since I know the queries work, I know the column names are correct.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic