How to use executeStoredProcedureForQuery in an interface

Additionally, you have it in the "contents" parameter of your a!formLayout - which isn't really valid since contents expects one or many interface components (including sections, column layouts, fields, etc), but even if your stored procedure call worked, it would be spitting out plain data which is not a valid form content.  If you're curious, you could always set it as the "value" parameter of a ParagraphField and see what happens then.  the more common way to handle this, though, would be to set it to the value of a local variable, then display that in the value parameter of text / paragraph / grid / rich text field(s), as appropriate.

I have removed it from the content., Please, see code. Still have issues

  a!formLayout(
    label: "New Purchase Request",
    contents: {
             
          a!dateField(
            label: "payment Run Date",
            labelPosition: "ABOVE",
            value: rule!APN_replaceNull("05/01/2022",now()),
            saveInto: {}
          )
      
    },
    
    executestoredprocedure(
      datasourceName: "jdbc/DXXX",
      procedureName: "dbo.procFileProcessingLog",
      inputs: {
        {
          name: "date_param",
          value: {}
        }
      }
    )
    
    buttons: a!buttonLayout(
      primaryButtons: {
        a!buttonWidget(
          label: "Submit",
          saveInto: {},
          submit: true,
          style: "PRIMARY",
          /* Prevent form submission while a new address is being configured */
          
          validationGroup: "main"
        )
      }
 
    )
  )

The easiest way to handle that is to move the stored procedure call into the saveInto of a button or link (which only becomes enabled once the user has filled in the date you want to pass in), and calls the procedure (saving its returned data into the same local variable perhaps) instead of calling it immediately upon form load (which i initially assumed you were after, since you didn't specify this constraint either way).

I'm out of time tonight to work up a code example for this but it's not very difficult overall - and maybe one of the "night shift" guys will give it a shot.

this is what I am trying to achieve (this is triggered by a process model):

a!localVariables(

  local!procedureOutput: executestoredprocedure(
    datasourceName: "jdbc/Dxxx",
    procedureName: "dbo.procFileProcessingLog",
    inputs: {
      {
        name: "date_param",
        value: pv!paymentDate
      }
    }
  ),
  {
    a!dateField(
      label: "Payment Run Date",
      value: rule!APN_replaceNull(pv!paymentDate,now()),
      saveInto: pv!paymentDate),
      a!buttonLayout(
        primaryButtons: {
          a!buttonWidget(
            label: "Submit",
            saveInto: local!procedureOutput,
            submit: true,
            style: "PRIMARY"
          )
        },
        secondaryButtons: {

        }
      )
  }

)

Still getting errors.

Thanks for your help.

Can you clarify what you mean by "triggered by a process model"?  Is this an interface being used in a User Input Task?  If not, then your DateField component won't really do anything as it's only valid within an interface.

Please see highlighted image below:

the code is loaded in the Process Start Form of the process model. It pops up a form when the process is ran manually to allow the user enter the date.

Thanks

Gotcha.  You should put this in an interface, though - hardcoding the SAIL form directly into the process model like this will only cause you headaches in the future.

You will want your interface to work something like this:

a!formLayout(
  contents: {
    a!dateField(
      label: "Payment Run Date",
      value: ri!paymentDate,
      saveInto: ri!paymentDate
    )
  },
  
  buttons: a!buttonLayout(
    primaryButtons: {
      a!buttonWidget(
        label: "Submit",
        value: "Submit",
        submit: true(),
        disabled: a!isNullOrEmpty(ri!paymentDate),
        saveInto: {
          ri!buttonClicked,
          a!save(
            ri!procedureResult,
            executestoredprocedure(
              datasourceName: "jdbc/Dxxx",
              procedureName: "dbo.procFileProcessingLog",
              inputs: {
                { name: "date_param", value: ri!paymentDate }
              }
            )
          )
        }
      )
    }
  )
)

what does this part do:

ri!buttonClicked,

That saves the button's value into a rule input, to be passed into the process, in case it's needed.  In this case it's somewhat "extra", particularly since "submit" is the only button here, but if for example you added a "cancel" button, you could then start the process with an XOR gateway to judge whether the user had clicked "Cancel", and in that case, end the process straight away.  I decided to add it in my example just for completeness (best practices, etc).

Thanks Mike. I don't want the button to save any value; I just want the stored proc to be executed after the date is entered.

Thanks Mike. I don't want the button to save any value; I just want the stored proc to be executed after the date is entered.

In this example the stored procedure is run when the Submit button is pressed, as part of its SaveInto list - and the button becomes active only upon entry of a date.  The stored procedure output is saved into a Rule Input variable such that it can be passed out to your process instance.

If this doesn't sound like what you're after then you might need to provide some additional clarification as to what exactly you're trying to do with the result of your Stored Procedure call - since up until now I was under the assumption that you were trying to get the result of a stored procedure query to be used in some way in your process.

The stored proc simply clears a log on the DB based on the date; I am not expecting to get any result from the stored proc, other than success or failure.

Also, the date is saved in a process variable and it's used by other process nodes in the process model.

Thanks

The original rule you'd asked about was ExecuteStoredProcedureForQuery hence the confusion - this rule is designed to execute a stored procedure for data querying purposes and is restricted against executing any stored procedures that cause data changes, AFAIK.

You probably want to use the old Execute Stored Procedure process node (the one included in the plug-in since your appian version doesn't yet support the new smart service).  You would still require the user to enter the date in an interface and save it into a PV, and then you would use that date in the procedure inputs in the in-process node.

For this node, the configuration is just done (as far as I remember) by just adding node inputs named exactly after the procedure input (with the same data type) and passing your process value.