generated update command

[Ludek]

Hi,
the automatically generated update command (when applying changes in toraquery to database) contains only the key fields in where clause. Is there a way to make it containg all possible fields (all keys + values)?
or, do you have a proposal, how to detect changes from other users in odac-based application? (when cached updates are active)
Thanks, Ludek.

[bork]

Hello

Please specify where do you need to detect that record has been changed: when calling the Edit method or when calling the ApplyChanges method?

In any case you can define update query manually by defining the SQLUpdate property and specifying all required fields in the WHERE statement.

Also if you want to add all fields to the automatically generated WHERE statement, you can add all fields to the KeyFields property and all fields from this property will be added to the WHERE statement.

[Ludek]

Hello

Please specify where do you need to detect that record has been changed: when calling the Edit method or when calling the ApplyChanges method?

I mean ApplyUpdates = posting the cached changes to database.

In any case you can define update query manually by defining the SQLUpdate property and specifying all required fields in the WHERE statement.

Yes, i know. But doing this for each toraquery and especially updating it paralelly to each change in the toraquery.fields is somehow... you know

Also if you want to add all fields to the automatically generated WHERE statement, you can add all fields to the KeyFields property and all fields from this property will be added to the WHERE statement.

not good. the exactly set keyfields are useful for other things like sequences and perhaps some other ones, that i don't know yet

[bork]

If you need to detect record changes on ApplyChanges then try to set the LockMode property of TOraQuery to the lmLockDelayed or lmLockImmediate value.

[Ludek]

I tried it, both of them. It didn't change anything. a had a record, with key and a value, two instances of an application, both on the same record. first app called edit and changed data, second app called edit and changed data, first app called post and applyupdates, second app called post and applyupdates. the second app overwrited silently the change of first app and the user did not see anything i even haven't seen any attempt to lock the record in sql monitor... (and i really don't want to lock anything, i would just like to DETECT the change from the first app, cancel all updates, refresh query and inform the second user about it - he has to make his changes once more)

you are still writing about applychanges - i can't see any applychanges method at toraquery - is it only misspelled or is it some very hidden special feature?

[bork]

We reproduced your issue when lock mode is ignored in the cached updates mode. Now we are investigating the possibility to change lock mode behavior for the cached updates mode. We will notify you as soon as we have any result.

I meant "ApplyUpdates" when I wrote "ApplyChanges". Sorry for the misprint.

[Ludek]

ok, i'll wait... thanks!

[bork]

We have fixed this problem. This fix will be included in the next ODAC build.

[Ludek]

again to this topic and update command generation:
is there really no simple way to generate update sql command inclusive all updatable fields in where? it would be really helpful... setting keyfields to true for each field in each query is really very time consuming... one simple option at toraquery level would be nice. or one to-do list, what is to do, if i should do it myself
thanks.

[Ludek]

Hello, is anybody there to help me a little?

[AlexP]

Hello,

You can write your own procedure to fill the KeyFields property with all field names from the query, and call it for each TOraQuery.
For example,

procedure SetKeyFields(var Query: TOraQuery);
var
i: integer;
FldLst: TstringList;
begin
FldLst:= TstringList.Create;
Query.GetFieldNames(FldLst);
Query.KeyFields:='';
for i:=0 to FldLst.Count-1 do
begin
Query.KeyFields:=Query.KeyFields+FldLst.Strings+';';;
end;
FldLst.free;
end;

and call it in the BeforeOpen event for each TOraQuery.

But in this case the speed will be lower than if using only real key fields in the WHERE clause.
Most users don't need this functionality, and we cannot include it in our components.

[Ludek]

Hi, thanks for tip, but this apperently would not work mit calculated or lookup fields in the query...

I you say "Most users don't need this functionality", what other ways of solving concurrent user updates do you recommend? record locking is no option in environment with many users (some user goes for one month holiday with record locked - everything stays), the standard behaviour "last wins" is also far from some optimal solution (problem with application level data constraints - if some user changes field A from 1 to 2 and other user field B from 1 to 2, you get in database A = 2, B = 2, wich could not be allowed, but the application can't catch this, as uses 1 has A = 2, B = 1 and user 2 A = 1, B = 2. with where clause set to all updatatable fields (my wish) i'll get "update failed, 0 rows affected", which is somehow enough - the main thing is, the wrong combination of data won't get written to db)

[AlexP]

Hello,

You can check the FieldKind property in the procedure and add only necessary fields.

procedure SetKeyFields(var Query: TOraQuery);
var
i: integer;
FldLst: TstringList;
begin
FldLst:= TstringList.Create;
Query.GetFieldNames(FldLst);
Query.KeyFields:='';
for i:=0 to FldLst.Count-1 do
begin
if Query.FieldByName(FldLst.Strings).FieldKind = fkData then
Query.KeyFields:=Query.KeyFields+FldLst.Strings+';';
end;
FldLst.free;
end;

Also you can add the GUID field in the tables, and generate it each time before update, and use it as the key field.

[Ludek]

Hi, I programmed following:

Code: Select all

type
  TMyOraSQLGenerator = class(TOraSQLGenerator)
    procedure GenerateConditions(SB: _StringBuilder; const StatementType: TStatementType;
      const ModifiedFieldsOnly: boolean;
      const KeyAndDataFields: TKeyAndDataFields;
      const Index: integer = -1); override;// Generate WHERE part for UPDATE, DELETE, REFRESH SQLs
  end;

  TMyOraDatasetService = class(TOraDatasetService)
    procedure CreateSQLGenerator; override;
  end;

  TMyOraQuery = class(TOraQuery)
    function GetDataSetServiceClass: TDataSetServiceClass; override;
  end;

{ TMyOraSQLGenerator }

procedure TMyOraSQLGenerator.GenerateConditions(SB: _StringBuilder;
  const StatementType: TStatementType; const ModifiedFieldsOnly: boolean;
  const KeyAndDataFields: TKeyAndDataFields; const Index: integer);
var
  i: integer;
begin
  for i := 0 to High(KeyAndDataFields.DataFieldDescs) do
    if not IsBlobDataType(KeyAndDataFields.DataFieldDescs[i].DataType) then
      AddFieldToCondition(SB, KeyAndDataFields.DataFieldDescs[i], StatementType, ModifiedFieldsOnly, Index);
  if SB.Length = 0 then
    DatabaseError(SNoKeyFields);
end;

{ TMyOraDatasetService }

procedure TMyOraDatasetService.CreateSQLGenerator;
begin
  SetSQLGenerator(TMyOraSQLGenerator.Create(Self));
end;

{ TMyOraQuery }

function TMyOraQuery.GetDataSetServiceClass: TDataSetServiceClass;
begin
  result := TMyOraDatasetService;
end;

Is seems to generate the commands as I need. Do you find it ok, or somehow dangerous?
If ok, it would be nice, if you also include that code in your sources using some options - i mean something like

Code: Select all

if someoptionset then begin
  for i := 0 to High(KeyAndDataFields.DataFieldDescs) do
    if not IsBlobDataType(KeyAndDataFields.DataFieldDescs[i].DataType) then
      AddFieldToCondition(SB, KeyAndDataFields.DataFieldDescs[i], StatementType, ModifiedFieldsOnly, Index);
  if SB.Length = 0 then
    DatabaseError(SNoKeyFields);
end else
  ... {original code}

Thanks, Ludek.

[bork]

Hello

Sorry, but I cannot understand your aim.

Lets see your example:

We have a record: with ID = 1, A = 1, B = 1

Steps:
1. User_1 start editing record with ID = 1
2. User_1 changes field A from 1 to 2 in the DataSet
3. User_2 start editing record with ID = 1
4. User_2 changes field B from 1 to 2 in the DataSet
5. User_1 post record with ID = 1 to database
6. User_2 post record with ID = 1 to database

What behavior do you want to get?

I can suppose the following behavior kinds:

1. Database will contain A = 1, B = 2 (the standard "last wins" behavior). For this case you should set Options.UpdateAllFields to True.
2. Database will contain A = 2, B = 2. It is possible with the default options values. If A = 2 and B = 2 are a prohibited values for the record, you can create Constraint in the database:

Code: Select all

alter table MY_TABLE add constraint CHECK_AB check (A2 or B2)

3. User_2 is not able to start editing Record until User_1 finishes editing. In this case you should set LockMode to lmLockImmediate.
4. User_2 should see all changes in the database before editing and after editing record. In this case you should set the RefreshOptions.roBeforeEdit and RefreshOptions.roAfterUpdate to True

If you want to get a different behaviour, please give a more detailed description of the behaviour you want to get. I took a look at your code, it looks safe but slow, because any update with all fields in the WHERE clause is slower then update with key fields only in the WHERE clause. You can use your code in your application, but to include it to ODAC we should understand the necessity of this code for other users.