executeStoredProcedureForQuery executing more than once in the background.

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

Parents
  • If this is a local variable being used on an interface, then by default the local variable will be set to "refresh on referenced variable change", which means that if anything in the interface causes any changes to any of the 3 rule inputs you feed into the local variable, it will re-evaluate.

    The first thing you can try in this case is to turn "refresh on referenced variable change" off in the local variable by utilizing a!refreshVariable() and passing "false()" to the relevant parameter.  That would ensure, at least, that no accidental re-evaluations are occuring because of this particular cause.

    Without more details about exactly how this call is being used and in what context, that's about all I can offer to you at this point.

Reply
  • If this is a local variable being used on an interface, then by default the local variable will be set to "refresh on referenced variable change", which means that if anything in the interface causes any changes to any of the 3 rule inputs you feed into the local variable, it will re-evaluate.

    The first thing you can try in this case is to turn "refresh on referenced variable change" off in the local variable by utilizing a!refreshVariable() and passing "false()" to the relevant parameter.  That would ensure, at least, that no accidental re-evaluations are occuring because of this particular cause.

    Without more details about exactly how this call is being used and in what context, that's about all I can offer to you at this point.

Children
No Data