a!executeStoredProcedureForQuery( dataSource, procedureName, inputs, timeout )
Executes a stored procedure in a database. Since this function could run more than once, do not use it with stored procedures that modify data to avoid unintentional changes. To safely execute stored procedures in a saveInto parameter, use a!executeStoredProcedureOnSave.
Tip: 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.
See also: Execute Stored Procedure Smart Service
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. |
See also: Stored Procedure Input
The a!executeStoredProcedureForQuery
function returns a Map data type with the following parameters:
success
(Boolean)error
(Text)results
(List of List of Map)parameters
(List of Map)The error
parameter will only be returned in the event of a failure.
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.
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.
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
.In this example, a parameterized PostgreSQL stored procedure is executed using the a!executeStoredProcedureForQuery()
function.
Stored procedure details:
get_customers
1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE get_customers(
IN limit_input INTEGER,
IN offset_input INTEGER,
INOUT my_cursor refcursor
)
BEGIN
OPEN my_cursor FOR
SELECT * FROM customers LIMIT limit_input OFFSET offset_input;
END;
To call this stored procedure we can use the expression below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
a!executeStoredProcedureForQuery(
dataSource: cons!POSTGRES_DB_POINTER,
procedureName: "get_customers",
inputs: {
a!storedProcedureInput(
name: "limit_input",
value: 5
),
a!storedProcedureInput(
name: "offset_input",
value: 0
)
}
)
Because PostgreSQL databases use cursor objects for the stored procedure output, the query results from this stored procedure are returned in the parameter key.
However, if you execute a similar stored procedure in a database that doesn't use cursor objects for the stored procedure output, the query results will be in the results key instead. Below, you can see how the results will differ between stored procedures executed on PostgreSQL and MariaDB.
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. |
a!executeStoredProcedureForQuery() Function