I am creating a custom JDBC activity to pass variables to a MSSQL stored procedure. The connection tests fine with the credentials and connection I've created and we are using Mid Servers to connect.
This is the definition of the stored procedure (as provided by the DBA):
and these are my inputs:
When I test the inputs, my raw output has no error messages, shows completed and I have auto-map to Local for the output. However, it appears that the stored procedure on the database is not receiving the values in the inputs when I test.
I have confirmed the inputs with the DBAs but I'm stymied on how to troubleshoot this next. I've combed through all the documentation, of course, but I'm not seeing a lot for setting up calls to store procedures and troubleshooting.
TIA!
I did finally get this working and will try to explain what we did (I had a third-party vendor assist with this, so I cannot take credit). My inputs are essentially the same as what I originally posted. On the execution command in the activity, notice they are all VARCHAR where I originally had some INTEGER.
I have no outputs or conditions defined in my activity.
I have a catalog item that provides the inputs and when that catalog item is invoked, the workflow runs a script to gather those variables and prepare them for the activity. I'll paste it here, though obviously you'll have your own inputs.
workflow.scratchpad.user = current.variables.requested_for.u_windowsusername;
workflow.scratchpad.Role_actuals = "0";
workflow.scratchpad.Role_actualsFrec = "0";
workflow.scratchpad.approvers = "";
if(current.variables.role_needed == "reporting"){
workflow.scratchpad.Role_actuals = "1";
}
else if(current.variables.role_needed == "forecast"){
workflow.scratchpad.Role_actualsFrec = "1";
}
//Get last approver
var grApprover = new GlideRecord("sysapproval_approver");
grApprover.addQuery("state", "approved");
grApprover.addQuery("document_id",current.sys_id);
grApprover.query();
while(grApprover.next()){
workflow.scratchpad.approvers = (workflow.scratchpad.approvers=="")?grApprover.approver.u_windowsusername : workflow.scratchpad.approvers + "," + grApprover.approver.u_windowsusername;
}
//Add Manager as approver
workflow.scratchpad.approvers = workflow.scratchpad.approvers +","+ current.variables.requested_for.manager.u_windowsusername;
workflow.info("Name: " + workflow.scratchpad.user);
workflow.info("Role_actuals: " + workflow.scratchpad.Role_actuals);
workflow.info("Role_actualsFrec: " + workflow.scratchpad.Role_actualsFrec);
workflow.info("approvers: " + workflow.scratchpad.approvers);
//get ticket number
var gr = new GlideRecord('sc_req_item');
gr.addQuery('request', current.request);
workflow.scratchpad.number = current.request.getDisplayValue();Then when I add my custom activity to the workflow, I can use the scratchpad variables as inputs:
Hope that helps! Feel free to ask more questions and I'll try to help, but like I said, I can't take credit for our final solution. 🙂
