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.