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

Certified Lead Developer

We are using the “Execute Stored Procedure” 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: “Use the Execute Stored Procedure Smart Service to modify data. This function must only be used to query data, not modify”.  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:

  1. Why can we execute the stored procedure fine via the smart service, but not the function?
  2. Why does the function error on a MariaDB environment, but work in a MySql environment?

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer

    James,

    As mariaDB is a fork of MySQL and has a different jbdc driver, has the plug-in been updated to support  MariaDB?

    As they have different drivers, it is possible that MariaDB is seeing two prepared statements executing into a variable as a mod. However, MySQL doesn’t have this issue for the same stored proc. So, just want to make sure there is not a coming update to the plug-in that may solve the issue. Thanks!

  • Can you share an example of the stored procedure you're calling? As long as the statements are not causing updates (e.g. insert, update, create) and sticking to plain select statements I would expect the stored procedure to work.

    I'm not aware of any other reports that the stored procedure function is incorrectly detecting updates under MariaDB. If this is a critical point, I recommend raising a support case also as that's the best channel for getting support for the plug-in

Reply
  • Can you share an example of the stored procedure you're calling? As long as the statements are not causing updates (e.g. insert, update, create) and sticking to plain select statements I would expect the stored procedure to work.

    I'm not aware of any other reports that the stored procedure function is incorrectly detecting updates under MariaDB. If this is a critical point, I recommend raising a support case also as that's the best channel for getting support for the plug-in

Children
  • 0
    Certified Lead Developer
    in reply to James Carter

    James,

    We are facing a similar issue when we are calling the stored procedure which uses complex join queries, function call in select part of the query and subqueries to get the result. I believe the sub queries are implicitly creating temporary tables which may cause the driver to think that an update transaction is being applied.

    Do we have any update to the plug-in which can resolve the issue?