Hi,
I was trying to get Employee related data by executing a stored procedure with the help of "Execute Stored Procedure" smart service in a process model. To get the data, in "Output" tab of Data I have selected a variable as Target variable and type of which is employee CDT. There is a section with the name "Expression". What should I write at "Expression"? Because at the time of publishing it is showing error as follows:
The Output tab:
Can any one please let me know what should I write at Expression box, so that I can get the employee data which is returned by the store procedure at Appian end?
Discussion posts and replies are publicly visible
Per the documentation provided with the plug-in:
In other words, you'll need a CDT that exactly matches the columns in the output data set, and an ACP that either matches an OUT parameter of the stored procedure or otherwise named like "resultSet1" etc.
Edit to add: an easier method to get and deal with the results of a stored-procedure backed query (assuming you're querying only, and not writing/modifying data) would be to put your SP read in an expression rule, and not use the Execute Stored Procedure process node at all. In the expression rule you'd use the executeStoredProcedure function which is a little more lenient in its output - it simply spits out a data dictionary that you can parse and then deal with as you like.
Thank you for your response.
Using executeStoredProcedure function I have able to get the result set. But the parameter inputs are not working and the procedure is returning all records as it returns the same if it's parameter values are null or not supplied.
I have executed the function like this:
executestoredprocedure( dataSourceName: "jdbc/DB_TEST", procedureName: "analytics.GetEmployee", inputs: { departmentId: 2, employeeId: 589 } )
It is returning all records ignoring the parameter values. Am I doing any mistake?
Can you please provide an example of calling this method using more than one parameters?
The inputs need to be a dictionary (i believe the plugin comes with a custom type "ProcedureInput"), with the parameters "name" and "value", where "name" exactly matches the procedure's input, and "value" contains the value being passed in.
Here's an example of one being used in my current system, to give you a more solid idea:
Thank you for your example. Now it is working fine.