Dataset.Refresh and BookmarkValid problem

[louis]

Hello,
How I notify to all user in the network that a record is deleted by another user?

Before to modify a record i need to verify if thar record still exists then with this procedure I try to avoid that an user try modify the record deleted by another user in the network:

Code: Select all

procedure TMyDataSet.BeforeEdit(Dataset: TDataset);
const
  ErrMsg = 'The record was deleted by another user.';
var
  BM: TBookmark;
begin
  BM := DataSet.GetBookmark;
  DataSet.Refresh;
  if DataSet.BookmarkValid(BM) then
  begin
    DataSet.GotoBookmark(BM);
    DataSet.FreeBookmark(BM);
  end
  else raise Exception.Create(ErrMsg);
end;

But this procedure not work correctly because if the record deleted by another user is previous of current record Dataset.BookmarkValid(BM) is ever false and block editing of current record. If the record deleted by anhoter user is subsequent of current record Dataset.BookmarkValid(BM) is ever true and not cause problem.

What modify i need made to works fine?

Thanks

[aehimself]

As far as I remember all bookmarks become invalid if you close and reopen (refresh) a dataset.
You can solve what you are looking for is taking the value of all index fields prior and use TDataSet.Locate on the same fields after the refresh.

P.s.: depending on your Delphi version FreeBookMark is no longer needed as it's a TBytes array and gets cleaned up automatically. If it's an older version, you should wrap it with a Try...Finally block to avoid memory leaks.

[marsupilami]

As far as I remember all bookmarks become invalid if you close and reopen (refresh) a dataset.
You can solve what you are looking for is taking the value of all index fields prior and use TDataSet.Locate on the same fields after the refresh.

This is correct. Bookmarks are only meant to be used while a dataset is open. After closing a dataset, they become invalid. Also there is no link between a specific record in the database and the book mark in a dataset. The same record may get assigned a different bookmark each time.

A note on the use of "Refresh": As far as I know it only refreshes the current record.

Regading updates of deleted records: I may be wrong, but Zeos checks the number of affected records after an update gets sent to the database. So if you update a record that was already deleted you should get the infamous "0 record(s) updated. Only one record should have been updated." error.

[louis]

A note on the use of "Refresh": As far as I know it only refreshes the current record.

Refresh refreshes whole table.

You can solve what you are looking for is taking the value of all index fields prior and use TDataSet.Locate on the same fields after the refresh.

Unfortunately I'm in a Delphi DBAction and i don't know the name of table to extract their primary key.

Bookmarks are not safe for my needs but RDB$DB_KEY seems safe to me:
It is persistent in the tables until backup/restore and is unique in the table. Sure, it can be reused after the garbage collector cleans up the data, but when i need DB_KEY there is ever a live transaction that the GC can't clean up then:

Before modifying a record I can try to locate a record that contains an RDB$DB_KEY to see if it still exists.

Now do I have to edit all queries by adding ", RDB$.DB_KEY" or is there a function in Zeos that returns the DBKey of each record like the "RecNo" tables in Paradox?

Thanks

[marsupilami]

Bookmarks are not safe for my needs but RDB$DB_KEY seems safe to me:
It is persistent in the tables until backup/restore and is unique in the table. Sure, it can be reused after the garbage collector cleans up the data, but when i need DB_KEY there is ever a live transaction that the GC can't clean up then:

Before modifying a record I can try to locate a record that contains an RDB$DB_KEY to see if it still exists.

You might want to be careful. This is what IBPhoenix has to say about the life time of RDB$DB_KEY:

The duration of db_keys is a point that's not very clear in the manuals unless you read with sharp eye the API Reference Guide. By default, a db_key is valid only for the duration of the current transaction. After you use Commit or Rollback, the db_key values you had are inaccurate. If you are using auto-commit, you still can use db_keys because this mode uses an implicit CommitRetaining, that means the transaction context is retained, so garbage collection cannot proceed and hence, the db_keys used remain valid until you do a "hard" Commit.

So - if you see changes to the database, you also might see the RDB$DB_KEY value change if a record changes - because the adddress in the database changes.

Now do I have to edit all queries by adding ", RDB$.DB_KEY" or is there a function in Zeos that returns the DBKey of each record like the "RecNo" tables in Paradox?

There is no magic bullet here. Zeos doesn't automagically extend your query to include RDB$DB_KEY. Even if it did, there would be ambiguities as soon as you do a join. So if you want to use RDB$DB_KEY, you will have to extend your queries.
Another option might be to determine the primary key for the data you have. TZQuery.DbcResultSet.GetMetadata.GetTableName() will help you in identifying the table(s) for your current query. Use TZSQLMetadata to fetch the primary key of that table. Then use that information for checking if the record still exists.

Something like this can get you started:

Code: Select all

uses ZAbstractRODataset, ZDBCIntfs;

function GetTableName(Query: TZAbstractRODataset): String;
var
  MD: IZResultSetMetadata;
  TableName: String;
  x: Integer;
begin
  MD := Query.DbcResultSet.GetMetadata;
  TableName := '';
  for x := FirstDbcIndex to MD.GetColumnCount - FirstDbcIndex do begin
    if TableName = '' then begin; //no table name found yet
      TableName := MD.GetTableName(x);
    end else begin // table name found. Make sure, it is only one table.
      if (TableName <> MD.GetTableName(x)) and (MD.GetTableName(x) <> '') then
        raise Exception.Create('Cannot determine primary key. More than one table name found in Query: ' + TableName + ' and ' + MD.GetTableName(x));
    end;
  end;
end;