Execute Stored Procedure

**NOTE: This plug-in is deprecated and no longer recommended for use for sites running on Appian 22.1 or later. The functionality of this component is available in Appian without installing the plug-in. You can use a!executeStoredProcedureForQuery(), a!executeStoredProcedureOnSave(), or the Execute Stored Procedure Smart Service in Appian 22.1 or later.**

Overview

This plug-in provides a Smart Service and Function for executing stored procedures. The Smart Service can be used to execute stored procedures that modify data. Result sets are returned as CDTs. As custom functions cannot have side-effects, it must not be used to execute stored procedures that modify data. It is only safe to use the Smart Service with stored procedures that modify data. Following SQL Statements should not be used by the stored procedures executed with the function:

  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • CREATE TEMPORARY TABLE
  • SELECT... INTO (when the input target is not an input or output variable)

Key Features & Functionality

  • Works with both complex and simple data types
  • Tested with: MySQL, SQL Server, and Oracle
  • See the instructions document provided with the download for an example and how to configure
  • (New) Total row count column added to trace log
Anonymous
  • Hi,

    We have a parameter called timeout. When we try to pass value to this parameter, we are getting "Expression evaluation error at function 'executestoredprocedure' [line 2]: Unexpected number of parameters."

    Appian Version: 21.1

    Execute store proc plugin version: 1.5.1, Even tried with 1.5.4 version.

    Any idea to resolve this issue would be very helpful!

    Thanks in Advance.

  • After upgrading to MariaDB, several of my stored procedures are returning the standard error message:

     "... This function must only be used to query data, not modify."

    None of these stored procedures modify data when called from an expression rule.  The one common thing seems to be that they do have a "select ... into", which stores information exclusively into local variables (no table modifications).  I've seen on Community that other users have been having this issue for several months at least.  Is there any planned fix for this issue?  I'm on 21.2 and the latest version of the plug-in.

  • v1.5.4 Release Notes
    • Explicitly closes Prepared Statements in both the function and Smart Service.
  • Hi,

    We upgraded Appian to version 20.4 recently and during this process, we also upgraded this plugin from v1.4.4 to v1.5.3. Following the upgrade, one of our processes that uses the Execute Stored Procedure smart-service ran as scheduled. The process takes a file with ~45k rows, splits it into batches of 1000, and then runs the smart service for each batch.

    We are seeing that the first 10 or so batches are taking an hour each to run, and then suddenly the batches start completing in 5 minutes each.

    Prior to the upgrade, each batch would complete in ~5 minutes. The smart-service slowdown/hanging is only happening between 1am and 8am which is really strange. 

    Any thoughts on whether or not this could be a plugin issue? Has anyone seen any similar behaviour?

  • Hello, we are trying to use the Data Generation Application, however we ran into an issue when the application attempts to call one of the stored procedures as part of the Data Generation process. It occurs in the DG_RetrieveTablesForApplication expression rule, while calling the executestoredprocedure() function inside of the "Execute Stored Procedure" plugin. However we keep getting this error:

    We are passing in the parameters for the stored procedure using the ProcedureInput CDT. Currently we are using MySql version 8.0.20, and the stored procedure seems to work fine when we execute it from the DB itself.

    Could you help to look into this issue? Thanks.

  • Hello! I am trying to execute stored procedures on PostgreSQL and currently facing the below error:


    Does this service support execution of PostgreSQL procedures? And If not, is there any other way to achieve this?

    Thanks.

  • +1 on this.

    We're starting to rely heavily on stored procedures, given that the query generated by queryEntity function seems to be overly complicated with subqueries.

    Reviewing SP trace log, we can see that chunk of the SP runtime comes only from the validation. We're also starting to see performance impact on the DB itself due to the volume of the validation queries executed.

  • Hi Pete,
    Thanks for the confirmation. 

  • We are only using the smart service at this point so I can't comment on the function, but as noted above the function is not supposed to be used to modify data, only read. Most of our critical path processes use write to data store smart services, but a few do use procedures. We have observed no issues with procedures- that includes modifying the DB as well.

  • Hi - we have recently upgraded to 20.4 with Maria DB and also have extensive use of the Stored Procedure plug in.

    So far all of our Smart Service calls have been ok - which perform create, read, update and delete statements. 

    We also ensured that any use of the stored procedure function in expressions and script tasks were limited to read functions.

    We have had a niggle with one stored proc returning inconsistent results but on the whole the Plug In still operates as it did on MySQL. Bearing in mind that if you are on Cloud DB, the function warned users not to use this with procedures that created data due to GTID enforcement.

    Hope this helps. 

    As with all upgrades though thorough testing is highly recommended.  

    Cheers 

    Paul