updating a maximum of 2 records

[Bob M <rg...@orcon.net.nz>]

Hi

I am trying to code for the ability to update either two records (the two
records which have the latest date & time) or just one (the record which has
the latest date & time) for the profit field
If two_time is TRUE, then two records are requiring updating, otherwise just
the one record
The records which need updating have 999.99 in the profit field initially
There are x+2 records in the database
At the moment, no updating is taking place - any idea why ?

****************************************************************
// if we have two records to update
// i.e. the penultimate record which was closed at the last clock-in point
// and the ultimate record which was opened at the same time and has since
closed
if ((two_time) && (!initialize) && (myCanTrade) && (myCurrPositionValue >0))
{
// retrieve the last two records
rs = s.executeQuery("SELECT * FROM TABLE ORDER BY Trading_Date ASC,"
        + " Trading_Time ASC OFFSET x ROWS FETCH NEXT 2 ROWS ONLY");
rs.next();
String Date2 = rs.getString("Trading_Date");
int Time2 = rs.getInt("Trading_Time");
s.setCursorName("MYCURSOR");
rs = s.executeQuery("SELECT * FROM TABLE WHERE Date = " + Date2 + " AND Time
= " + Time2 + " FOR UPDATE");
rs.next();
conn.prepareStatement("UPDATE TABLE SET PROFIT_LOSS = prev_profit WHERE
CURRENT OF MYCURSOR").executeUpdate();
prev_profit = 0;
//move on to the last record
rs.next();
String Date3 = rs.getString("Trading_Date");
int Time3 = rs.getInt("Trading_Time");
s.setCursorName("MYCURSOR");
rs = s.executeQuery("SELECT * FROM TABLE WHERE Date = " + Date3 + " AND Time
= " + Time3 + " FOR UPDATE");
rs.next();
conn.prepareStatement("UPDATE TABLE SET PROFIT_LOSS = profit WHERE CURRENT
OF MYCURSOR").executeUpdate();
two_time =false;
} // end of updating the last two records
 else {
// else, if we have just the one record to update 
// retrieve and output date and time of the latest record from the table
// providing this is not the first trade and we are about to open a new
trade !
if ((!initialize) && (myCanTrade) && (myCurrPositionValue >0)) {
psUpdate = conn.prepareStatement("UPDATE TABLE SET PROFIT_LOSS=? WHERE
PROFIT_LOSS = 999.99");
statements.add(psUpdate);
psUpdate.setDouble(1, profit);
psUpdate.executeUpdate();
}
} // end of updating latest record
*************************************************************

Bob M




--
View this message in context: http://apache-database.10148.n7.nabble.com/updating-a-maximum-of-2-records-tp143764.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.