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 Inserting and deleting records
Date Sat, 31 Jan 2015 07:27:17 GMT
Hi

I have a section of code which I hope does the following:-
1) inserts one new record with the latest (date + time)
2) deletes one old record with the oldest (date + time)

When I run the code for example, I get 194 new records written where I
expect them to be at the end of the table BUT 10 new records are written at
the beginning of the table(out of date+time) sequence

What should I do to rectify this situation so that all 204 new records
appear at the end of the table ?
The code I have is as follows:-
*******************************************
 psInsert = conn.prepareStatement("INSERT INTO TABLE VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
statements.add(psInsert);

27 - psInsert.xxx lines

psInsert.executeUpdate();

// retrieve oldest record from the table
rs = s.executeQuery("SELECT * FROM TABLE ORDER BY Date ASC,"
        + " Time ASC FETCH FIRST ROW ONLY");
rs.next();
String Date2 = rs.getString("Date");
int Time2 = rs.getInt("Time");

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

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

// commit the above transactions
conn.commit();

} // end of adding new record and deleting oldest one
****************************************************

Bob M



--
View this message in context: http://apache-database.10148.n7.nabble.com/Inserting-and-deleting-records-tp143723.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Mime
View raw message