File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle error on jsp page, but the sql runs fine in Oracle tool

 
William Peck
Ranch Hand
Posts: 54
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I get this error and not sure where to go to troubleshoot, since the query runs fine in Oracle (I ran the query as the same user as the jsp page)

The rest of the application runs fine.

Error 500: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar
[select et.srce_sys_cd, trunc(et.load_dt) as date_loaded, count(*) as num_errors, et.fld_nm, et.mtrcs_errs_linkg_id
from stage3i.err_tbl et
where et.mtrcs_errs_linkg_id = 4413
group by et.srce_sys_cd, trunc(et.load_dt), et.mtrcs_errs_linkg_id, et.fld_nm, et.err_cd, et.err_sqlcode order by 4 ];

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

I got some questions answered yesterday on setting up the server pieces, here's all the code:

Code below is
- jsp
- Controller
- DAO Impl
- DAO
- Primary Class

 
William Peck
Ranch Hand
Posts: 54
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I changed the query to pull from a different table (same number of columns, same datatypes in the same position as the original query, etc.) - same result: nested exception is java.sql.SQLException: Invalid column name

So I'm at the same spot as before, not sure how to troubleshoot this. Only difference from the error above is the different sql being used.

Here's the new error message:
Error 500: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar
[select et.srce_sys_cd, trunc(et.proc_strt_dt) as date_loaded, err_cnt, et.proc_nm, et.mtrcs_errs_linkg_id from ods.proc_stus et where et.mtrcs_errs_linkg_id = 4413 order by 4 ];
nested exception is java.sql.SQLException: Invalid column name
 
William Peck
Ranch Hand
Posts: 54
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, I hard coded the whole query and still got "invalid column name"

Error 500: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar
[select '104', trunc(sysdate) as date_loaded, 500, 'Error Description', 555 from sys.dual order by 4 ];
nested exception is java.sql.SQLException: Invalid column name

So I'll start looking at other things . . .
 
Paul Clapham
Sheriff
Pie
Posts: 20188
26
MySQL Database
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To me, "order by 4" looks like it has an invalid column name. And it's the only thing in common between all of your
examples.
 
William Peck
Ranch Hand
Posts: 54
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul, ok that's worth trying, but Oracle can handle that, it's a common practice.

But I will try it and see, thanks.
 
William Peck
Ranch Hand
Posts: 54
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
same error . . .
 
William Peck
Ranch Hand
Posts: 54
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, I figured it out . . . typo in my DAO Impl . . . sorry about that . . .
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic