Database Connector | connections are not getting closed when calling a Stored Procedure in Mule 4

SYMPTOM

<db:stored-procedure doc:name="Stored procedure" doc:id="bbe98f4a-3f4e-44f2-950f-fa0f1d4c671c" config-ref="Database_Config">
 <db:sql><![CDATA[CALL sys123.GetAllData()]]></db:sql>
</db:stored-procedure>

CAUSE

In Mule 4 database streaming is always enabled by default. So when the streaming option is enabled for the stored procedure call, the connection, statement, and result set are left open after execution completes.

SOLUTION

As the streaming strategy is always enabled in Mule 4, we have to enclose the stored procedure in TRY Scope to close the connection after the Database Store Procedure call. Please refer the below example:

<try doc:name="Try" doc:id="8b471698-d359-4318-bb2a-388d52b1bddd" transactionalAction="ALWAYS_BEGIN">
<db:stored-procedure doc:name="Stored procedure" doc:id="bbe98f4a-3f4e-44f2-950f-fa0f1d4c671c" config-ref="Database_Config" transactionalAction="NOT_SUPPORTED">
<db:sql><![CDATA[CALL sys123.GetAllData()]]></db:sql>
</db:stored-procedure>
</try>