Unable to apply update: "No rows were affected by this update"

Data Abstract
1

Hi,

I recently updated to DA9 and what used to work in an earlier version, doesn’t work anymore.
I’m using Delphi 10 Seattle with DA 9.0.95.1233 Server Edition, and the SQLite UniDac v6.2.10 provider.

I have the following table:

  'CREATE TABLE IF NOT EXISTS `projectsmeta` (' +
  '`key` NVARCHAR(255) NOT NULL ,' +
  '`value` NTEXT NULL ,' +
  '`idproject` NVARCHAR(32) NULL )';

When I change a field on the client, this is working as expected but when I call ApplyUpdates, I see the following dialog:

A problem occured while updating a record in table “projectsmeta”, record ""
No rows were affected by this update

Tracking the problem using the OnBeforeExecuteDeltaCommand, I can see that the generated SQL is the following:

UPDATE projectsmeta SET
"key"= :key,
“value”= :value,
“idproject”= :idproject
WHERE
((“key”=:OLD_key) OR (:OLD_key IS NULL AND “key” IS NULL)) AND
((“value”=:OLD_value) OR (:OLD_value IS NULL AND “value” IS NULL)) AND
((“idproject”=:OLD_idproject) OR (:OLD_idproject IS NULL AND “idproject” IS NULL))

With the following parameters:

Parameter “key” = MetaValid
Parameter “value” = 34958
Parameter “idproject” = FC8DDBC1CC7E49829E7F05A231E2CF0D
Parameter “OLD_key” = MetaValid
Parameter “OLD_value” = 1846597
Parameter “OLD_idproject” = FC8DDBC1CC7E49829E7F05A231E2CF0D

Old version was 8.0.81.1131 and didn’t trigger any dialog in that situation.
Could you explain why this is not working anymore in DA 9 ? Is that because this table doesn’t have a primary key ?
What could I do to avoid this error ?

Thanks.

2

yes, it can be a reason.
You can set PK for this table manually in DASM. don’t forget to update datatable schema on client-side after changing it in DASM.

3

PK fix the problem however, it can’t be used for a relationship table such as:

CREATE TABLE IF NOT EXISTS AssociatedKeywords (
idTopic NVARCHAR(255) NOT NULL ,
idKeyword NVARCHAR(255) NOT NULL);

For such tables I still experience the “No rows were affected by this update” problem.
What would be the solution in that case ?

Thanks.

4

Really every database table should have a primary key!?

5

@mh Is that a question or an affirmation ?
If so what should be the primary key in a relationship table ?

6

in a relationship, the PK should usually cover the both key fields.

7

as mh said, for optimal working you need to have PK for both fields.
the SQL Server: Indexing a link table article recommends: