a!executeStoredProcedureForQuery() Function
This function may not be compatible with all features of Appian. For details, see the Feature Compatibility section below.

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)
  • errorMessage (Text)
  • results (List of List of Map)
  • parameters (List of Map)

The errorMessage 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 limitations when executing stored procedures:

  • Max timeout: The maximum timeout for a stored procedure is 600 seconds.
  • Max rows per result set: The maximum 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 collective maximum number of rows is 10,000. Once the collective maximum is reached, all subsequent rows and result sets will be absent from the output.

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

  • The stored procedure name and input parameters are case sensitive.
  • For a stored procedure that is defined within a package in an Oracle database, enter the procedureName 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

  • The stored procedure name and input parameters are case sensitive.
  • 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
Custom Record Field Expressions Incompatible
Open in Github Built: Fri, Aug 12, 2022 (10:01:45 AM)

On This Page

FEEDBACK