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/12/02 16:48:29 UTC
Problem updating a record
Hi there
I have the following code:-
******************************************************************************
// retrieve the last record from the table
rs = s.executeQuery("SELECT * FROM USD_JPY ORDER BY Date DESC, Time DESC
FETCH FIRST ROW ONLY");
rs.next();
String Date1 = rs.getString("Date");
String Time1 = rs.getString("Time");
myConsole.getOut().println("Date/Time: " + Date1 + ", " + Time1);
// Update this record by adding predicted return and predicted class
psUpdate = conn.prepareStatement("UPDATE USD_JPY SET
Return_predicted=?,Class_predicted=?");
statements.add(psUpdate);
psUpdate.setDouble(1, return_current);
psUpdate.setString(2, class_current);
conn.commit();
myConsole.getOut().println("Updated latest record with predicted return: " +
return_current + ", predicted class: " + class_current);
***********************************************************
However, the record is NOT being updated as I am wishing it to be ?
Any ideas ?
Bob M
--
View this message in context: http://apache-database.10148.n7.nabble.com/Problem-updating-a-record-tp135856.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Problem updating a record
Posted by "David G.P." <vi...@gmail.com>.
I would rather use '?' for the dates instead of concatenating them...
2013/12/3 Bob M <rg...@orcon.net.nz>
> Thanks Myrna
>
> I can't tell you want happens as I have not run the code with a commit yet!
>
> Adding the one line psUpdate.executeUpdate(); is OK - thank you
>
> You are spot on with what I was assuming............I did think that the
> update would only apply to the last record in my table
>
> What I am trying to do is to update only the newest record in the table
> That record has the highest primary key which is made up of the first two
> fields in each record
> Field 1 = Date and Field 2 = Time (which has values 0, 6, 12, and 18)
>
> Are you suggesting that the following would suffice?
>
> psUpdate = conn.prepareStatement("Update USD_JPY SET Return_predicted=?,
> Class_predicted=? where Date=" +Date1 +"and Time=" + Time1)
>
> A final question....................
>
> If one was inserting a new record using psInsert what code do you use to
> execute the Insert?
>
> Bob M
>
>
>
> --
> View this message in context:
> http://apache-database.10148.n7.nabble.com/Problem-updating-a-record-tp135856p135873.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Re: Problem updating a record
Posted by Bob M <rg...@orcon.net.nz>.
Thanks Myrna
I can't tell you want happens as I have not run the code with a commit yet!
Adding the one line psUpdate.executeUpdate(); is OK - thank you
You are spot on with what I was assuming............I did think that the
update would only apply to the last record in my table
What I am trying to do is to update only the newest record in the table
That record has the highest primary key which is made up of the first two
fields in each record
Field 1 = Date and Field 2 = Time (which has values 0, 6, 12, and 18)
Are you suggesting that the following would suffice?
psUpdate = conn.prepareStatement("Update USD_JPY SET Return_predicted=?,
Class_predicted=? where Date=" +Date1 +"and Time=" + Time1)
A final question....................
If one was inserting a new record using psInsert what code do you use to
execute the Insert?
Bob M
--
View this message in context: http://apache-database.10148.n7.nabble.com/Problem-updating-a-record-tp135856p135873.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Problem updating a record
Posted by Myrna van Lunteren <m....@gmail.com>.
Bob,
You're not telling us what does happen, so we're doing our best...
The missing code? Bryan said:
"call executeUpdate() on the prepared statement in order to execute it."
So, add:
psUpdate.executeUpdate();
after setting the values, but before doing the commit. See for examples and
explanation a simple JDBC tutorial or book, for instance:
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
- Bryan also thinks the SQL you've written may not do what you think/want.
Perhaps you were hoping that FETCH FIRST ROW ONLY from your query would
magically trickle down to the update?
But it will not unless you make it so. So with the current update SQL
you'll end up with updating every row in the table with the same two values.
But perhaps that is what you want.
If not, there are different ways to accomplish updating just one row.
You'll have to figure out what works there yourself. Perhaps there's
another key (id) field in the table that identifies each unique row - if
so, you can get that value in your select, and change the update SQL to
have something like 'UPDATE USD_JPY SET
Return_predicted=?,Class_predicted=? where keycolumn=" + keyvalue, or maybe
"UPDATE USD_JPY SET
Return_predicted=?,Class_predicted=? where Date=" + Date1 + "and Time=" +
Time1. Or perhaps you can rework the code to use updatable cursors and use
Resultset.rsUpdaterow method - but then I think you cannot use the ORDER BY
clauses in your select. (see:
http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html and
http://db.apache.org/derby/docs/10.10/ref/rrefsqlj41360.html#rrefsqlj41360__sqlj15384
)
HTH
Myrna
On Mon, Dec 2, 2013 at 6:15 PM, Bob M <rg...@orcon.net.nz> wrote:
> It would help if you could show me the additional code I need to accomplish
> my task
>
> Bob M
>
>
>
> --
> View this message in context:
> http://apache-database.10148.n7.nabble.com/Problem-updating-a-record-tp135856p135870.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Re: Problem updating a record
Posted by Bob M <rg...@orcon.net.nz>.
It would help if you could show me the additional code I need to accomplish
my task
Bob M
--
View this message in context: http://apache-database.10148.n7.nabble.com/Problem-updating-a-record-tp135856p135870.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Problem updating a record
Posted by Bryan Pendleton <bp...@gmail.com>.
> // Update this record by adding predicted return and predicted class
> psUpdate = conn.prepareStatement("UPDATE USD_JPY SET
> Return_predicted=?,Class_predicted=?");
> statements.add(psUpdate);
>
> psUpdate.setDouble(1, return_current);
> psUpdate.setString(2, class_current);
>
> conn.commit();
>
> However, the record is NOT being updated as I am wishing it to be ?
For one thing, it seems like you should call executeUpdate() on
the prepared statement in order to execute it.
For another thing, since the UPDATE statement has no WHERE clause,
it's going to update every row in the table, not just a single one.
thanks,
bryan