java.sql.SQLException: Invalid column name

Question

I cannot figure out why I am getting "Invalid column name" here.

We have tried a variant of the sql directly in Oracle, and it works fine, but when I try it using jdbcTemplate then something is wrong.

List<Dataholder> alleXmler = jdbcTemplate.query("select p.applicationid, x.datadocumentid, x.datadocumentxml " +
                        "from CFUSERENGINE51.PROCESSENGINE p " +
                        "left join CFUSERENGINE51.DATADOCUMENTXML x " +
                        "on p.processengineguid = x.processengineguid " +
                        "where x.datadocumentid = 'Disbursment' " +
                        "and p.phasecacheid = 'Disbursed' ",
                (rs, rowNum) -> {
                    return Dataholder.builder()
                            .applicationid(rs.getInt("p.applicationid"))
                            .datadocumentId(rs.getInt("x.datadocumentid"))
                            .xml(lobHandler.getClobAsString(rs, "x.datadocumentxml"))
                            .build();
                });

The entire sql that works on Oracle is this:

select
process.applicationid,
xml.datadocumentid,
xml.datadocumentxml
from CFUSERENGINE51.PROCESSENGINE process
left join CFUSERENGINE51.DATADOCUMENTXML xml
on process.processengineguid = xml. processengineguid
where xml.datadocumentid = 'Disbursment'
and process.phasecacheid = 'Disbursed'
and process.lastupdatetime > sysdate-14

The entire stacktrace:

java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.boot.maven.AbstractRunMojo$LaunchRunner.run(AbstractRunMojo.java:507)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalStateException: Failed to execute CommandLineRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:803)
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:784)
    at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:771)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1186)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1175)
    at no.gjensidige.bank.datavarehus.kontonrinfridd.Application.main(Application.java:44)
    ... 6 more
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select p.applicationid, x.datadocumentid, x.datadocumentxml from CFUSERENGINE51.PROCESSENGINE p left join CFUSERENGINE51.DATADOCUMENTXML x on p.processengineguid = x.processengineguid where x.datadocumentid = 'Disbursment' ]; nested exception is java.sql.SQLException: Invalid column name
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:474)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:484)
    at no.gjensidige.bank.datavarehus.kontonrinfridd.Application.run(Application.java:61)
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800)
    ... 12 more
Caused by: java.sql.SQLException: Invalid column name
    at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:4146)
    at oracle.jdbc.driver.InsensitiveScrollableResultSet.findColumn(InsensitiveScrollableResultSet.java:300)
    at oracle.jdbc.driver.GeneratedResultSet.getString(GeneratedResultSet.java:1460)
    at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:267)
    at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:267)
    at no.gjensidige.bank.datavarehus.kontonrinfridd.Application.lambda$run$0(Application.java:69)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:463)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
    ... 16 more

How are your tables defined? How's the variant tried directly on Oracle? Please add them to your question. — Alfabravo, Jan 23 '17 at 15:51
@Alfabravo I honestly don't know. I have gotten this sql sent by someone, who showed me it running directly in Oracle. So I know it should work. But "Invalid column name" means it is not an error from Oracle, but spring-jdbc it self. So it should be something obvious I think. Just not to me :) — Shervin Asgari, Jan 23 '17 at 15:53
So you don't even have the name of the invalid column? How should we be able help you? — juergen d, Jan 23 '17 at 15:54
The variant is very similar, I just remove more on the where clause, but I can add it if it is that important — Shervin Asgari, Jan 23 '17 at 15:54
Add the stacktrace instead. Saying you get "invalid column name" tells absolutely nothing. — Kayaman, Jan 23 '17 at 15:55
@AlexPoole Yes I tried adding that space because the sql pasted to me had it also. I get the same error even if I remove the space — Shervin Asgari, Jan 23 '17 at 16:00
@Berger The space is not supposed to be there. I just tried putting the space because the sql I got had the space. But I figure its just a copy/paste error. I still get the same error even with or without the space — Shervin Asgari, Jan 23 '17 at 16:03
So if you run the generated SQL you can see in the stack trace through a client (SQL\*Plus, SQL Developer etc.) and against the same database you don't get the error? Maybe the identifiers are being automatically quoted and it isn;t showing that happening? — Alex Poole, Jan 23 '17 at 16:08

Luke Woodward · Accepted Answer · 2017-01-27T20:42:15.903

59

The problem isn't the query. The query is running fine.

The problem is in the row-mapping that converts a row from the ResultSet into a domain object. It seems that as part of the row-mapping in your application you are trying to read out of the ResultSet a value from a column that it doesn't contain.

The key lines of your stacktrace are the following three, near the bottom:

    at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:267)
    at no.gjensidige.bank.datavarehus.kontonrinfridd.Application.lambda$run$0(Application.java:69)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)

The middle of these three lines would appear to be in your code. Line 69 of your Application class contains a lambda which is calling ResultSet.getString(), but as this results in an 'Invalid column name' error, then (a) you are passing a string for a column name rather than a numeric column index, and (b) the column name you're passing in doesn't exist in the result set.

Now that you've edited your question to include the call to jdbcTemplate.query(), and in particular the lambda responsible for mapping a result-set row to an object, the problem is a little clearer. When calling rs.getInt(...) or rs.getString(...) with column names as opposed to indexes, don't include prefixes such as p. or x.. Instead of writing rs.getInt("p.applicationid") or rs.getInt("x.datadocumentid"), write rs.getInt("applicationid") or rs.getInt("datadocumentid").

edited Jan 27 '17 at 20:42

answered Jan 24 '17 at 20:36

Luke Woodward

63,336
16
89
104

I think you are on to something here. Its my lack of knowledge of how jdbcTemplate works. I have updated the question with code of how I try to read the data. Can you please have a look? I changed the getString() to getInt, and it still doesnt work. – Shervin Asgari Jan 25 '17 at 09:54
I finally made it work using getObject(int index). Thank you. It wasn't really clear from the error message. – Shervin Asgari Jan 25 '17 at 11:21
1

@ShervinAsgari: drop the prefixes `p.` and `x.` from the column names in the calls to `getInt` or `getString`: they should be `applicationid` or `datadocumentid` rather than `p.applicationid` or `x.datadocumentid`. – Luke Woodward Jan 25 '17 at 21:00
@ShervinAsgari. Do u have any idea what was the issue and what is the final resolution ? I am getting the same error and it's working in my local environment but not in UAT – Stunner Oct 22 '18 at 07:34
@Stunner I changed to using index instead of column name. That worked. Why it is not working in your UAT environment, I don't know. Perhaps you should debug all the parameters and make sure they dont have a weird value – Shervin Asgari Oct 23 '18 at 09:02
finally able to resolve this issue. Issue was due to the sortKey property tag. property name="sortKey" value="columnName" /> . Here whatever column name we give in this tag , the same column should be present in the select query. If it is missing , it gives this invalid column error – Stunner Oct 24 '18 at 07:27

score 2 · Answer 2 · answered Jun 17 '21 at 18:05

java.sql.SQLException: Invalid column name at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:4146) at oracle.jdbc.driver.InsensitiveScrollableResultSet.findColumn(InsensitiveScrollableResultSet.java:300) at oracle.jdbc.driver.GeneratedResultSet.getInt(GeneratedResultSet.java:1350) at com.zaxxer.hikari.pool.HikariProxyResultSet.getInt(HikariProxyResultSet.java)

The issue also occurs when the index_name you are trying to fetch from Resultset is not present, please check your query once and match with the column index name that you are trying to fetch from ResultSet. This can be on the possibility of this cause.

score 0 · Answer 3 · answered Jan 23 '17 at 17:04

You need the table definition to find out where the problem is. Install / run Oracle SQL Developer (it is free), setup JDBC connection and investigate the schema.

You need to check existence of following columns:

CFUSERENGINE51.PROCESSENGINE.applicationid,
CFUSERENGINE51.PROCESSENGINE.lastupdatetime
CFUSERENGINE51.PROCESSENGINE.phasecacheid
CFUSERENGINE51.PROCESSENGINE.processengineguid
CFUSERENGINE51.DATADOCUMENTXML.datadocumentid
CFUSERENGINE51.DATADOCUMENTXML.datadocumentxml
CFUSERENGINE51.DATADOCUMENTXML.processengineguid

java.sql.SQLException: Invalid column name

3 Answers3