memberexists does not help

dcoun · 2022-10-20 18:32:58

var b:boolean;
      o:TOrmamyclass;
......
 if srv.Orm.MemberExists(TOrmamyclass,o.IDValue) then b:=srv.Orm.Update(o) else b:=srv.Orm.Add(o,true,true)=o.IDValue;

The above does not work all the times, I am getting:

Exception: 20221020 16294225  " EXC   EZSQLException {Message:"SQL Error: Duplicate entry '1611284410' for key 'PRIMARY' \r\nCode: 1062 SQL: insert into amyclass (bid,xml,stat) values (?,?,?)"} [Pool7-TEpresslave] at 0235e59a ZDbcMySql.pas TZMySQLConnection.HandleErrorOrWarning (1271)  ZDbcMySql.pas TZMySQLConnection.HandleErrorOrWarning (1271) ZDbcMySqlStatement.pas TZAbstractMySQLPreparedStatement.ExecuteUpdatePrepared (1150) mormot.db.sql.zeos.pas TSqlDBZeosStatement.ExecutePrepared (1204) mormot.orm.sql.pas TRestStorageExternal.ExecuteFromJson (2096) mormot.orm.sql.pas TRestStorageExternal.EngineAdd (1249) mormot.orm.rest.pas TRestOrm.Add (1725) .........

There are not many threads that try to write the same thing
Without ORM I used to have a REPLACE sql command. Now what to do?
After the above, everything goes out of control with "Commands out of sync" and that generally happens if such an error (duplicate primary key or field out of range) happens
Delphi 11.2 win32

ab · 2022-10-21 07:23:55

You could either call srv.Orm.WriteLock/WriteUnlock or use a batch with the boInsertOrReplace option.

dcoun · 2022-10-21 07:30:55

Thank you @ab. Please try to have a replace function as we have add/update for ORM

An other question:
If I use a batch, how can I add to the batch a sql command for multiple rows like the following:

update table set act=0

Thank you in advance

dcoun · 2022-10-21 07:35:28

Reading the following response from overflow:

REPLACE INTO table(column_list) VALUES(value_list);
is a shorter form of
INSERT OR REPLACE INTO table(column_list) VALUES(value_list);
For REPLACE to execute correctly your table structure must have unique rows, whether a simple primary key or a unique index.
REPLACE deletes, then INSERTs the record and will cause an INSERT Trigger to execute if you have them setup. If you have a trigger on INSERT, you may encounter issues.
This is a work around.. not checked the speed..
INSERT OR IGNORE INTO table (column_list) VALUES(value_list);
followed by
UPDATE table SET field=value,field2=value WHERE uniqueid='uniquevalue'
This method allows a replace to occur without causing a trigger.

Probably an insertOrIgnore (or an extra boolean flag in add to add 'or ignore') is better for this case and it is supported by sqlite

Last edited by dcoun (2022-10-21 07:38:35)

ab · 2022-10-21 08:13:07

The batch does not support such full update.
This is a statement which may be very slow on a big database. So the idea is to use IRestOrm.UpdateField with WriteLock/WriteUnLock if you want to do something else atomically at the same time.

I will look into adding IRestOrm.Replace but it should work also on databases which have no REPLACE process.
There is already IRestOrm.AddOrUpdate() but it generates a new ID - which is not what you need.

dcoun · 2022-10-21 08:14:54

Thank you a lot @ab
check also the above discussion
Probably adding ignore to INSERT is a better approach

dcoun · 2022-10-21 09:21:33

From a small search, INSERT IGNORE is supported by SQLITE, postgres, Oracle, mysql, mariadb,firebird
SQLITE,mysql : INSERT IGNORE
Oracle: INSERT .... IGNORE_ROW_ON_DUPKEY_INDEX
Firebird: UPDATE OR INSERT

For Oracle there is enough info:
https://blogs.oracle.com/sql/post/how-t … e-database
http://guyharrison.squarespace.com/blog … -hint.html (old article)

ab · 2022-10-21 10:38:55

Note that insert/or/ignore is not exactly the same as update/or/insert.

Take a look at EncodeInsertPrefix() in mormot.db.core.pas.
We did not include Oracle yet because it is very complicated to get it working properly. As often with Oracle.
And PostgreSQL also expect a complex syntax with the column names to ignore, as On CONFLICT(column1, ....) DO NOTHING;.

dcoun · 2022-10-21 10:53:32

ab wrote:

Note that insert/or/ignore is not exactly the same as update/or/insert.
Take a look at EncodeInsertPrefix() in mormot.db.core.pas.
We did not include Oracle yet because it is very complicated to get it working properly. As often with Oracle.
And PostgreSQL also expect a complex syntax with the column names to ignore, as On CONFLICT(column1, ....) DO NOTHING;.

I am not familiar with Oracle and Firebird
Firebird, Oracle, Postgres do not have an 'INSERT [OR] IGNORE' dialect

For Oracle I have included the info

For firebird >2.1 is just a little more complex (you have to provide the unique indexes) as described here:
https://firebirdsql.org/refdocs/langref … nsert.html

For postgres >9.5 nearly the same with firebird
INSERT INTO table(column1, column2, ...)
VALUES
(value1, value2, ....),
(value1, value2, ....)
On CONFLICT[(column1, ....)] DO NOTHING;
https://www.postgresql.org/docs/10/sql-insert.html

Last edited by dcoun (2022-10-21 10:56:39)

mORMot Open Source

#1 2022-10-20 18:32:58

memberexists does not help

#2 2022-10-21 07:23:55

Re: memberexists does not help

#3 2022-10-21 07:30:55

Re: memberexists does not help

#4 2022-10-21 07:35:28

Re: memberexists does not help

#5 2022-10-21 08:13:07

Re: memberexists does not help

#6 2022-10-21 08:14:54

Re: memberexists does not help

#7 2022-10-21 09:21:33

Re: memberexists does not help

#8 2022-10-21 10:38:55

Re: memberexists does not help

#9 2022-10-21 10:53:32

Re: memberexists does not help

Board footer