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/21 20:53:32 UTC

Retrieving the 'oldest' record and deleting it

Hi

I have the following code which retrieves the 'oldest' record and prints out 
some data

rs = s.executeQuery("SELECT from tablename ORDER BY Date ASC, Time, ASC
FETCH FIRST ROW ONLY");
rs.next();
String Date = rs.getString("Date");
String Date = rs.getString("Time");
myConsole.getOut().println("Date/Time " + Date ", " + Time);

Now I wish to delete this record and the code is..........

rs = s.executeQuery("DELETE from tablename WHERE ????");

What is the ?????

Bob M



--
View this message in context: http://apache-database.10148.n7.nabble.com/Retrieving-the-oldest-record-and-deleting-it-tp134915.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Retrieving the 'oldest' record and deleting it

Posted by Rick Hillegas <ri...@oracle.com>.
On 10/22/13 10:40 AM, Bob M wrote:
> Thanks Rick
>
> Keeping the code 'in sync' is difficult when applying example code :)
>
> Will the following do what I am seeking?
>
>   rs = s.executeQuery("SELECT from tablename ORDER BY Date ASC, Time, ASC
>> FETCH FIRST ROW ONLY");
>> rs.next();
>> String Date = rs.getString("Date");
>> String Date = rs.getString("Time");
>> myConsole.getOut().println("Date/Time " + Date ", " + Time);
>>
>>
> At this stage , I am successfully printing out the correct record data!
>
> Now I wish to delete this record..........
>
>           conn.setAutoCommit( false );
>
>           s.setCursorName( "MYCURSOR" );
>           rs = s.executeQuery( repeat the above...............);
Hi Bob,

The SELECT statement needs to end with the "FOR UPDATE" phrase in order 
to make the positioned DELETE work.

Hope this helps,
-Rick
>           rs.next();
>
>           psDelete = conn.prepareStatement( "DELETE from tablename where
> current of
> MYCURSOR" );
>           statements.add(psDelete);
>
> }
>
> }
>
>
> p.s.
> code further above this lot................
>
> ArrayList statements = new ArrayList();
> PreparedStatement psInsert = null;
> PreparedStatement psUpdate = null;
> PreparedStatement psDelete  = null;
> Statement s = null;
> ResultSet rs = null;
>
>
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Retrieving-the-oldest-record-and-deleting-it-tp134915p134956.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>

Re: Retrieving the 'oldest' record and deleting it

Posted by Bob M <rg...@orcon.net.nz>.
Thanks Rick

Keeping the code 'in sync' is difficult when applying example code :)

Will the following do what I am seeking?

 rs = s.executeQuery("SELECT from tablename ORDER BY Date ASC, Time, ASC 
> FETCH FIRST ROW ONLY"); 
> rs.next(); 
> String Date = rs.getString("Date"); 
> String Date = rs.getString("Time"); 
> myConsole.getOut().println("Date/Time " + Date ", " + Time); 
> 
>
At this stage , I am successfully printing out the correct record data!

Now I wish to delete this record..........
 
         conn.setAutoCommit( false ); 

         s.setCursorName( "MYCURSOR" ); 
         rs = s.executeQuery( repeat the above...............); 
         rs.next(); 

         psDelete = conn.prepareStatement( "DELETE from tablename where
current of 
MYCURSOR" ); 
         statements.add(psDelete);     

} 

}


p.s.
code further above this lot................

ArrayList statements = new ArrayList();
PreparedStatement psInsert = null;
PreparedStatement psUpdate = null;
PreparedStatement psDelete  = null;
Statement s = null;
ResultSet rs = null;
 




--
View this message in context: http://apache-database.10148.n7.nabble.com/Retrieving-the-oldest-record-and-deleting-it-tp134915p134956.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Retrieving the 'oldest' record and deleting it

Posted by Rick Hillegas <ri...@oracle.com>.
On 10/21/13 11:53 AM, Bob M wrote:
> Hi
>
> I have the following code which retrieves the 'oldest' record and prints out
> some data
>
> rs = s.executeQuery("SELECT from tablename ORDER BY Date ASC, Time, ASC
> FETCH FIRST ROW ONLY");
> rs.next();
> String Date = rs.getString("Date");
> String Date = rs.getString("Time");
> myConsole.getOut().println("Date/Time " + Date ", " + Time);
>
> Now I wish to delete this record and the code is..........
>
> rs = s.executeQuery("DELETE from tablename WHERE ????");
>
> What is the ?????
>
> Bob M
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Retrieving-the-oldest-record-and-deleting-it-tp134915.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Hi Bob,

The following program may shed some light on how to perform positioned 
deletes.

Hope this helps,
-Rick

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

import java.sql.*;

public class w
{
     public  static  void    main( String... args ) throws Exception
     {
         Connection  conn = DriverManager.getConnection( 
"jdbc:derby:memory:db;create=true" );

         conn.prepareStatement( "create table t( a int generated always 
as identity, b int )" ).execute();
         conn.prepareStatement( "insert into t( b ) values ( 10 ), ( 20 
), ( 30 )" ).execute();

         conn.setAutoCommit( false );

         Statement   cursorStatement = conn.createStatement();
         cursorStatement.setCursorName( "MYCURSOR" );
         ResultSet   rs = cursorStatement.executeQuery( "select * from t 
where b = 20 for update" );
         rs.next();

         conn.prepareStatement( "delete from t where current of 
MYCURSOR" ).executeUpdate();
     }

}