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

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?

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,527
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)

Offline

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

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

Offline

Board footer

Powered by FluxBB