CODEWITHSUNDEEP
javasqljdbcsybase
masay
masay

Reputation: 933

No row returned from db - but there are records to be returned

I am executing the select statement with using jdbc sybase driver (jconn3). I checked the statement with executed manually on isql and all rows returned correctly. The statement which is executing on jdbc :

select * from mytable where date between ? and ?

I added the dateformat as yyyy-MM-dd HH:mm:ss and set the time value as 00:00:00 for begin date and 23:59:59 for end date.

It is not working. The row count must be 1000 but it is sometimes 770, sometimes 990, sometimes 564 etc.. There is no any specific row count which everytime returned.

After that I added an extra execution which returns only row count. First I am executing the select count(*) from ... statement then executing select * from .... and now `select * from ... query returns the correct number of records everytime. This can not be related with caching. And weird thing is , I am using same preparedstatement and resultset objects for those two execution.

Any idea on that issue?

@Rulmeq, here is the code (added on 2012-03-29)

ResultSet rs = null;
PreparedStatement ps = null;

ps = myConn.getConnection().prepareStatement("select count(*) from myTable where date between ? and ?");
ps.setDate(1, new java.sql.Date(beginDate.getTime()));   // format : yyyy-MM-dd
ps.setDate(2, new java.sql.Date(endDate.getTime()));    // format : yyyy-MM-dd
rs = ps.executeQuery();
rs.next();
// some logs here

ps = myConn.getConnection().prepareStatement("select * from myTable where date between ? and ?");
ps.setTimestamp(1, new java.sql.Timestamp(beginDate.getTime()));    // format : yyyy-MM-dd hh:mm:ss  
ps.setTimestamp(2, new java.sql.Timestamp(endDate.getTime()));  // format : yyyy-MM-dd hh:mm:ss  
rs = ps.executeQuery();
while(rs.next()){
    ........
}

Upvotes: 7

Views: 1010

Answers (4)

masay
masay

Reputation: 933

Thanks for all but issue has been solved and it was not related with jdbc. It was related with using System.currentTimeMillis() and host system is too fast. That's why system sometime uses the same ms. I changed the references.

Upvotes: -1

Paul Morgan
Paul Morgan

Reputation: 32528

Is date defined as date, datetime, smalldatetime or timestamp in myTable? You are using setDate and setTimestamp. One of them doesn't match the date type defined in myTable.

Upvotes: 1

titogeo
titogeo

Reputation: 2184

In Oracle TO_DATE function would help like below. 

 "select count(*) from myTable where date between TO_DATE(?, 'yyyy-mm-dd') and TO_DATE(?, 'yyyy-mm-dd')"

Upvotes: 0

Shahbaz Chishty
Shahbaz Chishty

Reputation: 502

What I think the problem is the code you are using to assign datetime values to the query arguments. And now as you specified that the code with "select * from ..." is working fine, so i think the only difference between them is that you can use

ps = myConn.getConnection().prepareStatement("select Count(*) from myTable where date between ? and ?");
ps.setTimestamp(1, new java.sql.Timestamp(beginDate.getTime()));
ps.setTimestamp(2, new java.sql.Timestamp(endDate.getTime()));  
rs = ps.executeQuery();

for "select Count(*) from .."

Upvotes: 2

Related Questions