the sql statement is not allowable for a bulk operation

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
plevrier
Posts: 4
Joined: Fri 16 Dec 2016 09:03

the sql statement is not allowable for a bulk operation

Post by plevrier » Wed 30 Jun 2021 15:14

Since the upgrade from 8.3.2 to 8.4.3 I have the following error :

the sql statement is not allowable for a bulk operation

For now I am reversing my upgrade and am back to 8.3.2
Using with postgreSQL. Q1 is a TUniQuery

Code snippet:

Code: Select all

Const
_SQL_XLOG_INSERT  = 'insert into xlog (sensorid, valuestamp, valuetype, valueraw, valuecalc, valuestate) values
   (:PAR0, :PAR1, :PAR2, :PAR3, :PAR4, :PAR5) on conflict do nothing';

Q1.SQL.Text := _SQL_XLOG_INSERT;
Q1.Params[0].DataType := ftInteger ;    // sensorid
Q1.Params[1].DataType := ftDateTime;    // valuestamp
Q1.Params[2].DataType := ftInteger ;    // valuetype
Q1.Params[3].DataType := ftString  ;    // valueraw
Q1.Params[4].DataType := ftString  ;    // valuecalc
Q1.Params[5].DataType := ftInteger ;    // valuestate
Q1.Params.ValueCount := FPrList[Nr].Count;
Cnt := 0;
for I := 0 to FPrList[Nr].Count - 1 do begin
  P := FPrList[Nr][I];
  Q1.Params[0][I].asInteger  := P^.SensorID;
  Q1.Params[1][I].asDateTime := P^.ValueStamp;
  Q1.Params[2][I].asInteger  := Integer(P^.ValueTypeCalc);
  Q1.Params[3][I].asString   := P^.ValueRaw;
  Q1.Params[4][I].asString   := P^.ValueCalc;
  Q1.Params[5][I].asInteger  := Integer(P^.ValueState);
  Cnt := Cnt + 1;
end;
Success := False;
try
  Q1.Execute(Cnt);
  Success := True;
except
end;
Please fix this issue

evgeniym
Devart Team
Posts: 103
Joined: Thu 13 May 2021 07:08

Re: the sql statement is not allowable for a bulk operation

Post by evgeniym » Thu 01 Jul 2021 09:00

Please note that the ON CONFLICT statement is forbidden for BULK operations in UniDAC 8.4.3.
If you remove ON CONFLICT from your statement, batch insert will work correctly.

Post Reply