Execute Stored Procedure

**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:

  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • CREATE TEMPORARY TABLE
  • SELECT... INTO (when the input target is not an input or output variable)

Key Features & Functionality

  • 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
Anonymous
Parents
  • 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.

  • The stored procedure was tested successfully on SQL Server has one varchar(200) input and Integer output. We are using an Appian 18.4.. Please help.. I tried all kind of solutions i could find online. 

  • 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
  • I have the node inputs already matched to that on Stored procedure. I looked-up the STD logs, other than the following  (which says it failed due to a NULLException, there are no leads (atleast for me).

Comment Children
  • 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