mORMot Open Source

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

REPLACE sql statement in REST

when using Batches we can set the 'boInsertOrReplace' to have replaced a row if its primary key is the same with the inserted new row.

How can we have it in ORM/REST add or addorupdate functions of mormot2?

If I understand it correctly, addorupdate is based on IDvalue to issue an add or an update.

How can we have an 'INSERT OR REPLACE' sql statement for ORM/REST in order not to have to check if a record with the same primary key value exists in the table ?

Thank you in advance

Last edited by dcoun (2022-08-08 10:41:26)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: REPLACE sql statement in REST

This is not yet supported - the idea is to make a manual Update then Add if the first failed...

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: REPLACE sql statement in REST

Thanks a lot @ab

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: REPLACE sql statement in REST

Or to make a Retrieve() - which should be very fast if there is a cache enabled for the table - then Update or Add...

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: REPLACE sql statement in REST

This is not yet supported - the idea is to make a manual Update then Add if the first failed...

I tried the above and it does not work
If record is missing, update returns true

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 430

Re: REPLACE sql statement in REST

Trying to debug it, I noticed that in the update sql query no data are expected to be returned back. That is ok
But an update is not returning an error if the updated record does not exist
I am using ZEOS 8.0-patches branch from GitHub

I switched to your second suggestion that it is better I believe too
I issue an OneField requesting the RowID

function OneFieldValue(Table: TOrmClass; const FieldName: RawUtf8; WhereID: TID): RawUtf8;

Last edited by dcoun (2022-08-12 09:36:33)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: REPLACE sql statement in REST

You have IRestOrm.MemberExists() for checking if a ID does exist.