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

Ned

How are your tables defined? How's the variant tried directly on Oracle? Please add them to your question. – Ivetteivetts

@Ivetteivetts 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 :) – Ned

So you don't even have the name of the invalid column? How should we be able help you? – Galliwasp

The variant is very similar, I just remove more on the where clause, but I can add it if it is that important – Ned

Add the stacktrace instead. Saying you get "invalid column name" tells absolutely nothing. – Schild

Stacktrace and oracle sql which works is added. – Ned

@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 – Ned

@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 – Ned

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? – Modernity

Answer 1

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").

Ukulele

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. – Ned

I finally made it work using getObject(int index). Thank you. It wasn't really clear from the error message. – Ned

@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. – Ukulele

@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 – Galloglass

@Galloglass 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 – Ned

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 – Galloglass

Answer 2

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.

Upmost

Answer 3

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

Brewster