nested exception is java.sql.SQLException: Invalid column name ORACLE

Question

I tried to execute the following oracle query using JdbcTemplate in Java:

select RESOURCE_ID 
from REPRO_PRINTING_JOB 
where (USER_ID=? and PRINTING_CENTER_ID=?) 
group by RESOURCE_ID 
union all 
select RESOURCE_ID 
from REPRO_PRINTING_JOB_OLD 
where (USER_ID=? and PRINTING_CENTER_ID=? and STATE='CM') 
group by RESOURCE_ID

The query is working perfectly in oracle query browser but its not working during the execution in java. What could be the source of this problem? I´ve heard something about Jdbc cannot handle case sensitive. Is this true?

ADDED JAVA CODE(CORRECTED) : I call the query using getStatement() which retrieves the query from external source(a properties file) as belows :

   public List<PrintingJob> getPrintingJobsByCreatedUserId(String createdUserId, String userId) {
    if(log.isDebugEnabled()) {
        log.debug("getReportItemsByUserId(" + createdUserId + "," + userId + ")");
    }
    try {               
        System.out.println("getPrintingJobsByResourceId : createdUserId :"+createdUserId+",userId : "+userId);
        return getJdbcTemplate().query(getStatement("gen.report.userid"),                       
                new Object[]{createdUserId, userId, createdUserId, userId},                     
                new PrintingJobMapper());           
        } catch (DataAccessException ex) {              
            log.error("Error executing query: " + ex.getClass() + ":" + ex.getMessage());               
            return null;            
        }       
}

Are you sure the query is read correctly, since you're reading it from a properties file? — GolezTrol
– GolezTrol, Commented Mar 6, 2012 at 8:16
yes I'm pretty sure because if it didn't read the statement correctly, java would have thrown NoSuchElementException where the related key hasn't been found. — Best
– Best, Commented Mar 6, 2012 at 8:19
please print the statement as read from the property file and the complete exception and show us the result. — Jens Schauder
– Jens Schauder, Commented Mar 6, 2012 at 8:32
Are you sure the exception occurs in the select and not in the rowmapper? — tom
– tom, Commented Mar 6, 2012 at 8:32

Zombies · Accepted Answer · 2014-03-14 14:54:07Z

3

The reason you are getting this error is because it is an error from the JDBC driver or java.sql API, not from the database. Note the lack of ORA-XXXXX in the exception message. If it contained that, then it would be related to syntax, invalid name or something that would go wrong on the database server side.

SQLExceptions are thrown in java for anything related to the SQL api, not just from errors that occur when statements are sent over the connection... In this case, it is likely that the query in fact ran correctly but the result set API is giving you errors (rs.getLong("blah"), where column "blah" doesn't exist in your select) is causing problems.

edited Mar 14, 2014 at 14:54

answered Mar 14, 2014 at 14:47

Zombies

26k45 gold badges147 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

nested exception is java.sql.SQLException: Invalid column name ORACLE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related