• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Stmt not working in Java App %

 
Lisa Modglin
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to pull a monthly report. I tested my SQL statement in SQL Scratchpad and it works great. However, it does not work so well when I assign the SQL statement to a string and pass it to the statement object. Here is my code:

Timestamp now = new Timestamp ( new java.util.Date().getTime() );
SimpleDateFormat sdf = new SimpleDateFormat( "MMM" );
String thisMonth = sdf.format( now );
String sql = "select * from G2.HD_FEEDBACK where HD_DATE like '%-" + thisMonth + "-2004'";

HARD CODED: sql = "select * from G2.HD_FEEDBACK where HD_DATE like '%-Feb-2004' "; doesn't work either.

This works: sql = "select * from G2.HD_FEEDBACK where HD_DATE between '01-" + thisMonth + "-2004' and '29-" + thisMonth + "-2004'"
I print the value of sql to the console and it comes back like this:

select * from G2.HD_FEEDBACK where HD_DATE like '%-Feb-2004'
Any help? Thanks!
 
Rudy Dakota
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Howdy Lisa,
You 're not incidently querying a DATE column, are you? Wouldn't be surprised to see the VARCHAR syntax not working on those, would I!
More in general: don't forget telling us what kind of database you are using. Makes it so much easier to answer. For myself, I can easily rope in the Oracle loose ends, but when you 're talking Evil Bill's stuff, that 's different stuff!
Good riddance,
Rudy.
 
Lisa Modglin
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I'm querying a Date field in an Oracle 9i database. So why does the query work in the Oracle 9i OEM SQL Scratchpad and not via the Java app?
Lisa
 
Rudy Dakota
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Lisa,
Good point, why does it? I have never worked with the scratchpad myself, so I am not very familiar with it. I do suppose however that it does take care of some casting for you. In other situations (SQL*Plus vs. JDBC programming) I have seen queries that are not entirely comparable, due to that kind of issues. Specifically, it looks like there 's a cast from VARCHAR to DATE being done by your scratchpad that the JDBC interface doesn't take care of. Not entirely satisfactory as an answer, but there you have my best guess.
Good riddance,
Rudy.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic