Execute Stored Procedure

There was a false positive detection of removed APIs in the Health Check that has been addressed. API's being detected as removed should not resolved in new Health Check reports

DB2 has not been explicitly tested so may not work. Can you try it out and let us know if it works for you?

Does this plugin work on DB2 database? I see that it is tested only on MS SQL, SQL Server and Oracle?

Hi James,

I have a query on how connection is maintained while using the function as well as smart service, i.e. when does the connection to DB opens and closes?

Is it per execution of smart service / function call or some other logic?

We are calling a stored procedure hosted on another legacy system data base using this plugin and they have reported that there are many open connections on their dashboard for this call from Appian. Any comments or suggestions welcomed:)

Thanks.

Hitesh

Hi James,

In health check, we are receiving the Apis used in plug-in has removed/deprecated. Please find the below report we receive in health check. Can we expect an update sooner to fix this?

Execute Stored Procedure (executestoredprocedure) references removed Appian APIs [removed] com.appiancorp.suiteapi.process.exceptions.SmartServiceException.Builder.<init>(java.lang.Class,java.lang.Throwable) [removed] com.appiancorp.suiteapi.type.TypedValue.<init>(java.lang.Long,java.lang.Object)

Thanks James, yes this procedure is located in an Oracle 12c database.  However, it it our main financials system so updating environmental settings for us is extremely unlikely.  Are there any plans to update the plugin to utilize a similar Run Validation input?  That would be huge for the community!  

Hi Chris, I suspect you're right regarding Run Validation. Would you happen to be using Oracle? Another poster below shared an option to improve the validation performance. Given the performance difference is so dramatic, I definitely recommend reviewing the case with your DBA, as I would expect the validation overhead to be very low.

Hi James, we are seeing massive performance differences with fn!executestoredprocedure vs the Execute Stored Procedure Smart Service.  Typically, 9 seconds for the function vs 0.1 seconds for the Smart Service, when calling the same procedure. Our guess is this could be related to the Run Validation parameter not being available on the function.  Could this be the case, and if so, are there any plans to add a run_validation input on the function?  In our case (validating over 100 grid rows with a procedure), the function is not usable due to the performance issue. Any input would be great, thanks!

Hi James.

I'm seeing periodic issues in our High Availability Appian Cloud production environment when using the Execute Stored Procedure plugin. Our database is MySQL. Our Appian version is 19.3.

We have an automated process which triggers every night at midnight. This process is responsible for generating an Excel file which contains human-readable details for every member of a specific Record type. The process is fairly simple:

1. We use a Query Database node to truncate the database staging table.
2. We use an Execute Stored Procedure node to call a stored procedure which (only) contains an INSERT INTO ... SELECT ... statement.
3. We use an Export Data Store to Excel node to dump the contents of the staging table into a new Excel file.

There should be roughly 32,000 rows of data in this table. The generated Excel file should be approximately 18.5MB in size. The process successfully completes every time, with no errors.

On a handful of occasions (and only in our production environment), however, we have noticed that the process will successfully run, but will generate an Excel file which is blank. Upon further investigation, we noticed that, on these occasions, the database staging table has not been populated, even though the stored procedure node ran & completed successfully, with no errors. When we manually re-run this process to try to generate the Excel file correctly, it sometimes will work as expected. Other times, the process will, again, complete with no errors, but will generate a blank Excel file (due to the database staging table still not being populated). Still other times, the Execute Stored Procedure node will "hang", sometimes for upwards of 30 minutes. In this last scenario, we observe in PHPMyAdmin that the database process which contains the execution of the stored procedure is "Waiting for table metadata lock".

I have a theory as to what may be going on, but I have no way to really confirm or prove it. Perhaps you can shed some light on this for me. I believe that longer-running stored procedures, executed through the Execute Stored Procedure plugin, may sometimes lose or otherwise fail to close their implicit transactions.

You have previously stated that "The smart service (will) only proceed when the stored procedure has finished executing." In our case, however, we have seen that the Smart Service node will sometimes complete when the result of the stored procedure has not yet been COMMIT-ted. Adding a wait timer after the Execute Stored Procedure node does not help, either. I believe that the implicit COMMIT statement, called after the execution of the stored procedure completes, might occasionally be "killed" mid-flight by the closing of the database connection at the time the Smart Service node completes, especially if the modified data set is sufficiently large.

This issue only occurs in our production environment, where we have roughly 8,000 more rows of data than in our lower environments. I've already created an Appian Support case to get help investigating this issue, to no avail, but I was advised to reach out to you, the plugin author, directly. Do you have any thoughts or insight into what I've described above?

We identified that the below two properties have to be set for Oracle 12C in order to get around this issue which we encountered. 

1. optimizer_adaptive_features = FALSE
2. optimizer_adaptive_statistics = FALSE