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 = " ";
}
Update a row in Database
Moderators: Jon, Steve, Ian, Dave
-
- Ebase User
- Posts: 11
- Joined: Thu Jan 05, 2017 11:13 am
- Location: Jakarta
- Contact:
-
- Moderator
- Posts: 1342
- Joined: Wed Sep 12, 2007 12:49 pm
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:
..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.
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);
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.
0 x
-
- Ebase User
- Posts: 11
- Joined: Thu Jan 05, 2017 11:13 am
- Location: Jakarta
- Contact:
Same Subject
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.
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.
0 x
It is what all eyes see and all fingers point to
Who is online
Users browsing this forum: No registered users and 10 guests