How to avoid auto commit with TPgSQL.execute

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jmt
Posts: 2
Joined: Tue 10 Nov 2020 09:55

How to avoid auto commit with TPgSQL.execute

Post by jmt » Tue 10 Nov 2020 10:16

Hi,
I might miss something, but I can't find any option to avoid auto commit when using TPgSQL.execute.

Let assume I have different methods using TPgSQL to update, delete or insert data to different tables and all using the same TPgConnection. And I want the caller of those methods or even the caller of the caller to decide when to commit or rollback the data.

How to achieve that?

I read about the AutoCommit property that is available for TPgQuery but with my small test it looks like even it's set to false, the execute method of TPGQuery does a commit. So, using TPgQuery instead doesn't looks like a workaround.

Appreciacte any advice.
Thanks

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: How to avoid auto commit with TPgSQL.execute

Post by oleg0k » Thu 12 Nov 2020 16:33

Hello,
You should manually start the transaction for TPgConnection: PgConnection.StartTransaction, and then commit or rollback the transaction after performing the necessary operations: PgConnection.Commit or PgConnection.Rollback, respectively. Visit our documentation for more information: https://www.devart.com/pgdac/docs/devar ... tion().htm

wbr, Oleg
Devart Team

jmt
Posts: 2
Joined: Tue 10 Nov 2020 09:55

Re: How to avoid auto commit with TPgSQL.execute

Post by jmt » Thu 19 Nov 2020 12:39

In other words AutoCommit is not working?

But what if a manual start transaction is not an option?

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: How to avoid auto commit with TPgSQL.execute

Post by oleg0k » Mon 23 Nov 2020 18:46

Hello,
Some database systems, e.g., Oracle, allow executing DML statements without a commit statement, but that's not the case for PostgreSQL. We'll consider the possibility of implementing an implicit transaction start when AutoCommit=False. Currently the only way to control transactions in PgDAC is to manually start them with StartTransaction and end with Commmit or Rollback when necessary.

wbr, Oleg
Devart Team

Post Reply