Error Handling in Sybase

Error Handling in Sybase

1st solution.

You can't catch an exception this way on Sybase. Before you update you have to check data:

if not exists
(
  select 1 from table2
  where id = 1
)
begin
  update table2
  set id = 1 
  where id = 30
end
else
begin
  print 'rolled back'
  rollback
end

2nd solution.

You can also put an update command to procedure, then you can catch an exception.
Create procedure:

create procedure myproc
as
begin
  update table2
  set id = 1 
  where id = 30
end

and run it as below:

begin tran

update table1
set name = 'new name'
where name = 'old name'

exec myproc

IF @@error = 0
begin
    print 'commited'
    commit
end
else
begin
    print 'rolled back'
    rollback
end

Handling error from one stored procedure into another in Sybase ASE 15.0

Many "sp_" stored procedures set a nonzero return code when something goes wrong. Usually it is better to handle this return code than trying to catch errors raised inside the stored procedure. IIRC, this catching would not be possible with Transact-SQL; a 3rd generation language such as C would be required.

To get the return code of myproc stored procedure into variable @myvar, the syntax is

exec @myvar = myproc [arguments] 

A simple example with sp_password:

declare @spreturn int  
exec @spreturn = sp_password 'notmyoldpw', 'notmynewpw'  
print "Return from sp_password is %1!", @spreturn  
go

Server Message:  Number  10315, Severity  14  
Server 'SDSTRHA01_SY01', Procedure 'sp_password', Line 148:  
Invalid caller's password specified, password left unchanged.  
Server Message:  Number  17720, Severity  16  
Server 'SDSTRHA01_SY01', Procedure 'sp_password', Line 158:  
Error:  Unable to set the Password.  
(1 row affected)  
Return from sp_password is 1  
(return status = 1)  

The int variable @spreturn defined in the first line got sp_password return code, whose value was one as shown by (return status = 1) in the last message line. The reason why it was not zero is clear: there were two errors inside sp_password, 10315 and 17720. The point is to focus in this nonzero return code and ignore 10315 and 17720. In your stored proc, @spreturn ought to be checked against zero. If zero it ran OK, else something failed.

How to handle a transaction in Sybase ASE?

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.

Create Sybase function with exception handling

After a long investigation and lot of efforts I've found my solution:

CREATE FUNCTION MY_FUNCTION(@date CHAR(20))
  RETURNS DATE
AS
BEGIN
RETURN
(CASE
  WHEN ISDATE(@date) = 0
  THEN NULL
  ELSE CAST(@date AS DATE)
END)
END

Sybase method ISDATE() doing all magic in this case without throwing exception...

Get System Error Message In Sybase

I don't know how to take specify error message - I think it's not possible. Maybe below query will cover your needs. It return and pattern message for example Must declare variable '%.*s'. insetad of Must declare variable 'fake variable'.

SELECT description 
from master..sysmessages where error = @@error

@@error variable change every time you make an operation so you need to use local variable for example @err. In your code should be like this.

declare @err int,
        @msg varchar(255)

Insert into A
Select top 250 id from C
inner join D
on c.id = D. id

select @err = @@error

IF (@err != 0)
BEGIN
    SELECT @p_err_code = 1
    SELECT @p_err_desc = "Error while inserting records into #PAR_PROVIDERS."

    SELECT @msg = description 
    from master..sysmessages where error = @err

    DROP TABLE #PAR_PROVIDERS
    RETURN 1
END

---

Related Topics