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 problem here is you still have it as a member of your a!formLayout() call even though it's not a valid thing to put there.  Instead you should be setting the value to a local variable and then referencing that inside the contents.  As I mentioned before, the simplest way to do this is in the value of a paragraph field (just for preview/display purposes).

a!localVariables(

  local!procedureOutput:  executestoredprocedure(
      datasourceName: "jdbc/DXXX",
      procedureName: "dbo.procFileProcessingLog",
      inputs: {
        {
          name: "date_param",
          value: {}
        }
      }
    ),

  a!formLayout(
    label: "New Purchase Request",
    contents: {
      a!paragraphField(
        label: "EXAMPLE procedure output:",
        value: local!procedureOutput,
        readOnly: true()
      ),
             
          a!dateField(
            label: "payment Run Date",
            labelPosition: "ABOVE",
            value: rule!APN_replaceNull("05/01/2022",now()),
            saveInto: {}
          )
      
    },
    
    
    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"
        )
      }
    )
  )
)

Thanks Mike, But, I want to get the date from the form and pass it to the stored proc.

Thanks Mike, But, I want to get the date from the form and pass it to the stored proc.

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.