CachedUpdates have stopped working after moving to ODAC

[nasirnoor]

I am using Delphi 5 w/ ODAC NET v5.70.0.28. I am moving an application off BDE.

These are the steps I have followed:
TOraSession replaced TDatabase
TQuery -> TOraQuery
TUpdateSQL -> TOraUpdateSQL

For the most part, the migration has been a success. All data access components work fine. But, the updateSQL based cachedUpdates mechanism doesn't work anymore!

The query object has following properties set:
cachedUpdates := TRUE, and
updateObject := updateSQLAQuery

Please note that in order to minimize our migration efforts, we kept updateSQL objects around (only changed their declaration from TUpdateSQL to TOraUpdateSQL). Also, we did not put anything in the SQLUpdate, SQLInsert, and SQLDelete properties of the TOraQuery object. By the way, DeleteSQL, InsertSQL, and ModifySQL properties of TOraUpdateSQL object do have SQL text in them (carried over from the old TUpdateSQL objects).

Following code is attached to AfterPost event of the Query object.

procedure AQueryAfterPost(DataSet: TDataSet):
begin
if TOraDataSet(DataSet).updatesPending then
updateSQLAQuery.apply(ukModify);
end;

This code used to run fine under BDE. But under ODAC, I get the following exception and database is not updated either. Project app.exe raised exception class EDatabaseError with message 'Update failed. Found 0 records.'. I stepped through the code and the "updateSQLAQuery.apply(ukModify);" line is executed (which then raised above mentioned exception). It means that "TOraDataSet(DataSet).updatesPending" is returning TRUE, which means that it is correctly detecting the presence of unapplied changes. The next statement (i.e. apply), however, fails with "Found 0 records" exception...

By the way, if updateSQL objects are not properly supported under ODAC, and there is another better way to handle cachedUpdates under ODAC, I'll be interested in hearing about that too.

Please help! Thanks.

[Plash]

This exception occurs when UPDATE statement that is executed but no records are updated in the database. This situation can be caused by incorrect parameters values in WHERE clause of UPDATE statement.

Try if the error occurs if you call ApplyUpdates method of TOraQuery instead of Apply method of TOraUpdateSQL:

Code: Select all

  AQuery.ApplyUpdates;
  AQuery.CommitUpdates; // this call is need to clear cached updates buffer

TOraUpdateSQL component is required only for backward compatibility with BDE. Normally you use SQLInsert, SQLUpdate, and SQLDelete properties of TOraQuery instead of TOraUpdateSQL.

SQL statements from TOraUpdateSQL or SQLInsert, SQLUpdate, and SQLDelete properties of TOraQuery used even with CachedUpdates = False. So you don't need to use CachedUpdates if you apply updates after each post.

[nasirnoor]

Plash, thanks for your help, but unfortunately it didn't work.

Following is what I have tried so far:

1. As you suggested, used ApplyUpdates and CommitUpdates on AQuery, same error message (Found 0 records). SQLUpdate was left untouched (i.e. blank).
2. Repeated #1 but this time transfered ModifySQL from updateSQL object to the AQuery object's SQLUpdate. Same error message.
3. Repeated #2 but this time generated ModifySQL in updateSQL all over again using "oraUpdateSQL Editor" (it gave me same SQL as was there before but I had to try it just to cover all basis). Same error message.

I think I have tried all possible combinations, and now I am stuck. BDE works fine with this setup, and has been working for years. This is quite large and mature application.

Just as extra bit of information: we query the data using query object (ToraQuery), and then link that query directly to data aware edit controls (TDBEdit, etc) using data source (ToraDataSource). All user edits are performed directly on the result set contained in query object.

Anyways, I realy need this record update mechanism to work. Please help! At this time, I am open to any suggestions.

thanks.

[nasirnoor]

Bugs in ODAC CachedUpdates?

OK, I was able to solve the problem, and it points to the possibility of a couple of bugs in ODAC.

#1

If you generate update SQL using built-in SQL editor, it references "old values" in the result set using the OLD_ prefix. The where clause of the (automatically generated) update SQL is like

Where RECORD_ID = :OLD_RECORD_ID

The idea is that parameter values will be read from the query object, and OLD_ prefix will force reading of the "old value" of the field. The problem is that I also have a field in the table called OLD_RECORD_ID! It confuses ODAC's update mechanism. It interprets OLD_RECORD_ID to literally go after the OLD_REOCRD_ID field, and of course, the update fails.

I had to modify the update query's SQL to remove OLD_ prefix. In this particular case, it doesn't matter, but if I realy need to use the old field value of RECORD_ID, I will be in trouble!

It should not break when the automitically generated "code name" for a field's old value matches a field name in the table. I noticed that the mechanism to use OLD_ prefix to reference "old value" of a field works though--as long as the automtically generated OLD_ code name doesn't match a field name in the result set. For example, the test case of where record_id_test = :old_record_id_test worked fine.

Your oraSQLEditor works fine as well. I generated SQL by selecting the field OLD_RECORD_ID as the key field, and I saw following in the where claus of resulting update SQL:

OLD_RECORD_ID = :OLD_OLD_RECORD_ID

Please note that it worked fine under BDE.

#2

You may suggest that I don't need to put anything in the SQLUpdate property, and that I should let ODAC generate the SQL on the fly. Even though it doesn't work for me (I don't want every field to be updated enmass), but I tried that too and got the error message "Exception class EOraError with message ORA-01009: missing mandatory parameter'. So, I was stuck anyways...

By the way, After I got around the OLD_ issue (as described in #1 above), it didn't matter whether I used udpateSQL object or not, it worked.

If these bugs have been fixed in a subsequent version, please let me know.

thx

[Plash]

This is a known problem of ODAC. It is hardly to fix it because it can break compatibility with old ODAC versions. We'll consider possibility to fix the problem in one of the future ODAC versions.