Problems still in retrieving 'oldest' record and 'newest' record

[Bob M <rg...@orcon.net.nz>]

Hi again

My code currently does three transactions
1) updates the 'newest' record
2) inserts/appends a brand new record
3) deletes the 'oldest' record

Of these three only number 2 is working correctly

1) my code does NOT identify the 'newest' record correctly

3) my code does NOT identify the 'oldest' record correctly

My records use the first two fields as the unique key
The first field is a date field and the second is a time field [the second
only has 4 possible values 0, 6, 12, and 18]

My code for (1) above is as follows.......................
****************************************************************************** 
// retrieve the last record from the table 
rs = s.executeQuery("SELECT * FROM USD_JPY ORDER BY Date DESC, Time DESC
FETCH FIRST ROW ONLY"); 
rs.next(); 
String Date1 = rs.getString("Date"); 
String Time1 = rs.getString("Time"); 

myConsole.getOut().println("Latest record:- Date/Time: " + Date1 + ", " +
Time1); 

// Update this record by adding predicted return and predicted class 
psUpdate = conn.prepareStatement("UPDATE USD_JPY SET
Return_predicted=?,Class_predicted=? WHERE Date = '" + Date1 +"' AND Time = 
" + Time1); 
statements.add(psUpdate); 

psUpdate.setDouble(1, return_current); 
psUpdate.setString(2, class_current); 
psUpdate.executeUpdate();

myConsole.getOut().println("Updated latest record with predicted return: " +
return_current + ", predicted class: " + class_current); 

*********************************************************** 

My code for (3) above is as follows...............
****************************************************************************** 
// retrieve and output date and time of oldest record from the table 
rs = s.executeQuery("SELECT * FROM USD_JPY ORDER BY Date ASC, Time ASC FETCH
FIRST ROW ONLY"); 
rs.next(); 
String Date2 = rs.getString("Date"); 
int Time2 = rs.getInt("Time"); 

myConsole.getOut().println("Oldest record:- Date/Time: " + Date2 + ", " +
Time2); 

// and now delete this record............. 

s.setCursorName("MYCURSOR"); 
rs = s.executeQuery("SELECT * from USD_JPY WHERE Date = '" + Date2 + "' AND
Time = " + Time2 
+ " FOR UPDATE"); 
rs.next(); 
conn.prepareStatement("DELETE FROM USD_JPY WHERE CURRENT OF
MYCURSOR").executeUpdate(); 

myConsole.getOut().println("Deleted oldest record"); 
*********************************************************** 

The code shows the 'latest' record as 31-12-2010, 12 and the next one as
31-12-2010, 18
The code shows the 'oldest' record as 01-01-2010, 0 and the next one as
01-01-2010, 6

Any suggestions welcome............

Bob M







--
View this message in context: http://apache-database.10148.n7.nabble.com/Problems-still-in-retrieving-oldest-record-and-newest-record-tp135875.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.