**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:
Key Features & Functionality
Now that the database connection can be configured using the connected system, can I use 'Data Source Name' as the name of the connected system?
I am trying to get the result of the Execute Stored Procedure, trying to capture the list of dictionary as a result(output). I tried giving a result variable in the input tab of the smart service and setting the type of it as the cdt that has the matching columns as the rows that we intend to get as the result. I have attached the image of how i set it up. Can someone help me getting the result from this? keertihasyagarhareeshag0001
Hi, just stumbled over a bug when using nvarchar(max) in SQL Server 2016.
I am fetching a base64 encoded document with output defined as nvarchar(max). When using the execute SP plugin the data gets cutoff after 4000 characters. I tried it with the deprecated data type ntext. With ntext I get back the complete data.
Also tested this directly with the SP. There ntext and nvarchar(max) return the full data. Therefore I think it's an issue within the Execute SP Plugin.
Please have a look. Thanks.
Can you please elaborate on this update. Didn't find anything related to this in the latest documentation of this plugin. I see that the latest version of the executestoredprocedure now has the Timeout parameter and it defaults to 30 seconds. Would you be able to let us know what would be the default timeout for previous versions of the function?
what fill in procedure name?
I see that the latest version of the executestoredprocedure now has the Timeout parameter.And it defaults to 30 seconds. Would you be able to let us know what would be the default timeout for previous versions of the function? This is imporatant as I have seen some of the cases where we used executestoredprocedure (prior to this update) take more than 30 seconds and Im concerned about any issues in existing code due to this plugin update.
Also, does the Timeout actually kill the query or only disconnects the Application from the DB? It would be very useful if the query can be killed so the potentially long running query doesn't affect the DB.
And also, the timeout for the Execute Stored Procedure Smart Service (which doesn't yet have this option). Is there a plan to include the Timeout parameter in the smart service in future versions?
Thank you for this wonderful and very useful plugin,
Pavan
Use executestoredprocedureforked() and make sure that the input were casted to ProcedureInput_Forked
are you using MySQL? I'm not sure if you can output a set of rows as an OUT parameter, but what you can do if you're returning a set of rows is to just have the last statement as the SELECT statement.
Then in the Inputs tab of the Execute Stored Procedure smart service, you can define an ac parameter named "resultset1" marked as multiple - it has to be a CDT matching the columns of the returned rows - and have a save Into defined to a pv.
If you're using the function version, then it will just return the data in the "result" attribute, a list of dictionary.
I suggest you download the plugin jar; it has the documentation and examples on how to use this plugin.
How to pass the result set back to Appian from the stored procedure? I am using an out parameter of type varchar that returns string data. But I don't know what datatype I should select if I have to return a set of rows? Any thoughts?
Hi All,
Please find the updates on the above-mentioned issue.
This issue is being caused due to the syntax miss. The Inputs should be between double curly brackets {{ }} and the timeout value declared after the list.
Please see the example below: fn!executestoredprocedure( "jdbc/AppianAnywhere", "sp_ListBooks", { { name: "title_search", value: "%pig%" } }, 80 )