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

    We have observed that MariaDB report a stored procedure as modifying data when it executes any of
    the following commands:
    - INSERT
    -  UPDATE
    - DELETE
    - TRUNCATE
    - CREATE TEMPORARY
    - SELECT … INTO … (where the INTO target is not an output variable)

    Additionally we had to make change to functions using "INTO" which were called in a select statement within a stored proc.

  • We recently ran into a very similar issue calling the executeStoredProcedure function from an expression rule which, in turn, utilized an internal stored function. Unfortunately, after the 20.4 upgrade, the ESP function would fail due to a SELECT .. INTO statement located within the stored function (simply returns a YES/NO response): 

    Where the former statement would fail (SELECT.. INTO):

    BEGIN
    
    DECLARE returnVal VARCHAR(20);
    DECLARE c1 INT;
    
    SELECT COUNT(*) INTO c1
    FROM OG_TOOL_VW
    WHERE TOOL_SERVICE_ID IN
        (SELECT TOOL_SERVICE_ID
         FROM OG_TOOL_SERVICE
         WHERE MOBILIZATION_ID = @mobilizationId
           AND IS_ACTIVE = 1);
    
     IF c1 <> 0 THEN
    SET returnVal = 'Yes';
    
     ELSE
    SET returnVal = 'No';
    
     END IF;
    
     RETURN returnVal;
    
    END

    And the refactored SQL would return a success:

    BEGIN
    
    DECLARE returnVal VARCHAR(20);
    DECLARE c1 INT;
    
    SET c1 = (SELECT COUNT(*)
    FROM OG_TOOL_VW
    WHERE TOOL_SERVICE_ID IN
        (SELECT TOOL_SERVICE_ID
         FROM OG_TOOL_SERVICE
         WHERE MOBILIZATION_ID = @mobilizationId
           AND IS_ACTIVE = 1));
    
     IF c1 <> 0 THEN
    SET returnVal = 'Yes';
    
     ELSE
    SET returnVal = 'No';
    
     END IF;
    
     RETURN returnVal;
    
    END

    Hope this helps others who may run into this unintended migration side-effect.

    JT

Reply
  • We recently ran into a very similar issue calling the executeStoredProcedure function from an expression rule which, in turn, utilized an internal stored function. Unfortunately, after the 20.4 upgrade, the ESP function would fail due to a SELECT .. INTO statement located within the stored function (simply returns a YES/NO response): 

    Where the former statement would fail (SELECT.. INTO):

    BEGIN
    
    DECLARE returnVal VARCHAR(20);
    DECLARE c1 INT;
    
    SELECT COUNT(*) INTO c1
    FROM OG_TOOL_VW
    WHERE TOOL_SERVICE_ID IN
        (SELECT TOOL_SERVICE_ID
         FROM OG_TOOL_SERVICE
         WHERE MOBILIZATION_ID = @mobilizationId
           AND IS_ACTIVE = 1);
    
     IF c1 <> 0 THEN
    SET returnVal = 'Yes';
    
     ELSE
    SET returnVal = 'No';
    
     END IF;
    
     RETURN returnVal;
    
    END

    And the refactored SQL would return a success:

    BEGIN
    
    DECLARE returnVal VARCHAR(20);
    DECLARE c1 INT;
    
    SET c1 = (SELECT COUNT(*)
    FROM OG_TOOL_VW
    WHERE TOOL_SERVICE_ID IN
        (SELECT TOOL_SERVICE_ID
         FROM OG_TOOL_SERVICE
         WHERE MOBILIZATION_ID = @mobilizationId
           AND IS_ACTIVE = 1));
    
     IF c1 <> 0 THEN
    SET returnVal = 'Yes';
    
     ELSE
    SET returnVal = 'No';
    
     END IF;
    
     RETURN returnVal;
    
    END

    Hope this helps others who may run into this unintended migration side-effect.

    JT

Children