Execute Stored Procedure - Inconsistencies with function/Smart Service across architectures

Question

We are using the &ldquo;Execute Stored Procedure&rdquo; plugin and have noticed differences across architectures. On a SAIL interface, we are using the fn!executestoredprocedure function, and the stored procedure we are calling defines and executes 2 prepared statements, both of which select data. 
 
 In our environment with Appian 20.3, we are receiving the following error when executing the function: &ldquo;Use the Execute Stored Procedure Smart Service to modify data. This function must only be used to query data, not modify&rdquo;. However, we are not modifying data via the stored procedure, and furthermore, when we call the stored procedure in the Execute Stored Procedure smart service , it works fine. Also, when we execute the stored procedure directly in the DB via PhpMyAdmin, it works fine. 
 
 The environment where we are receiving this error is Appian 20.3 on cloud with MariaDB 10.5.6 
 
 However, when we do the same thing on another Appian 20.3 cloud environment with MySql 5.7 , it works fine from the fn! call. 
 
 So, our questions are: 
 
 Why can we execute the stored procedure fine via the smart service, but not the function? 
 Why does the function error on a MariaDB environment, but work in a MySql environment?

James Carter · Answer

Hi Evan, to answer your questions: 
 
 The function is only permitted to call read-only stored procedures. The function attempts to detect when it's calling a stored procedure that's modifying data and reject the request when it identifies one. In this case it's calling (or believes it is calling) a stored procedure that's modifying data. The Smart Service is permitted to call stored procedures that modify data, so this is why the stored procedure will work. 
 The function relies on the underlying database JDBC driver reporting whether an update occurred 
 
 I suggest simplifying the stored procedure to identify which part is causing the function (JDBC driver) to identify that data modification is happening. Once you know what statement in the stored procedure it is, there may be options to modify it

Execute Stored Procedure - Inconsistencies with function/Smart Service across architectures

Top Replies