Free cookie consent management tool by TermsFeed a!executeStoredProcedureForQuery() Function
a!executeStoredProcedureForQuery() Function

Function

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.

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

Parameters

Keyword Type Description

dataSource

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.

procedureName

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.

inputs

List of Map

A list of names and values of the IN and INOUT parameters to the stored procedure created using a!storedProcedureInput(). An input must be provided for every IN and INOUT parameter. Case sensitivity requirements are specific to each type of database. For Oracle and Db2 databases, enter the parameter names in uppercase. For PostgreSQL databases, enter the parameter names in lowercase.

timeout

Integer

The amount of time (in seconds) until the stored procedure execution is cancelled. Default: 30 seconds.

See also: Stored Procedure Input

Returns

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.

Usage considerations

Ensuring users have access to execute stored procedures

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.

Limitations

Keep in mind the following default values and limitations when executing stored procedures:

  • Max timeout: The default maximum value for timeout for a stored procedure is 600 seconds.
  • Max rows per result set: The default maximum value for number of rows per result set is 1000. Result sets that exceed this threshold will be truncated.
  • Max total rows: For stored procedures with multiple result sets, the default maximum value for the collective number of rows is 10,000. Once the collective maximum is reached, all subsequent rows and result sets will be absent from the output.

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
  • The configured value is 500 seconds. With this setting, the value provided for the timeout parameter cannot exceed 500 seconds.

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.

Database specific behaviors

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.

Oracle

  • Capitalize all letters in the stored procedure name and input parameters.
  • For a stored procedure that is defined within a package in an Oracle database, enter the procedure name as PACKAGE_NAME.STORED_PROCEDURE_NAME
  • Access the stored procedure query results in the parameters key using the name that corresponds to the cursor parameter, instead of in results.

DB2

  • Capitalize all letters in the stored procedure name and input parameters.
  • Access the stored procedure query results in the parameters key using the name that corresponds to the cursor parameter, instead of in results.

PostgreSQL

  • The stored procedure name and input parameters are case sensitive.
  • Add the escapeSyntaxCallMode=callIfNoReturn or escapeSyntaxCallMode=call parameter to the connectionURL of the data source.
  • Access the stored procedure query results in the parameters key using the name that corresponds to the cursor parameter, instead of in results.
  • When executing stored procedures from a process model node with validations disabled, parameters of type Number (Integer) in Appian will be passed to the stored procedure as type BIGINT.

Example

In this example, a parameterized PostgreSQL stored procedure is executed using the a!executeStoredProcedureForQuery() function.

Stored procedure details:

  • Stored procedure name: get_customers
  • IN parameters: limit_input, offset_input
  • INOUT parameter: my_cursor
  • PostgreSQL stored procedure definition:
    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

The table below lists this function's compatibility with various features in Appian.
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
Process Reports Incompatible

You cannot use this function to configure a process report.

Process Events Incompatible

You cannot use this function to configure a process event node, such as a start event or timer event.

Open in Github Built: Fri, Mar 22, 2024 (05:03:43 PM)

a!executeStoredProcedureForQuery() Function

FEEDBACK