**NOTE: This plug-in is deprecated and no longer recommended for use for sites running on Appian 22.1 or later. The functionality of this component is available in Appian without installing the plug-in. You can use a!executeStoredProcedureForQuery(), a!executeStoredProcedureOnSave(), or the Execute Stored Procedure Smart Service in Appian 22.1 or later.**
Overview
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:
Key Features & Functionality
When I try the Execute Stored Procedure smart service in my process model, I get a "java.lang.NullPointerException" error. No further information to why this error is returned.
Additionally, I checked the Execute Stored Procedure logs and found that the issue is not consistent. Sometime it works, sometime it don't.
We are using Smart Service.
Appian version 18.4
Hi James,
We are using Execute Stored Procedure in Test and Prod region with plug-in version 1.4.2 for both.
In Test, SP seems to be working fine with no error. But in Prod, the exact same SP is failing with GTID error.
GTID is enabled in both regions.
SP have multiple IN and OUT parameters along with creation and deletion of temp table.
Could you please suggest what could be possible reason for Prod failure.
Thanks & Regards,
Nafis Khan
We are considering updating this plugin to support the CLOB datatype. We are integrating with a legacy system where most of the columns that would normally be VARCHAR2 are CLOBs and the only way to access them is via a stored procedure. Do you have any thoughts on this change? Is this change something we could provide back to the community?
Thanks!
Nate
@ajinkyab277 are you using version 1.4.6? (if not, please upgrade and try again). If that does not help, try to set auto commit at the beginning of the stored procedure
dev.mysql.com/.../replication-gtids-restrictions.html - "It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1."
The function initiates an explicit transactions and must not be used in conjunction with statements that modify data (such as temporary tables, insert, update, delete, etc statements). SELECT statements are safe to use with the function.
The smart service does not initiate an explicit transactions, as as long as v1.4.6 is deployed and possibly also autocommit=1 is enabled in the stored procedure it should work
Hi,
Is this plugin compatible with appian 19.1 version. We are facing below error
1. When we execute the store procedure with function it works . and after that if execute the store procedure with smart service it fails. We have the GTID constraints enable in our environment. Smart service fails with GTID error.
We think that it may be because after the execution of function SS is also trying to use the same connection pul and hence trasaction logic in function. GTID constrains does not allows Store proc with temple table creation logic to be called in transaction
The desciption above says that data should not be updated at all by a stored procedure using this plugin, but the pdf documentation in the plugin zip says:
"This function may be used anywhere expressions are valid. This function must not be used to update
data and actively attempts to prevent such use. **Use the Smart Service to update data instead.**" [Emphasis added]
The latter quote makes it look like the no-update restriction is only for the function version, consistent with the goal of preventing stateful changes in expression rules generally (and needed by the parallelism in SAIL evaluation in recent versions of Appian).
Which restriction is correct? Note that doing updates in sprocs is a very useful way to implement transactional locking on related actions.
I am having the procedure which has text input and the output is CLOB .When I test the fn!executestoredprocedure in expression rule, the result is null whereas the same procedure is executed in the process model using smart service and it returns the output.
Is this the expected behaviour?
Thanks!!
Hi timothym906, I would suggest submitting a support case due to the type of error reported
James,
We received the following error when trying to deploy the latest version in Appian 18.2. Any help is appreciated.
12:56:31,720 INFO [stdout] (Appian Plugin Hot Deploy) 2018-08-02 12:56:31,720 [Appian Plugin Hot Deploy] ERROR com.atlassian.plugin.manager.DefaultPluginManager - There was an error loading the descriptor 'ProcedureInput' of plugin 'executestoredprocedure'. Disabling.
12:56:31,724 INFO [stdout] (Appian Plugin Hot Deploy) com.atlassian.plugin.PluginException: com.appiancorp.suiteapi.common.exceptions.AppianException: com.appiancorp.suiteapi.common.exceptions.AppianException: The type [{urn:appian:plugin:executestoredprocedure:types}ProcedureInput] was not exported because of the following error while accessing the Appian data source: [com.appiancorp.type.model.DatatypeModelNotFoundException: Type definitions could not be found in the Appian data source for the following type ids: 5331 (APNX-1-4047-008)]. (APNX-1-4047-00A)