Thread: Meaning of Level of rollback on errors

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