my dog learned polymorphism*
The moose likes JDBC and the fly likes SQL Stmt not working in Java App % Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Stmt not working in Java App %" Watch "SQL Stmt not working in Java App %" New topic
Author

SQL Stmt not working in Java App %

Lisa Modglin
Ranch Hand

Joined: Oct 28, 2003
Posts: 46
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

Joined: Jul 27, 2002
Posts: 54
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

Joined: Oct 28, 2003
Posts: 46
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

Joined: Jul 27, 2002
Posts: 54
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: SQL Stmt not working in Java App %
 
Similar Threads
time conversion
Convert to timestamp yyyy-MM-dd hh:mm:ss a
data match
Having trouble using Dates in SQL
time stamp question