I want to execute store procedure as soon as i called the rule and in this store procedure i am trying to update some of the field on the basis of conditions. At the very first time it is working perfectly fine but after 4-5 minutes something wired is happening. It is updating all the rows data and not considering the condition which we have written in the procedure. Example: SQL query which we are executing: "update projectProject set isEditMode = 1, isEditCurrentUser = @currentUser, isEditTimeStamp = GETDATE() where IDprojectProject = @projectId "
2nd time it is not considering the where condition and updating all the project.
I know it is already been written that executeStoredProcedureForQuery function could run more than once, do not use it with stored procedures that modify data to avoid unintentional changes but i am not able to find any other solution.
Below is the code which i am using in rule.
a!localVariables( local!data: a!executeStoredProcedureForQuery( dataSource: cons!PDB_DATA_SOURCE_NAME, procedureName: "spCheckAndUpdateIsEditProject", inputs: { a!storedProcedureInput( name: "projectId", value: ri!projectId ), a!storedProcedureInput( name: "currentUser", value: ri!currentUser ), a!storedProcedureInput( name: "isEdit", value: ri!isEdit ), /*a!storedProcedureInput(*/ /*name: "isError "*/ /*),*/ /*a!storedProcedureInput(*/ /*name: "userName "*/ /*)*/ } ), index(local!data, "results",{}) )
Below is the procedure screenshot where we are trying to execute.
Can we use 'executeStoredProcedureOnSave' in rule?
Please suggest how to execute procedure when the procedure is responsible to update the database also.
Discussion posts and replies are publicly visible