You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Bob M <rg...@orcon.net.nz> on 2013/10/23 05:44:22 UTC

deleting a record - still a problem

Hi there

The error is that my SELECT can not have "For UPDATE" ????

My current code is
***************************
// retrieve and output date and time of oldest record from the table
rs = s.executeQuery("SELECT * FROM tablename ORDER BY Date ASC, Time ASC
FETCH FIRST ROW ONLY");
rs.next();
String Date2 = rs.getString("Date");
String Time2 = rs.getString("Time");

myConsole.getOut().println("Date/Time: " + Date2 + ", " + Time2);

// and now delete this record.............
s.setCursorName("MYCURSOR");
rs = s.executeQuery("SELECT * FROM tablename ORDER BY Date ASC, Time ASC
FETCH FIRST ROW ONLY FOR UPDATE");
rs.next();

psDelete = conn.prepareStatement("DELETE FROM tablename WHERE CURRENT OF
MYCURSOR");
statements.add(psDelete);

myConsole.getOut().println("Deleted oldest record");



--
View this message in context: http://apache-database.10148.n7.nabble.com/deleting-a-record-still-a-problem-tp134975.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: deleting a record - still a problem

Posted by Rick Hillegas <ri...@oracle.com>.
On 10/22/13 8:44 PM, Bob M wrote:
> Hi there
>
> The error is that my SELECT can not have "For UPDATE" ????
>
> My current code is
> ***************************
> // retrieve and output date and time of oldest record from the table
> rs = s.executeQuery("SELECT * FROM tablename ORDER BY Date ASC, Time ASC
> FETCH FIRST ROW ONLY");
> rs.next();
> String Date2 = rs.getString("Date");
> String Time2 = rs.getString("Time");
>
> myConsole.getOut().println("Date/Time: " + Date2 + ", " + Time2);
>
> // and now delete this record.............
> s.setCursorName("MYCURSOR");
> rs = s.executeQuery("SELECT * FROM tablename ORDER BY Date ASC, Time ASC
> FETCH FIRST ROW ONLY FOR UPDATE");
> rs.next();
>
> psDelete = conn.prepareStatement("DELETE FROM tablename WHERE CURRENT OF
> MYCURSOR");
> statements.add(psDelete);
>
> myConsole.getOut().println("Deleted oldest record");
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/deleting-a-record-still-a-problem-tp134975.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Hi Bob,

Another way to solve this problem would be to add a unique key column to 
your table and use that to specify which row should be deleted. The 
following script shows how this would work.

Hope this helps,
-Rick

-----------------

connect 'jdbc:derby:memory:db;create=true';

-- create a little procedure to pause between inserts
create procedure sleep( millis bigint )
language java parameter style java no sql
external name 'java.lang.Thread.sleep';

create table t( id int generated always as identity, dateCol date, 
timeCol time );

insert into t( dateCol, timeCol ) values ( current date, current time );
call sleep( 1000 );
insert into t( dateCol, timeCol ) values ( current date, current time );
call sleep( 1000 );
insert into t( dateCol, timeCol ) values ( current date, current time );

select * from t order by dateCol, timeCol;

select id from t order by dateCol, timeCol fetch first 1 row only;

delete from t where id =
( select id from t order by dateCol, timeCol fetch first 1 row only );

select * from t order by dateCol, timeCol;

Re: deleting a record - still a problem

Posted by Bob M <rg...@orcon.net.nz>.
Hi John

The SELECT query I am using will not allow me to have "FOR UPDATE" at the
end.

I am happy to use rs.deleteRow(); but can you show me the code modifications
needed please.

Thanks

Bob M



--
View this message in context: http://apache-database.10148.n7.nabble.com/deleting-a-record-still-a-problem-tp134975p134993.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: deleting a record - still a problem

Posted by John English <jo...@gmail.com>.
On 23/10/2013 06:44, Bob M wrote:
> The error is that my SELECT can not have "For UPDATE" ????
>
> s.setCursorName("MYCURSOR");
> rs = s.executeQuery("SELECT * FROM tablename ORDER BY Date ASC, Time ASC
> FETCH FIRST ROW ONLY FOR UPDATE");

So it *can* have FOR UPDATE...

> rs.next();
>
> psDelete = conn.prepareStatement("DELETE FROM tablename WHERE CURRENT OF
> MYCURSOR");
> statements.add(psDelete);

Why not just use rs.deleteRow() instead of using a cursor?

-- 
John English