stored procedure on button click does not work

Carolyn Anderson

I want a button which does a server-side operation to execute a stored procedure which deletes a row out of a custom table in my metastorm database, so I used the 'execute stored procedure' option in the designer. I'm passing in a number as a parameter, and I know the stored procedure exists. It all deploys ok, but when I press the button I get a message 'Failed to execute deployed method 'WhenChanged' using entity 'btnDelete', specified method is not supported.'

 

Does this not work, or is it something I'm doing wrong with it?

Jerome_Pearce

Given the fact that no database functions apart from SelectSql() support parameters, and the awful problems involved with formatting many paramaters correctly, we would SelectSql() for everything.

 

In this case, a C# code fragment would be:

SelectSql(null,"exec spAMLDeleteBOCompany @Param", SQLArg("@Param",1));

 

assuming SQL Server.

Carolyn Anderson

That works. Thanks Jerome.

Damjan Sabotin

Hello,

i have the exact same problem. I would like to call stored procedure on Oracle XE which takes one IN parameter.
So, on my button i have defined "Visual Scrip Event Handler" within this i'am using "ExecuteProcedureActivity" and my code is

SelectSql(new.DBConnection, "EXEC PROCEDURE_TEST @P_PARAMETER", SQLArg("@P_PARAMETER", Local.Variable_1))

The Local.Variable_1 is my local variable, which i have to pass to my stored procedure. SP is existing and it is working fine to! The eLog table is giving me this error: Failed to execute deployed method 'WhenChanged', using entity 'Button_Call_SP'.

Does have any idea, what i'm doing wrong.

Best regards,
Damjan

Louise_Caldicott

Hello,

in response to the first question from Carolyn, I'm concerned why you would want to remove entries from a custom table. I can't see a good reason for this as these entries are used and handled by the MBPM Engine. They are also related to entries in the eFolder table as these two tables are kept in sync with each other. This isn't normally supported behaviour. It's possible that you're planning to do this as part of an admin database tidy up task for archived items.

Regarding the question from Damjan about using parameters with Execute Procedure Activities and Oracle, it looks like there is some inconsistency of support here as there is a workaround for SQL Server but not for Oracle. The EXEC stored procedure syntax is not supported by Oracle outside SQL Plus. I also tried using the ODBC Call syntax, however since Oracle returns values from stored procedures using OUT parameters, I haven't found a way to call an Oracle Stored Procedure using SelectSql. It's possible to call an Oracle function, but this will not allow any DML when it is called from within a select statement.

It looks like Execute Procedure Activity does not currently support dynamic parameter values and I have raised this as a bug. We will look into this issue in a future release.

Thanks very much, best wishes,

Louise.

Brian Mellert

By "custom table" I believe Carolyn really means "Solution table" when looking at the designer. (They used to be called external tables in V7.) If that is indeed the case, or its a table not created/maintained via a MBPM solution, then the engine will not handle automatically. It has to be coded with either MBPM methods (DeleteDataRows(), etc.) or some other means.

If its process tables, those are maintained via MBPM -- unless as you indicate its for some sort of admin / clean up type of function.