Upgrade from Execute Stored Procedure Plug-in to OOTB Features

Introduction

In version 22.1 Appian released support for executing stored procedures out-of-the-box using a!executeStoredProcedureForQuery(), a!executeStoredProcedureOnSave(), or the Execute Stored Procedure Smart Service. With this release, the Execute Stored Procedure plug-in was deprecated. Below is a guide for how you can upgrade the deprecated plug-in function calls and smart service nodes to the fully supported features. Note that this guide does not guarantee the success of your updates. All updates should be tested thoroughly.

When upgrading, keep in mind the following backwards compatibility considerations:

Optionally, the fully supported Appian Execute Stored Procedure functions and smart service can be used with data source connected systems, which allow you to apply role map security to your data sources. Migrating from an admin console data source to a data source connected system is simple. From the Appian designer click to create a new connected system object, copy the connection details, and apply security. To execute stored procedures using the new data source connected system you’ll also need to create a connected system constant.

Finding all plug-in usage for a site

To find each instance that the Execute Stored Procedure plug-in is used, follow these steps:

1. Open the “OBJECTS” page in the Appian Designer and select the “PLUG-INS” tab.
2. Search and select the “Execute Stored Procedure” plug-in.
3. Click the “Dependents” button.

Upgrading executeStoredProcedure

Like the plug-in function, a!executeStoredProcedureForQuery should not be used to execute stored procedures that modify data. Doing so can have unintended side effects! If you’re relying on this unsupported behavior to execute a stored procedure that modifies data, we’re excited to introduce you to the new a!executeStoredProcedureOnSave smart service function. Using this function, you can safely execute stored procedures that modify data from an interface.

Upgrading to the Appian execute stored procedure functions is super easy. In most cases it will be as simple as changing the function name.

1. Update the function name from fn!executeStoredProcedure to a!executeStoredProcedureForQuery.
2. Change the dataSourceName parameter to dataSource. If you are upgrading to use a new data source connected system, use the connected system constant instead of copying the name of the admin console data source.
3. The a!executeStoredProcedureForQuery function requires all IN and INOUT parameters to be defined. If you have any missing IN or INOUT parameters, add them to inputs.
4. Test the expression.

Upgrading execute stored procedure smart service nodes

To upgrade execute stored procedure process model nodes, we recommend copying the properties from the process model documentation in a separate window and following the instructions below:

1. Drag and drop a new “Execute Stored Procedure” node into the process model next to the deprecated execute stored procedure plug-in node.
2. In the process model documentation, find the execute stored procedure plug-in node properties.
3. Copy each property from the process model documentation of the deprecated node. Note that the dataSourceName input will need to be copied into dataSource. The new execute stored procedure smart service requires all IN and INOUT parameters to be defined. If you have any missing IN or INOUT parameters, add them to as custom inputs. If you are upgrading to use a new data source connected system, use the connected system constant instead of copying the name of the admin console data source.
4. Remove the deprecated process model node.
5. Test the process model.