Execute Stored Procedure

by Appian Corporation

Description

This plug-in provides a Smart Service and Function for executing stored procedures. The Smart Service can be used to execute stored procedures that modify data. Result sets are returned as CDTs. As custom functions cannot have side-effects, it must not be used to execute stored procedures that modify data. It is only safe to use the Smart Service with stored procedures that modify data. Following SQL Statements should not be used by the stored procedures executed with the function:

Insert
Update
Delete
Truncate
Create temporary table
Select…. into (when the input target is not an input or output variable)

Features

Works with both complex and simple data types Tested with: MySQL, SQL Server, and Oracle See the instructions document provided with the download for an example and how to configure (New) Total row count column added to trace log

Release notes

Bug fix for Oracle cursors

Version history (8)

1.5.6

Deprecation notice

1.5.5

Fix for multi-part sql server stored procedure identifiers and CVE fixes.

1.5.4

1.5.4 - Explicitly closes Prepared Statements in both the function and Smart Service.

1.5.3

This Smart Service allows a stored procedure to be executed while mapping data in and out. Result sets are returned as CDTs.

See the attached instructions document for an example and how to configure. Tested with MySQL, SQL Server and Oracle.

v1.5.3 Fixed a bug that closed the JDBC connection on passing null value parameter to the procedure v1.5.2 add support for CLOB fields in executestoredprocedure function (truncates to 128KB) v1.5.1 added configurable timeout to function. defaults to 30 seconds v1.4.6 fixing bugs related to 18.3 upgrade v1.4.5 added total row count column to trace log v1.4.4 return null when the value is null instead of 0 for numerical types when using executestoredprocedure function v1.4.2 new performance log (execute_stored_procedure_trace.csv); support more data types in function; small fix in validation logic v1.3.1 new smart service version with validation toggling and error outputs; support for accessing datastores created in the Administration console via the function v1.2.1 support for Oracle cursor OUT parameters in executestoredprocedure function v1.2.0 fixed ordinal binding error with Oracle (by Betty Huang) v1.1.0 added executestoredprocedure function (by Hillary Nichols). Can only be used for read-only procedures, must not be used with procs that change data. v1.0.8 fixed edge case where an error would stop the database connection being returned to the pool v1.0.7 added CLOB support (tested with Oracle)

1.5.2

This Smart Service allows a stored procedure to be executed while mapping data in and out. Result sets are returned as CDTs.

See the attached instructions document for an example and how to configure. Tested with MySQL, SQL Server and Oracle.

v1.5.2 add support for CLOB fields in executestoredprocedure function (truncates to 128KB) v1.5.1 added configurable timeout to function. defaults to 30 seconds v1.4.6 fixing bugs related to 18.3 upgrade v1.4.5 added total row count column to trace log v1.4.4 return null when the value is null instead of 0 for numerical types when using executestoredprocedure function v1.4.2 new performance log (execute_stored_procedure_trace.csv); support more data types in function; small fix in validation logic v1.3.1 new smart service version with validation toggling and error outputs; support for accessing datastores created in the Administration console via the function v1.2.1 support for Oracle cursor OUT parameters in executestoredprocedure function v1.2.0 fixed ordinal binding error with Oracle (by Betty Huang) v1.1.0 added executestoredprocedure function (by Hillary Nichols). Can only be used for read-only procedures, must not be used with procs that change data. v1.0.8 fixed edge case where an error would stop the database connection being returned to the pool v1.0.7 added CLOB support (tested with Oracle)

1.5.1

This Smart Service allows a stored procedure to be executed while mapping data in and out. Result sets are returned as CDTs.

See the attached instructions document for an example and how to configure. Tested with MySQL, SQL Server and Oracle.

v1.5.1 added configurable timeout to function. defaults to 30 seconds v1.4.6 fixing bugs related to 18.3 upgrade v1.4.5 added total row count column to trace log v1.4.4 return null when the value is null instead of 0 for numerical types when using executestoredprocedure function v1.4.2 new performance log (execute_stored_procedure_trace.csv); support more data types in function; small fix in validation logic v1.3.1 new smart service version with validation toggling and error outputs; support for accessing datastores created in the Administration console via the function v1.2.1 support for Oracle cursor OUT parameters in executestoredprocedure function v1.2.0 fixed ordinal binding error with Oracle (by Betty Huang) v1.1.0 added executestoredprocedure function (by Hillary Nichols). Can only be used for read-only procedures, must not be used with procs that change data. v1.0.8 fixed edge case where an error would stop the database connection being returned to the pool v1.0.7 added CLOB support (tested with Oracle)

1.4.6

This Smart Service allows a stored procedure to be executed while mapping data in and out. Result sets are returned as CDTs.

See the attached instructions document for an example and how to configure. Tested with MySQL, SQL Server and Oracle.

v.1.4.6 fixing bugs related to 18.3 upgrade v.1.4.5 added total row count column to trace log v1.4.4 return null when the value is null instead of 0 for numerical types when using executestoredprocedure function v1.4.2 new performance log (execute_stored_procedure_trace.csv); support more data types in function; small fix in validation logic v1.3.1 new smart service version with validation toggling and error outputs; support for accessing datastores created in the Administration console via the function v1.2.1 support for Oracle cursor OUT parameters in executestoredprocedure function v1.2.0 fixed ordinal binding error with Oracle (by Betty Huang) v1.1.0 added executestoredprocedure function (by Hillary Nichols). Can only be used for read-only procedures, must not be used with procs that change data. v1.0.8 fixed edge case where an error would stop the database connection being returned to the pool v1.0.7 added CLOB support (tested with Oracle)

1.5.0

[MIGRATED] This Smart Service allows a stored procedure to be executed while mapping data in and out. Result sets are returned as CDTs.

See the attached instructions document for an example and how to configure. Tested with MySQL, SQL Server and Oracle.

v.1.5.0 Added caching behavior for executestoredprocedure() function so that metadata queries can be reduced in a similar manner to validate: false in Smart Service. Cache expires every 15 minutes. Also added new function clearexecutestoredprocedurefunctioncache() in case cache needs to be manually cleared for deployment or SP prototype updates. v.1.4.6 fixing bugs related to 18.3 upgrade v.1.4.5 added total row count column to trace log v1.4.4 return null when the value is null instead of 0 for numerical types when using executestoredprocedure function v1.4.2 new performance log (execute_stored_procedure_trace.csv); support more data types in function; small fix in validation logic v1.3.1 new smart service version with validation toggling and error outputs; support for accessing datastores created in the Administration console via the function v1.2.1 support for Oracle cursor OUT parameters in executestoredprocedure function v1.2.0 fixed ordinal binding error with Oracle (by Betty Huang) v1.1.0 added executestoredprocedure function (by Hillary Nichols). Can only be used for read-only procedures, must not be used with procs that change data. v1.0.8 fixed edge case where an error would stop the database connection being returned to the pool v1.0.7 added CLOB support (tested with Oracle)