Update a row in Database

[alvinsanusi]

Sorry to ask a question again.
I have the following function. But somehow the STATUS is not updated to '1' in the table. Is there anything that I have missed out?
Thanks in advance.

con.setAutoCommit(false);

var query = "SELECT * FROM DBASE_CLIENT.ANUM " +
"WHERE STATUS ='0' AND" +
" PREFIX = '" + pfx + "' AND" +
" COMPANY = '" + co + "' AND" +
" GENKEY = '" + genk + "'" +
" FOR UPDATE OF STATUS";
stmt = con.prepareStatement(query);
stmt.setMaxRows(1);

if (stmt.execute())
{
rs = stmt.getResultSet();

if (rs.next())
{
autoNbr = rs.getString("AUTONUM");
// Update the STATUS to 1
rs.updateString("STATUS", "1");
rs.updateRow();
con.commit();
}
rs.close();
}
else
{
autoNbr = " ";
}

[Jon]

That looks desperately complicated to me and you're doing quite a few illegal things. Do you just want to update a single record? If so, you would normally execute a SQL statement something like this:

update dbase_client.anum
set status = '1'
where genkey = xxxxx

..and you make sure that your table has a unique key (genkey) so that only one record is updated.

The simplest way to execute this in Ebase is something like this:

Code: Select all

var sql = "update dbase_client.anum set status = '1' where genkey = 'xxxxx'";
var updateCount = services.database.executeGenericUpdateStatement(databaseConnectionName, sql);

..where databaseConnectionName is the name of the database connection. Variable updateCount should have a value of 1 after the update. Using services.database looks after closing all the connection and other sql objects for you. All transactions are committed automatically or rolled back if there is an error - you don't need to worry about this.

There are other ways of doing this in Ebase - in particular, where you have one or more rows loaded into an Ebase table. Then you can just issue updateTable() to the table having changed some values and the system will sort out all the SQL for you. But this may be for later.

[alvinsanusi]

hi Jon,

Thanks for your feedback.
What I actually want to do are :

1. Get the 1st row of rows that satisfy the WHERE condition. The rows can be more than 1.
2. Retrieve the content of AUTONUM field.
3. Set the STATUS field of this row to '1' so that when the same SELECT is executed, it is not picked up again. STATUS = '1' means it is invalid row already.

I read from some info, I need 2 SQL statement, the first one is SELECT, the second one is UPDATE.
But I wonder if using SELECT FOR UPDATE, I can perform the same function or not.