How to handle a transaction in Sybase ASE?

Question

I have to insert records into a table in a test environment, so that I now know that it will throw a primary key constraint violation. And because of the scripts that will be run independantly by other people once the time to migrate from an environment to another, I wish to make my script rollback whenever it encounters a problem.

My script is as follows:

use my_db
go

--
-- No rows of the given "my_code" must exist, as they shall be replaced.
--
if exists (
        select 1
            from my_table
            where my_code like 'my_code'
) delete from my_table
    where my_code like 'my_code'

--
-- All rows shall be inserted altogether, or rejected altogether at once.
--
begin tran a
    insert into my_table (field1, field2, field3) values (value1, value2, value3)

    if @@error != 0 or @@transtate != 0
        begin
            rollback tran a
        end

    insert into my_table (field1, field2, field3) values (value1, value2, value3)

    if @@error != 0 or @@transtate != 0
        begin
            rollback tran a
        end

    commit tran a
go

I have tried what I could get from these posts:

I have tried with only verifying @@error, @@transtate and both, and I always get the message box reporting the error, and no records are rolled back, that is, the passing rows are still inserted.

I wonder whether there is a way to make sure that Sybase handles the transactions adequately as expected, or else, simply make sure it doesn't autocommit rows when they are inserted as SQL Server allows it - I mean, SQL Server inherit from Sybase, after all... So was it into Sybase, or is it new to SQL Server, I don't know.

I wish to avoid having the error and more preferably log the error and rollback or delete the inserted rows insde the transaction.

Notice that I don't use a stored procedure. This script is a one-timer to update the database for recent changes occured in the software that uses the database.

score 2 · Accepted Answer · edited Aug 04 '14 at 13:55

The code looks mostly correct. If you hit a duplicate-key error upon the insert, then this will be caught by the IF-test.

However, you should also add some logic (GOTO or additional IF-ELSE tests based on a flag) that skips the second insert when you have decided to roll back the first insert.

Currently your code will always execute the second insert, regardless. Unlike some other databases, in ASE control flow is not affected by an error condition and you need to intercept this yourself.

Also note that both inserts are identical, so if there is unique index on the table the second insert will always generate dup-key error if the first insert was successful.

It sounds as if you are using a client that checks the status after every statement or something? ASE itself does not pop up any error message boxes.

To develop this, it is best to run the script from the command line with

isql [...] -i yourscriptfile.sql

Just two remarks:

You are using a transaction name ('a'). This is not necessary and can in fact cause problems: when you roll back to a named transaction, as you do, that must be the outermost transaction or the rollback will fail. Best don't use transaction names.
This problem in the previous bullet can in fact occur if there is already a transaction active when you execute this code. You should always check this at the start of your script with

if @@trancount > 0 begin print 'Error: transaction already active' return end

or something similar.

HTH,

Rob V.

+1 Thanks for this precious information, and for your time. I took into account most of the things stated, and my script finally behaves. =) For the PK violation, this was because of the way the PK constraint was stated. I was obliged to change the constraint to add a new column into it, along with a new constraint. — Will Marcouiller, Aug 04 '14 at 13:48

How to handle a transaction in Sybase ASE?

1 Answers1