I have code 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("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");
*********************
My 4,100 records have a date range from 6 Jan 2009 to 10 Dec 2012 and a Time
range going 0, 6, 12, 18, 0 etc.
The primary key is Date & Time fields
i.e. the oldest record is 6 Jan 2009 / 0
then 6 Jan 2009 / 6, 6 Jan 2009 / 12, 6 Jan 2009 / 18, 7 Jan 2009 / 0 etc.
In my code I am expecting to delete the oldest record each time the process
is run
However the results I get are :-
the deleted records are as follows................
1 Jan 2010 / 0
1 Jan 2010 / 6
1 Jan 2010 / 12 etc etc.
Any ideas on where my code is incorrect ?
Bob M
--
View this message in context:
http://apache-database.10148.n7.nabble.com/Problem-retrieving-the-oldest-record-tp135855.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
