GeeCON Prague 2014*
The moose likes JDBC and the fly likes this is my query running succesfully in database but in my java program rs.next() is false Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "this is my query running succesfully in database but in my java program rs.next() is false" Watch "this is my query running succesfully in database but in my java program rs.next() is false" New topic
Author

this is my query running succesfully in database but in my java program rs.next() is false

ripu sudan
Greenhorn

Joined: Dec 07, 2010
Posts: 20

String query="select TOTAL_SUBS, ACTIVE_SUBS,NEW_SUBSCRIPTION, ONE_WEEK_RENT,TWO_WEEK_RENT,THREE_WEEK_RENT,SUBSCRIPTION ,RBT_PURCHASE,RBT_GIFT,TOTAL_CALL_PULSE ,SMS , ((NEW_SUBSCRIPTION_AMOUNT+ONE_WEEK_AMOUNT+TWO_WEEK_AMOUNT+THREE_WEEK_AMOUNT+SUBSCRIPTION_AMOUNT+TOTAL_PULSE_AMOUNT+SMS_AMOUNT)/2+(RBT_PURCHASE_AMOUNT+RBT_GIFT_AMOUNT)/4)/USD_RATE as REVENUE from daily_invoice where invoice_date like '03-12-2010%'";


java.sql.PreparedStatement pstmt = con.prepareStatement(query);
java.sql.ResultSet rs =null;
System.out.println("RESULT SET "+rs);
rs = pstmt.executeQuery(query);
System.out.println("RESULT SET "+rs);

if( rs.next() )
{

System.out.println("yippee 111 have data");
}



hello everyone i have following query i hard coded it ,
and run in database it is running successfully but when i run it through java program(above given) it is returning
resultset in output

as
RESULT SET null
RESULT SET oracle.jdbc.driver.OracleResultSetImpl@27e353

means there is something in resultset
but is is not going inside rs.next()
means "yippee 111 have data"
is not printed


i asked this question earlier but i could not get any satisfied answer

waiting for response
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I bet that the problem is difference in national settings, and the horrible where clause (where invoice_date like '03-12-2010%').

The invoice_date column is most probably a DATE type. When you compare it to a text value using the LIKE operator, it is implicitly (silently) converted to text using actual national settings. When you run the query in the database client, the national settings are probably set to a format you expect, but in the Java environment it is something different (eg. "Dec 3, 2010" or "2010/12/03"). This does not match the format you're using in the where clause, so you don't get any matching records.

You're probably trying to get all records for December 3rd, 2010. I'd expect the invoice date to not contain time component, because time is usually not present on invoices, but if you really want to be sure, strip the time part using appropriate function for your database (eg. in Oracle it would be trunc(invoice_date, 'DD')). Then compare this value to a date value specified using bind value.

(Note: it is possible to solve his issue without using binds. However, not using binds is another serious bug you definitely should fix in your code. If you don't know the details of binds, read the PreparedStatement documentation, search this forum, or look for a decent JDBC tutorial. There are many of the net.)

Always avoid using implicit conversions in SQL. They are ticking bombs in your code. Imagine your program would by accident work on your computer, but when you deploy it onto another system, or someone at a later time changes the national settings for any reason, your program all of sudden stops working.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

Right. Good advise.

By the way, isn't the Oracle string representation of a date '03-DEC-2010' in stead of '03-12-2010'? And '31-DEZ-2010' in Germany?


OCUP UML fundamental and ITIL foundation
youtube channel
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Jan Cumps wrote:By the way, isn't the Oracle string representation of a date '03-DEC-2010' in stead of '03-12-2010'? And '31-DEZ-2010' in Germany?

I don't know, I've actually made the examples up. The implicit conversion format definitely depends on current session NLS settings, which might be redefined in a login script or on logon trigger to any non-standard, user defined format.
ripu sudan
Greenhorn

Joined: Dec 07, 2010
Posts: 20

thanks
jan,martin



could you provide me updated query that should i use ,i am still facing problem.


ripu sudan
Greenhorn

Joined: Dec 07, 2010
Posts: 20

Thanks a lot guys my problem is solved.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: this is my query running succesfully in database but in my java program rs.next() is false