CODEWITHSUNDEEP
javaoracle-databasejdbc
Best
Best

Reputation: 2318

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

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;            
        }       
}

Upvotes: 4

Views: 12831

Answers (1)

Zombies
Zombies

Reputation: 25912

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.

Upvotes: 3

Related Questions