Begin Transaction with Try Catch Block

Question

I have scenario where I am calling SQL file from JAVA. The SQL file has Begin Try Catch and checking Transaction State and doing rollback or commit based on any exception. However, if I want to log that rollback and my JAVA application should send some message saying rollback happened and the task did'nt finished. Having said, the logic for transaction rollback/commit needs to be part of SQL File only. Is there any way, I can log the error and throw on JAVA application letting my user know something is wrong ?

seanb seanb · Accepted Answer · 2020-10-06T23:08:10

Since others haven't answered yet - here's a suggestion, with the caveat I don't know Java.

The fundamental answer here is to put something in the 'catch' block to indicate failure.

For example, if Java could read RETURN values from the stored procedure, you could use those to flag success or failure. The below assumes SQL Server but could easily be tweaked to other databases.

CREATE PROCEDURE P_Test
AS
BEGIN

    BEGIN TRY
        BEGIN TRANSACTION

        (do your stuff)

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- Add any data to a log if desired
        RETURN -1;
    END CATCH;

    RETURN 1;
END

In the above, if it's successful, it will return 1; if it fails, it will return -1.

Begin Transaction with Try Catch Block

1 Answers