Unidac - Batch - Execption

[AlainANTOCI]

Hi,

I use Unidac with postgres and batch transcation all work fine i've a problem with code below :

if NbParam = MAX_BATCH then
begin
try
InsertQuery.Execute(MAX_BATCH);
except on E: Exception do
WriteLog(Format('Doublon dans la table P_MESURES_STEP (%s)',[E.Message]));
end;

NbParam := 0;
end;

All work fine but after the exception (Duplicate Key) all next transcations don't work with message :

"the transaction is canceled, the commands are ignored until the end of the transaction block"

in French "la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction"

I think i need to intialize something after the exception is catch.

Regards

ANTOCI Alain

[evgeniym]

Hi Alain!
Thank you for contacting Devart!
Obviously, before executing the code you specified, the transaction has already been started.
In this case, when an exception occurs, you need to end the transaction by explicitly calling Rollback or Commit.
Alternatively, you can implement handling of such situations in your application code using SavePoints.
What you may be looking for is described here:
https://www.postgresql.org/docs/current ... point.html
In short: creating a savepoint, executing a query and - in case the query fails - rolling back to the savepoint created previously restores the transaction state.
In your case, you should create the SAVEPOINT before executing the INSERT query, and when it fails - ROLLBACK to the savepoint and continue normally.
The use of Save Points in UniDAC is described in the documentation at the link:
https://www.devart.com/unidac/docs/deva ... tring).htm
Should you have any questions, do not hesitate to ask!
Regards,
Evgeniy