Repository navigation
SpyProcedure fails when paired with a RemoveObject #198
Description
I have a test where I need to mock a bunch of validation SPs and also want to replace one SP with a mock that should just throw an arbitrary exception to imitate SQL error during code execution. Everything was looking fine until that test case was executed.
After some code tweaks inside the test itself, I've found that after the actual code execution of the tested procedure _SpyProcedureLog table for my mock magically disappeared and is no longer available for the later portion of the test.
Here is an example code that illustrates what is going on inside the described test case:
CREATE PROC TestClass.[test application locks]
AS
-- Faking a dozen tables by using tSQLt.FakeTable
-- Faking a dozen small SProcs using tSQLt.SpyProcedure
EXEC tSQLt.SpyProcedure 'dbo.Entity_Lock';
EXEC tSQLt.SpyProcedure 'dbo.Entity_Release';
-- Replacing an object of interest by using a documented approach
EXEC tSQLt.RemoveObject 'dbo.usp_NeedToThrow';
EXEC('CREATE PROC dbo.usp_NeedToThrow AS RAISERROR(''This is a test'', 16, 1);');
EXEC tSQLt.ExpectException @ExpectMessage = 'This is a test';
-- Running my SP
EXEC dbo.usp_TestMePlease 'Param1', 'Param2';
-- Asserting the results
IF NOT EXISTS ( SELECT 1
FROM dbo.Entity_Release_SpyProcedureLog )
BEGIN
EXEC tSQLt.Fail 'Release was not called properly';
END
RETURN 0;When I run this test without TRY CATCH BLOCK wrapping a EXEC dbo.usp_TestMePlease ...,
I'm getting an error: TestClass.[test application locks] failed: : (Error) Message: Cannot release the application lock (Database Principal: 'public', Resource: 'Resource.NameIt') because it is not currently held. | Procedure: xp_userlock (1) | Severity, State: 16, 1 | Number: 1223 (There was also a ROLLBACK ERROR --> Message: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. | Procedure: Private_RunTest_TestExecution (154) | Severity, State: 16, 1 | Number: 3903)
That message obviously shows that the actual code uses the non-mocked version of dbo.Entity_Release. This clue was confirmed a bit later when I ran my SP within the TRY ... CATCH block. Then the test failed with another error: (Error) Message: Invalid object name 'dbo.Entity_Release_SpyProcedureLog'
SQL Server version: 12.0.2269.0 (MS SQL Server 2014 Express Edition)
tSQLt version: 1.0.8083.3529
Activity
mbt1 commentedon Feb 27, 2024
Is it possible that the procedure under test issues a rollback, explicitly it implicitly?
vkuharsky commentedon Feb 28, 2024
Yes, you're right.
Inside of the tested procedure, I do rollback in case of error.
Here is an example structure of how I do that:
vkuharsky commentedon Feb 28, 2024
After tinkering a bit longer, I've managed to make this test passable. The problem, though, is obvious. While semantically correct, the code above is not intended to be run inside another (outer) transaction. I designed this code to be a top-level data API consumable only by external clients, such as services hosting applications.
Is it possible to make such tests passable without changing how the code works with transactions?
mbt1 commentedon Feb 28, 2024
There are two possible solutions. The one is similar to what you have done. Change the procedure to use a transaction save-point in your procedure. I wrote about that a while ago here: https://sqlity.net/en/585/how-to-rollback-in-procedures/
This preserves your intended functionality without colliding with pre-existing transactions.
The technique above is the preferred architectural solution, because it solves other problems that you might run into, too. But if that change is not possible, there is the other solution. It is an advanced tSQLt technique that runs the test without initiating a transaction. This is fairly new functionality in tSQLt and not yet well documented. I'll attempt to write something up this weekend.
vkuharsky commentedon Mar 5, 2024
I'm very interested to see another way to test things.
mbt1 commentedon Mar 5, 2024
vkuharsky commentedon Mar 14, 2024
It's okay, I'll wait. Just post a link with the actual article after you publish it, or let me know somehow.
vkuharsky commentedon Sep 20, 2024
@mbt1 have you done something about that new functionality? If you still do not have enough time, can you just point to a set of stored procedures inside the tSQLt project?