aspose file tools*
The moose likes JDBC and the fly likes Invalid Column Name in getString()--query worked Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Invalid Column Name in getString()--query worked" Watch "Invalid Column Name in getString()--query worked" New topic
Author

Invalid Column Name in getString()--query worked

Ken Duncan
Ranch Hand

Joined: Apr 30, 2007
Posts: 46
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

Joined: Oct 10, 2000
Posts: 1364
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

Joined: Apr 30, 2007
Posts: 46
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Invalid Column Name in getString()--query worked