Question
Parana Banco
BR
Last activity: 9 Dec 2020 12:58 EST
Exception raise and error handling with SQL Stored Procedure (SQL Server)
Hi all,
We've been having difficulty on Pega being able to get the exception thrown during an execution of an stored procedure using SQL connect.
We've already ran some tests using Obj methods, RDB methods as well. The stored procedure call works fine, however the problem is whenever we have an exception during the execution (for example, a duplicate key insertion attempt) Pega is not able to catch the error so we can perform the proper exception handling.
Is there any best practices or recommended approach to handle exception when we have a transactional call of an stored procedure which is inserting data in some Customer Data tables on the DB Server?
Randstad Technologies US
US
can your try using sql error page in connect-sql rule. I think it will hold even DB side errors .
{SQLPage:MySqlErrorsPage}
In this page, the properties holding the error information are:
- pxRDBError- an error message from Process Commander.
- pxRDBSQLCode- a result code form the database access driver.
- pxRDBSQLVendorError1- an error code from the database vendor package.
- pxRDBSQLVendorError2- an error code from the database vendor package.
- pxRDBSQLVendorMessage1- an error message from the database vendor package.
- pxRDBSQVendorMessage2- an error message from the database vendor package.
- pxSQLStatementPre- the SQL statement as you entered it in the Connect SQL form.
- pxSQLStatementPost - the SQL you entered in the Connect SQL form tab with the references resolved to show their values, so you can verify the references.
Parana Banco
BR
Hi Venkata, thanks for your response.
I've checked with the other developers, and they've confirmed we did try that, however the RAISEERROR we use in our procedure is not populating the Data Page you mentioned.
Here is an example of the exception handling we have in our SQL Stored procedure:
COMMIT TRAN END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); ROLLBACK TRAN; RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH;