db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bob M <rgmatth...@orcon.net.nz>
Subject Problem retrieving the 'oldest' record
Date Mon, 02 Dec 2013 15:30:12 GMT
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.

Mime
View raw message