One of our customers is running a VFP app and an sql-based app (using your driver) on the same dbf files concurrently.
The VFP app is a legacy app that cannot be changed easily.
The sql-app is Servoy which generates sql in a general way and can only create standard sql.
We are now having an issue when the VFP app inserts a new record and then deletes the record (marks for deletion).
The sql-app creates a new pk by issuing a select-max(pk) on the table.
The insert from the sql-app fails because the select-max does not see the deleted PK, but it also cannot insert the PK (duplicate key conflict).
My question is, is it possible for the driver to handle this?
Could your driver change a insert of a PK of a marked-for-deletion record to an update/undelete of that record?
It is not possible for the Servoy sql generation to handle this because the sql would be very driver-specific.
Thanks in advance.
You should use deletesAreVisible=true connection property,
deletesAreVisible: Indicates whether DBF's resultSet includes deleted records.