Oracle update command and timestamp - how to get it back?

[PeterZ]

To all you Oracle gurus out there,

I'm updating an Oracle table with an OleDbDataAdapter (win forms).

I'm using a timestamp column rather than comparing all the values to
determine concurrency violation. The way I set the timestamp column is
within the actual dataAdapter update/insert statement.

SYS_TIMESTAMP = SYSDATE ..... equivalent of Now() in SQL I believe?

Screenshot of update query builder:
http://www.insightgis.com.au/web/stuff/update_qry_builder.gif

This works fine, when an update is posted to the db it will write the
current SYSDATE to the timestamp column. However, the currently
displayed record on my form doesn't know about the new timestamp value,
therefore if you make a subsequent edit to that same record and try to
post changes to the db you get a concurrency violation error.

Question is, when the dataAdapter makes the update to the db, can it
refresh the local copy of the timestamp with the new value? Or do I
have to fish out the whole record from the db again? One of my tables
has 160 fields, so I'd rather not have to fish the whole record out
again for obvious reasons

How do you Oracle people get around this problem?

Cheers,
PeterZ

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

I will have to look at it, but I believe there is a way to take the updated
value and set it as current value. If so, that will solve your issue. This
is not an Oracle problem as much as a disconnected data problem. Look at
Dino Esposito's columns in MSDN magazine (also on the MSDN website). He
usually has some great tidbits for these types of problems.


NOTE:
SYSDATE = GETDATE() in SQL Server and DateTime.Now() in .NET

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************

 

[Paul Clement]

¤ To all you Oracle gurus out there,
¤
¤ I'm updating an Oracle table with an OleDbDataAdapter (win forms).
¤
¤ I'm using a timestamp column rather than comparing all the values to
¤ determine concurrency violation. The way I set the timestamp column is
¤ within the actual dataAdapter update/insert statement.
¤
¤ SYS_TIMESTAMP = SYSDATE ..... equivalent of Now() in SQL I believe?
¤
¤ Screenshot of update query builder:
¤ http://www.insightgis.com.au/web/stuff/update_qry_builder.gif
¤
¤ This works fine, when an update is posted to the db it will write the
¤ current SYSDATE to the timestamp column. However, the currently
¤ displayed record on my form doesn't know about the new timestamp value,
¤ therefore if you make a subsequent edit to that same record and try to
¤ post changes to the db you get a concurrency violation error.
¤
¤ Question is, when the dataAdapter makes the update to the db, can it
¤ refresh the local copy of the timestamp with the new value? Or do I
¤ have to fish out the whole record from the db again? One of my tables
¤ has 160 fields, so I'd rather not have to fish the whole record out
¤ again for obvious reasons
¤
¤ How do you Oracle people get around this problem?

Other than generating the value on the client before performing the update, or returning it as an
output parameter from an Oracle stored procedure, I'm not aware of a direct solution.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[PeterZ]

I will have to look at it, but I believe there is a way to take the updated
value and set it as current value. If so, that will solve your issue.

If I can get something like that going then that would be great, the
least amount of client side logic the better. These sort of things have
a tendancy to become complicated very quickly, for example when an
update is run from a bound dataGrid. Just imagine trying to capture
every cell edit event and manually setting the SYSDATE in the timestamp
column!! Has to be a better way.

Thanks again,
PeterZ

 

[PeterZ]

 

[PeterZ]

I came across this on MSDN regarding concurency:

http://msdn.microsoft.com/library/d...skPerformingOptimisticConcurrencyChecking.asp

Toward the bottom of the article they have an example of an UPDATE sql
with a combined re-select, which is supposed to retrieve the new values
back into the dataset. I tried typing this in the query builder but
when i pressed OK it told me "unable to parse query text".

Screenshot:
http://www.insightgis.com.au/web/stuff/update_qry_builder_2.gif

I then pressed "yes" to preserve recent changes, it seemed to be happy
with that, however when I ran the app and tried to post an update to the
db it threw an exception "ORA-00911: invalid character".

I think my goose is cooked unless anyone has other suggestions.....
:-/


PS
Just learned that the dataAdapter configuration wizard has an option to
generate this 'refresh' type of sql automatically when you press the
Advanced button. Another dead end - the "Refresh the DataSet" option is
greyed out on mine.... any ideas as to why, is it only available with
the sql server data providers?

 

[PeterZ]

This seems to do the trick - subscribe to the DataAdapter's RowUpdated
event and in there suck back the timestamp value that was generated by
the db. This will work on any auto-generated values such as IDs, etc.


Declate the event:
-------------------
myDataAdapter.RowUpdated += new
OleDbRowUpdatedEventHandler(myDataAdapter_RowUpdated);


Handler:
---------
private void myDataAdapter_RowUpdated(object sender,
OleDbRowUpdatedEventArgs e)
{
if (e.StatementType == StatementType.Update ||
e.StatementType == StatementType.Insert)
{
// Retrieve the auto-generated timestamp value back
// to the current dataSet.
string sID = e.Row["MY_PRIMARY_KEY_COLUMN"].ToString();
OleDbCommand cmd = new OleDbCommand(
"select MY_TIMESTAMP_COLUMN from "
+ " MY_TABLE where MY_PRIMARY_KEY_COLUMN = " + sID,
this.dbConnection);

// Now set the local version of MY_TIMESTAMP_COLUMN to be
// the same as in the db.
e.Row["MY_TIMESTAMP_COLUMN"] = cmd.ExecuteScalar();

// You should commit this change otherwise the dataset
// will think you made changes to the record.
e.Row.AcceptChanges();
}
}