AnsweredAssumed Answered

Database Transaction in Stored Procedure - Explicit Not Supported?

Question asked by mplatt168095 on Jan 22, 2018

I have a stored procedure that soft deletes an existing record in the database before inserting a new one.


I wanted this so that the record in question does not get soft deleted then the insert fails, then there is no active record, and leaves the database in an inconsistent state. 


I wanted to put a transaction around the soft deletion, and subsequent insert (all within the stored procedure), but when I do, I seem to get the error:


The server failed to resume the transaction. Desc:5a00000001.


This is from the JDBC driver to MSSQL 2012.


I read that JDBC does not support explicit transactions, and that you should use the JDBC API.


First, is this correct?


Second, if this is correct, is there provision for transaction in the boomi platform, that I have not seen?


I'm interested in how the transaction can be enabled, not in how I might work around this problem with more code.