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.

See also: Execute Stored Procedure Smart Service

Parameters

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.

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

Feedback