I have code similar to below in sybase ASE 12.5.4 having begin-end inside another begin-end. I am using jdbc to execute the below code present in a SP.
Problem is even though the code properly goes to first IF loop (where there is an insert statement), it does not get commit always. This happens randomly and for very few records (99 % records are committed properly. )
I am able to get the proper return value from this SP in my Java code, which I print to logs but when I check database table T1, there is no record inserted.
Why sometimes the transaction is not committed ? Do I have to use explicit commit inside SP ?
declare @abc int
select @abc = Id from T1 where -- some proper where clause
if @@rowcount = 0
insert into T1 -- proper insert statement
select @@identity abc
select @abc Id
There doesn't appear to be any error checking on the insert statement (if @@error ...).
Maybe a deadlock occurred that you silently ignore?
If there is an error on the insert statement, does this code will still return identity value ?
Note that I am getting the identity value which I log into my java code, but when I check the database table there is no record.