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.
For Appian Cloud customers please note that Appian Cloud enforces GTID consistency. To ensure this Smart Service is read-only, any Cloud-based stored procedures that create temporary tables cannot be used in conjunction with this Plug-in
v1.5.1 now has configurable timeout (default 30 seconds)
Share:
https://docs.appian.com/suite/help/18.4/Database_Schema_Best_Practices.html#null-values
The smart service with only proceed when the stored procedure has finished executing. There is no timeout configured for the smart service.
The function does not support arrays as inputs. However it is usually possible to workaround this by passing a delimited string of values to the stored procedure. There are example online on how to pass a delimited list of values to a stored procedure.
Yes it is. This plug-in aim's to be compatible with current supported versions of Appian: https://docs.appian.com/suite/help/latest/Product_Release_History.html#appian-on-premise
Is this plugin compatible with Appian 19.2 on premise.
In the ExecuteStoredProcedure function, how can we do operator-based filtering? We need to query on a stored procedure using certain filters similar to when using a!queryEntity, i.e. using a "not" filter, an "in" filter, "<>", etc, etc. Any advice? We can't figure out how (or if) this is possible here?
+1 James Carter
Or is there a defined timeout?
Hey James, Using this smart service in a process, will the node only proceed when the procedure is done or will it only call the procedure and move on instantly?
SQL Server allows defaulting parameters passed as null but the plugin seems to pass empty string instead of null at least in the case of a string. Is there any effort to convert empty strings to actual nulls?
i am using appian on cloud. will check with my system admin tomorrow on version currently used by us.
Check the Admin Console - then Plug Ins. You should see an entry called Execute Stored Procedure. The version is in brakcets - the latest version is Execute Stored Procedure (v1.4.6) - if you're cloud based then you can upgrade to the latest version via the Admin Console itself. Not sure what you need to do for On Premise installs though.
How do i check for version of the plug-in ?
Hmm - are you using the latest version of the Stored Procedure Plug In ? There ought to be a trace file for Stored Procs that might help provide some additional output.
2019-06-19 08:00:54,936 [Appian Work Item - 121903 - execution01 : UnattendedJavaActivityRequest] ERROR com.appiancorp.process.engine.UnattendedJavaActivityRequest - An error occurred while executing activity: id=268442236, classname=com.appiancorp.ps.ss.ExecuteStoredProcedure com.appiancorp.suiteapi.process.exceptions.SmartServiceException: java.lang.NullPointerException at com.appiancorp.suiteapi.process.exceptions.SmartServiceException$Builder.build(SmartServiceException.java:145) at com.appiancorp.ps.ss.ExecuteStoredProcedure.run(ExecuteStoredProcedure.java:344) at com.appiancorp.process.runtime.framework.DefaultActivityExecutor$1.lambda$call$0(DefaultActivityExecutor.java:131) at com.appiancorp.plugins.PluginUsageLogger.runWithPluginInformation(PluginUsageLogger.java:52) at com.appiancorp.process.runtime.framework.DefaultActivityExecutor$1.call(DefaultActivityExecutor.java:131) at com.appiancorp.process.runtime.framework.DefaultActivityExecutor$1.call(DefaultActivityExecutor.java:125) at com.appiancorp.common.ContextClassLoaderSwitcher.runInContext(ContextClassLoaderSwitcher.java:25) at com.appiancorp.process.runtime.framework.DefaultActivityExecutor.execute(DefaultActivityExecutor.java:125) at com.appiancorp.process.engine.UnattendedJavaActivityRequest.execute(UnattendedJavaActivityRequest.java:83) at com.appiancorp.process.engine.UnattendedJavaActivityRequest.execute0(UnattendedJavaActivityRequest.java:55) at com.appiancorp.process.engine.ContinuationRequest.executeOuter(ContinuationRequest.java:71) at com.appiancorp.process.engine.ContinuationRequest.execute(ContinuationRequest.java:100) at com.appiancorp.process.workpoller.UnattendedRequestHandlerBean.onMessage(UnattendedRequestHandlerBean.java:35) at com.appiancorp.process.workpoller.WorkItem.run(WorkItem.java:84) at org.springframework.jca.work.SimpleTaskWorkManager$DelegatingWorkAdapter.run(SimpleTaskWorkManager.java:239) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: java.lang.NullPointerException at com.appiancorp.ps.ss.ExecuteStoredProcedure.run(ExecuteStoredProcedure.java:253) ... 16 more