Issue
I have a TSQL procedure, on SQL for Azure. Here is a minimal reproducible example.
CREATE PROCEDURE MyPROC
AS
BEGIN
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
UPDATE error_log SET error_desc='test'
END CATCH
END
When run directly from SSMS, then error_log
is successfully updated.
When run from my Tomcat application (using the Tomcat user) then the procedure is executed but error_log
is not updated.
Tomcat executes the procedure using the following Java code
DBConnection dbConn = DBConnection.getInstance();
Connection conn = null;
try {
conn = dbConn.getConnection();
conn.setAutoCommit(false);
CallableStatement stmt = conn.prepareCall("{CALL MyPROC}");
stmt.execute();
result = stmt.getInt(fields.size());
if (result == DBProcedures.RESULT_FAILED) { // error
conn.rollback();
} else { // ok
conn.commit();
}
} catch (SQLException e) {
result = DBProcedures.RESULT_FAILED;
try {
conn.rollback();
} catch (SQLException e1) {
System.out.println("Exception on " + this.toString() + " " + e.toString());
}
System.out.println("Exception on " + this.toString() + " " + e.toString());
} finally {
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
System.out.println("Exception on " + this.toString() + " " + e.toString());
}
dbConn.returnConnection(conn);
}
I have checked that the Tomcat user has permissions on the error_log
table.
What am I missing? I understand that not all errors are caught by TRY CATCH
and I understand that sometimes the transaction cannot execute any Transact-SQL statements that would generate a write operation. However my example does not seem to fall under either of these two categories. Further it does work under SSMS.
Solution
This is caused by the fact that auto_commit
is set to off, which means that the driver executes the command SET IMPLICIT_TRANSACTIONS ON;
You can see this in action in this fiddle.
To get around it, you can explicitly rollback the original transaction first. You should also ideally use XACT_ABORT
to prevent hanging transactions, and use THROW;
to rethrow the original error.
CREATE PROCEDURE MyPROC
AS
BEGIN
SET XACT_ABORT ON;
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;
UPDATE error_log SET error_desc = 'test';
THROW; -- only if you want the error to propagate back to Tomcat
END CATCH;
END;
To be brutally honest, I do not recommend using BEGIN CATCH
to log errors, as not all errors are catchable. Instead, use the SQL Server error log, or an XEvent session, to log errors.
Answered By - Charlieface Answer Checked By - Terry (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.