db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre Tjeldvoll <Dyre.Tjeldv...@oracle.com>
Subject Re: Inserting and deleting records
Date Sat, 31 Jan 2015 12:24:35 GMT

> On 31. jan. 2015, at 08.27, Bob M <rgmatthews@orcon.net.nz> wrote:
> 
> 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

So, what do you mean by “written where I expect them to be”? In SQL there is no concept
of “where” something is written. There is no “natural” order in which records will
be returned when you SELECT them. If you want to ensure a particular order you need to add
an ORDER BY clause. In your case you could order by your DATE/TIME column, but keep in mind
that this sorts according to what is entered. So if you enter the wrong date it may end up
at the beginning, even if it was the last thing you inserted…

You can sort according to the order in which rows where added to table if you add an GENERATED
ALWAYS AS IDENTITY column to your table and ORDER BY this column. Read the docs carefully
before using this as the results are not always intuitive (e.g. there can be gaps in sequence).

Other database systems (among them ORACLE) adds such a column to all tables automatically,
and it is often called ROWID. There you can always do ORDER BY ROWID to get this behavior.
But this is not mandated by the SQL standard and Derby does not to have it.


> 
> 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