• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
ripu sudan
Greenhorn
Posts: 20
Java Linux Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 20
Java Linux Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks
jan,martin



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


 
ripu sudan
Greenhorn
Posts: 20
Java Linux Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot guys my problem is solved.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic