This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Beginning Java and the fly likes Oracle error on jsp page, but the sql runs fine in Oracle tool Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "Oracle error on jsp page, but the sql runs fine in Oracle tool" Watch "Oracle error on jsp page, but the sql runs fine in Oracle tool" New topic
Author

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

William Peck
Ranch Hand

Joined: Dec 18, 2009
Posts: 54
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



Bill in Baltimore
William Peck
Ranch Hand

Joined: Dec 18, 2009
Posts: 54
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

Joined: Dec 18, 2009
Posts: 54
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Dec 18, 2009
Posts: 54
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

Joined: Dec 18, 2009
Posts: 54
same error . . .
William Peck
Ranch Hand

Joined: Dec 18, 2009
Posts: 54
Ok, I figured it out . . . typo in my DAO Impl . . . sorry about that . . .
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle error on jsp page, but the sql runs fine in Oracle tool
 
Similar Threads
Can't resolve errors in Java / Spring / Oracle application, trying to add a new screen
Need to display data on jsp, above table containing the recordset
Cannot get new screen to work properly; similar screen was recently added successfully
pagination exception in spring
Select clause for listing cities for refined search