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)
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:
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.
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
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
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.
© 2020 Appian. All rights reserved.