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

Question

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()){
    ........
}

Answer 1

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 .."

Answer 2

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.