can not insert a batch in postgres

dcoun · 2023-03-04 12:15:24

Trying to insert a Batch to postgres using TSqlDBPostgresConnectionProperties, mormot2, windows11 Delphi 11.2, win32( from pg9)-win64(15.2) Postgres 15.2

res:=TRestBatch.Create(DBsrv.Orm,TormMobMetav,1000,[boInsertOrReplace,boExtendedJson]);

The SQL query as I can see from mormot.db.sql.postgres, line 730 is:

'replace into pchrdb.m_mdmetav (ID,typ,sid,nam,nam2,nam3,nam4,act,sor) values (unnest(?::int8[]),unnest(?::int8[]),unnest(?::int8[]),unnest(?::text[]),unnest(?::text[]),unnest(?::text[]),unnest(?::text[]),unnest(?::int8[]),unnest(?::int8[]))'

I am getting an exception in mormot.db.raw.postgres.pas line 334.
'ERROR: syntax error at or near "replace"'#$A'LINE 1: replace into pchrdb.m_mdmetav (ID,typ,sid,nam,nam2,nam3,nam4...'#$A' ^'#$A
and an errCode that it must be overflowed
that come in the stack from mormot.db.sql.postgres, line 269

 PQ.Check(fPGConn, 'Prepare',PQ.Prepare(fPGConn, pointer(aName), pointer(aSql), aParamCount, nil));

How to overcome the above? The log is in this gist: https://gist.github.com/dkounal/e394d2a … 5110ea76ee

if the call is repeated, probably due to the previous failure, the prepared statement does not exist in the cache and I am getting:
'ERROR: prepared statement "08" does not exist'#$A
Is it expected to be handled by me or by the framework?

mpv · 2023-03-04 14:09:46

Replace into cause is not applicable for Postgres, this is mysql syntax. Postgrs statement should looks like insert.. on conflict do..
Try to remove boInsertOrReplace option

Last edited by mpv (2023-03-04 14:10:50)

dcoun · 2023-03-04 14:19:46

Coming from the mysql world..... Bang!!! You have right @mpv....
I have to forget the boInsertOrReplace and most of my habits with mormot's Batches....
Thank you a lot

mORMot Open Source

#1 2023-03-04 12:15:24

can not insert a batch in postgres

#2 2023-03-04 14:09:46

Re: can not insert a batch in postgres

#3 2023-03-04 14:19:46

Re: can not insert a batch in postgres

Board footer