mORMot Open Source

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 392

can not insert a batch in postgres

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

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,539

Website

Re: can not insert a batch in postgres

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

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 392

Re: can not insert a batch in postgres

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