This page provides guidance on how to use the Execute Stored Procedure smart service in your process model.
Stored procedures are a set of SQL statements that you can save in your database and use to query or modify data over and over again. The Execute Stored Procedure smart service lets you execute a stored procedure that is defined on any of the Appian supported relational databases that you have connected to.
To execute a stored procedure you need to provide:
More information about the parameters can be found in the configuration options and database specific behaviors sections.
After a stored procedure is successfully executed, a map containing a list of result sets and a list of parameters corresponding to the OUT and INOUT values of the stored procedure is returned.
If using a data source connected system, the user executing this activity must have Viewer permissions to the selected data source connected system in order to execute stored procedures.
Category: Data Services
Icon:
Assignment Options - Unattended
This section contains tab configuration details specific to this smart service. For more information about common configurations see the Process Node Properties page.
Name | Data Type | Description | Required | Multiple |
---|---|---|---|---|
Data Source | Any Type | The data source that contains the stored procedure. The value depends on how your data source is connected. For data source connected systems, enter the connected system constant. For data sources connected in the Admin Console, enter the name of the data source or a Text constant that stores the name. | Yes | No |
Procedure Name | Text | Name of the stored procedure. Case sensitivity requirements are specific to each type of database. For Oracle and Db2 databases, enter the name in uppercase. For PostgreSQL databases, enter the name in lowercase. | Yes | No |
Pause Node On Error | Boolean | If set to true, pauses the node if the stored procedure execution fails. Default: true. | Yes | No |
Run Validation | Boolean | If set to true, runs validations on the stored procedure before execution. See the running without validations section below before setting to false. | Yes | No |
Timeout | Number | The amount of time (in seconds) until the stored procedure execution is cancelled. Default: 30 seconds. | No | No |
In addition to the parameters above, inputs with names corresponding to the IN and INOUT parameters of the stored procedure must be created manually. A custom input parameter must be provided for every IN and INOUT parameter in order to pass validations. IN and INOUT parameters can be configured to pass values into the stored procedure by setting the Value field. Remember, the name of each custom input must match the name of a corresponding stored procedure parameter.
Given a stored procedure with the following IN and INOUT parameters:
You can pass customer_name and customer_id into the stored procedure from a process variable by configuring a custom input as you see in the image below.
Note that the output for the INOUT parameter customer_id will be available in an activity class parameter in the outputs tab.
It is strongly recommended to run validations when executing stored procedures unless severe performance degradations are observed as a result.
If you choose not to run validations, the following rules will apply:
ac!parameters
activity class parameter.Appian Data Type | Inferred SQL Data Type |
---|---|
Number (Integer) | Integer |
Number (Decimal) | Double |
Date | Date |
Time | Time |
Timestamp | Timestamp |
Text | Varchar |
Boolean | Boolean |
Depending on the type of database you are using, exceptions to the data type mappings above may apply. See database specific behaviors for more details.
Name | System Data Type | Description |
---|---|---|
Error Occured | Boolean | Whether the stored procedure has returned an error. |
Error Message | Text | The error message if an error occurred. |
Result Sets | List of Map | Result sets returned by the stored procedure. |
Parameters | Map | OUT and INOUT parameters returned by the stored procedure. |
In addition to the outputs above, custom outputs with names corresponding to the OUT, INOUT, and resultSets parameters of the stored procedures can be configured in the outputs tab.
Parameters can be accessed using the ac!parameters
activity class parameter. Similarly, result sets can be accessed using the ac!resultSets
activity class parameter. By parsing these outputs in a custom output expression, you can save individual stored procedure parameters and result sets into process variables.
Given a stored procedure that returns two result sets and the following OUT parameters:
You can save each result set and each OUT or INOUT parameter in its own process variable by using custom outputs with the following configurations.
ac!parameters.customer_name
is stored as pv!customer_name
ac!parameters.customer_id
is stored as pv!customer_id
ac!resultSets[0]
is stored as pv!first_result
ac!resultSets[1]
is stored as pv!second_result
In the image below, you can see how these configurations can be applied in the outputs tab of the smart service node.
Keep in mind the following default values and limitations when executing stored procedures:
These properties can also be configured to increase or decrease the default max value:
For Appian Cloud customers, these values can be updated by opening a support case.
For self-managed installations, these values are controlled by the following properties in custom.properties
:
1
2
3
conf.executeStoredProcedure.limits.maxTimeout=
conf.executeStoredProcedure.limits.maxRowsPerResultSet=
conf.executeStoredProcedure.limits.maxTotalRows=
For example:
1
conf.executeStoredProcedure.limits.maxTimeout=500
500
seconds. With this setting, the value provided for the timeout parameter cannot exceed 500 seconds.Note: The default maximum values represent the recommended settings even though there are no upper bounds limiting custom configurations. Be aware that configuring these guardrails to a value that is significantly greater than the default maximum value may increase your risk of encountering a system error or crash. While unlikely with most configurations, you should still plan to review and test any custom settings accordingly.
If you use this smart service to add, update, or delete data in a database table, and that table is the source of a record type with data sync enabled, those changes will not be automatically synced in Appian.
To sync changes made by this smart service, use the Sync Records smart service. Any data changed by this smart service will also be synced during the next scheduled sync or if you trigger a manual sync.
To automatically sync changes made to a database table, consider using the following smart services instead:
Stored procedures can be executed on any Appian supported relational databases. Below, we have listed some database specific behaviors that impact how you execute stored procedures from Appian.
parameters
key using the name that corresponds to the cursor parameter, instead of in results
.parameters
key using the name that corresponds to the cursor parameter, instead of in results
.escapeSyntaxCallMode=callIfNoReturn
or escapeSyntaxCallMode=call
parameter to the connectionURL of the data source.parameters
key using the name that corresponds to the cursor parameter, instead of in results
.The Execute Stored Procedure smart service is available as an expression function that can be executed inside a saveInto
on a component or as part of a Web API.
For information on how to execute a read-only stored procedure from anywhere in Appian, see the a!executeStoredProcedureForQuery() function. Unlike the smart service function, a!executeStoredProcedureForQuery()
is not restricted to being called inside a saveInto
. However, the a!executeStoredProcedureForQuery()
function should not be used to execute stored procedures that modify data.
a!executeStoredProcedureOnSave( dataSource, procedureName, inputs, timeout, onSuccess, onError )
Keyword | Type | Description |
---|---|---|
|
Any Type |
The data source that contains the stored procedure. The value depends on how your data source is connected. For data source connected systems, enter the connected system constant. For data sources connected in the Admin Console, enter the name of the data source or a Text constant that stores the name. |
|
Text |
Name of the stored procedure. Case sensitivity requirements are specific to each type of database. For Oracle and Db2 databases, enter the name in uppercase. For PostgreSQL databases, enter the name in lowercase. |
|
List of Map |
A list of names and values of the IN and INOUT parameters to the stored procedure created using |
|
Integer |
The amount of time (in seconds) until the stored procedure execution is cancelled. Default: 30 seconds. |
|
Any Type |
A list of saves or an HTTP response to execute after the function executes successfully. Created with |
|
Any Type |
A list of saves or an HTTP response to execute when the smart service does not execute successfully. Created with |
For the example below, we define a stored procedure that writes to a database and show how it can be executed using the a!executeStoredProcedureOnSave()
function.
Stored procedure details:
add_or_update_customer_and_case
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DELIMITER //
CREATE OR REPLACE PROCEDURE add_or_update_customer_and_case(
INOUT customer_id INT,
IN name VARCHAR(255),
INOUT case_id INT,
IN status VARCHAR(255),
IN description VARCHAR(255)
)
BEGIN
IF(customer_id IS NULL) THEN
INSERT INTO customers(name, email, phone_number) VALUES (name, null, null);
END IF;
IF(case_id IS NULL) THEN
INSERT INTO cases(status, description) VALUES (status, description);
ELSE
UPDATE cases SET status = status, description = description WHERE case_id = case_id;
END IF;
END //
DELIMITER ;
To call the stored procedure that we've defined, we can use the code below inside a saveInto
.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
a!executeStoredProcedureOnSave(
dataSource: cons!MARIADB_POINTER,
procedureName: "add_or_update_customer_and_case",
inputs: {
a!storedProcedureInput(name: "customer_id", value: local!customerId),
a!storedProcedureInput(name: "name", value: local!name),
a!storedProcedureInput(name: "case_id", value: local!caseId),
a!storedProcedureInput(name: "status", value: local!status),
a!storedProcedureInput(name: "description", value: local!description),
},
onSuccess: {
a!save(local!customerId, fv!parameters.customer_id),
a!save(local!caseId, fv!parameters.case_id),
},
onError: {
a!save{local!errorMessage, fv!errorMessage}
}
)
After the stored procedure is successfully executed, you can see that the INOUT parameters customer_id and case_id will be loaded into local variables from the fv!parameters
function variable. Note that for this stored procedure there are no queries, so the fv!resultSets
function variable will contain an empty list.
In the event that an error occurs, local!errorMessage
will be populated with the error message from the function variable fv!errorMessage
.
Feature | Compatibility | Note |
---|---|---|
Portals | Partially compatible | Can be used with Appian Portals if it is connected using an integration and web API. |
Offline Mobile | Incompatible | |
Sync-Time Custom Record Fields | Incompatible | |
Real-Time Custom Record Fields | Incompatible | Custom record fields that evaluate in real time must be configured using one or more Custom Field functions. |
Process Reports | Incompatible | Cannot be used to configure a process report. |
Process Events | Incompatible | Cannot be used to configure a process event node, such as a start event or timer event. |
Execute Stored Procedure Smart Service