Re: Meaning of Level of rollback on errors

[Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp>]

Renato Gondim wrote:
> Hi folks,
>
> I've just faced a situation here at work. Our application runs on Oracle,
> SqlServer, Sybase and PostGreSql. The problem was when a primary key
> violation occurs: after the error, the application tries to find the
> register, but if the ODBC is configured as "Transaction" on "Level of
> rollback on errors", nothing is returned from the select. After a research,
> I found out that I must use "Statement" , but I'd like to know what happen
> when this configuration is set, because the application must start / finish
> the transaction. I'm tried "Nop: don't rollback anything", cause make more
> sense for me: don't do anything, let the application control the
> transactions, but using it, the application can't continue and make more
> statements.

Level of rollback on errors.
1. Transaction
   Rollback the current transaction entirely on errors. This was the unique
   behavior of old drivers becauase PG has no savepoint functionality until
   8.0.

2. Statement
   Rollback the current (ODBC) statement on errors (in case of 8.0 or later
   version servers). The driver calls a SAVEPOINT command just before starting
   each (ODBC) statement and automatically ROLLBACK to the savepoint on errors
   or RELEASE it on success. If you expect Oracle-like automatic per statement
   rollback, please use this level.

3. Nop
   You can(have to) call some SAVEPOINT commands and rollback to a savepoint
   on errors by yourself. Please note you have to rollback the current
   transcation or ROLLBACK to a savepoint on errors (by yourself) to continue
   the application.

regards,
Hiroshi Inoue