Execute Stored Procedure

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?

Hi James,

we are using the executestoreprocedure function in one of our forms get query a list choice values we had newly introduced a file upload field component in the form after adding some attachments when we are trying to submit the form there is a delay in the form submission. But when we remove the executestoreprocedure function and try to submit the form it goes smoothly without any delay. The slowness occurs only when we have added attachments 

Hi James,

We are using this stored procedure smart service for a while and we did not see any issues, recently we have migrated from Oracle 11G to 12C, and while doing the testing we found that the meta data query is taking longer time and holding more resources. Is this issue reported anytime / any fix available for it. We are currently using V1.2.0. below is the query which is been executed. 

SQL Text : SELECT -- Standalone procedures and functions NULL AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Standalone procedure or function' AS remarks, DECODE(object_type, 'PROCEDURE', 1, 'FUNCTION', 2, 0) AS procedure_type , NULL AS specific_name FROM all_objects WHERE (object_type = 'PROCEDURE' OR object_type = 'FUNCTION') AND owner LIKE :1 ESCAPE '/' AND object_name LIKE :2 ESCAPE '/' UNION ALL SELECT -- Packaged procedures -- object_name AS procedure_cat, owner AS procedure_schem, procedure_name AS procedure_name, NULL, NULL, NULL, 'Packaged procedure' AS remarks, 1 AS procedure_type , NULL AS specific_name FROM all_procedures WHERE procedure_name IS NOT NULL AND object_name IS NOT NULL AND owner LIKE :6 ESCAPE '/' AND procedure_name LIKE :7 ESCAPE '/' AND NOT EXISTS (SELECT NULL FROM all_arguments WHERE argument_name IS NULL AND in_out = 'OUT' AND data_level = 0 AND package_name IS NOT NULL AND owner LIKE :6 ESCAPE '/' AND object_name LIKE :7 ESCAPE '/' AND all_procedures.object_id = all_arguments.object_id AND all_procedures.subprogram_id = all_arguments.subprogram_id)UNION ALL SELECT -- Packaged functions package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Packaged function' AS remarks, 2 AS procedure_type , NULL AS specific_name FROM all_arguments WHERE argument_name IS NULL AND in_out = 'OUT' AND data_level = 0 AND package_name IS NOT NULL AND owner LIKE :6 ESCAPE '/' AND object_name LIKE :7 ESCAPE '/' ORDER BY procedure_schem, procedure_name

Thanks,

Narasimha 

https://docs.appian.com/suite/help/18.4/Database_Schema_Best_Practices.html#null-values

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

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?