Getting out of transaction if exception occurred

[gmb]

Hi there
Using Zeos 6.6.6-stable with Delphi6 on Postgres 8.3
I'm having difficulty implementing transactions in my application in the way that I feel will be most practical.
My app is running lengthy DB processes which I'd like to put in transactions; I've implemented it like this:

ExecSQL( 'begin;' ); //(function executing scripts using TZSQLProcessor)
try
//...lengthy process start
ExecSQL(.... )
//...lengthy process ends
ExecSQL('commit');
except
ExecSQL('rollback');
raise; // exception should occur
end;

My requirement is this when an exception occurs:
1 ) the exception (normally EZSQLException) should be raised
2 ) all DB changes made in this transaction should be rolled back
3) the DB session should be able to continue processing transactions

My problem is that no matter how I try to tweak the code above, when a EZSQLException occurs,
the system excepts with the standard:
"SQL Error: ERROR: current transaction is aborted, commands ignored until end of transaction block."
and the connection to the DB should be restarted.

Is there any way in which I can get past this?
Thanks for any help

[Wild_Pointer]

Hello gmb,

have you turned AutoCommit off and set TransactIsolationLevel to ilNone ?

In my projects I use the same approach (managing transactions manually). The difference is I use 'START TRANSACTION ISOLATION LEVEL READ COMMITTED;' to start the transaction.

Good luck!

[gmb]

Thanks for the response, Wild_Pointer (and sorry for my late reply)

I've read some articles about AutoCommit, but cannot quite understand what effect it will have on my code.
As far as I understand, setting this to TRUE will mean that each statement is committed automatically, which negates my goal of running statements in transactions... possibly I'm not understanding it correctly.

Anyway, I'll try your suggestion re IsolationLevel.
Is it possible for you to give a brief explanation of what I'll be doing by setting this?

Regards

[Wild_Pointer]

hello gmb,

You are right - AutoCommit generates transaction for every statement, so transaction control is not possible. You should turn it off. You can add SQL monitor component to see what is being done.

Setting isolation level to "Read committed" ensures that the changes made in this transaction are visible in it only until it is committed. Please see http://www.postgresql.org/docs/8.1/stat ... n-iso.html It will explain the subject better than me

Good luck!

[ribut]

When I use zeoslib-7.2-r3929-testing together with the PostgreSQL database when using the code block

try
  BEGIN
except
ROLLBACK
  end;
COMMIT

rollback implemented and no problems error

When I use zeoslib-7.2-r3986-testing

try
  BEGIN
except
ROLLBACK
  end;
COMMIT

I get an error: commands ignored until end of transaction block

[marsupilami]

This is a copy of http://zeoslib.sourceforge.net/viewtopi ... 20&t=67078. Since it is old, I will lock it.

Jan