Hello
All tables in my database have a column called ID with primary key on it and I need to use that column instead of ROWID.
Some views with joined tables include that column and I'd like to be able to their refresh records without ROWID.
Most of my DataSet queries look like this:
SELECT * FROM TABLE/VIEW;
I successfully overruled Insert, Update and Delete actions in OnApplyRecord, but I cannot figure out how to properly handle the rest.
Here is what I'm doing:
case Action of
'L':
begin
S := 'SELECT * FROM ' + Sender.GetUpdatingTable + ' WHERE ID = :OLD_ID FOR UPDATE NOWAIT';
DMLQuery.SQL.Text := S;
Sender.DeclareQueryVariables(DMLQuery);
Sender.SetQueryVariables(DMLQuery);
DMLQuery.Execute;
Applied := True;
end;
'C':
begin
S := 'SELECT * FROM ' + Sender.GetUpdatingTable + ' WHERE ID = :OLD_ID';
DMLQuery.SQL.Text := S;
Sender.DeclareQueryVariables(DMLQuery);
Sender.SetQueryVariables(DMLQuery);
DMLQuery.Execute;
if not Sender.CompareQueryVariables(DMLQuery) then
raise Exception.Create('Record has been modified by another user');
Applied := True;
end;
'R':
begin
S := 'SELECT * FROM ' + Sender.GetUpdatingTable + ' WHERE ID = :ID';
DMLQuery.SQL.Text := S;
Sender.DeclareQueryVariables(DMLQuery);
Sender.SetQueryVariables(DMLQuery);
DMLQuery.Execute;
Sender.GetQueryVariables(DMLQuery);
Applied := True;
end;
Refresh doesn't work because the code declares and sets only the 'ID' variable. After query execution GetQueryVariables will apply only the value of 'ID' variable.
Check doesn't work either, because it compares variables - not common fields.
If I want to refresh and check common fields then should I iterate over FieldNames and set(or compare) the values manually? Or is this a wrong approach to the solution? (disregarding the fact that life would be easier if I simply used RowId but it's not possible for me at the moment..)
