Hi I’m trying to Execute a SQL sproc that has 3 arguments. The return data is 1000’s of rows, multiple columns. so the purpose of this sproc is to read data. Is this possible with the Clover designer as I’m having a few issues trying to return this data. Do I need to have to update my sproc to set every field as an output parameter? or is there an easy way to do this that i’m missing? Thanks!
Hi Nick,
No need to update your stored procedure as output parameter are not mandatory for getting your result set. I attached a simple example graph that demonstrates how you can approach this challenge. Let me break it down for you below:
Phase 0:
DBExecute component will create a test table with 4 columns.
Phase 1:
1000 dummy records will be inserted into this test table that will contain unique record IDs.
Phase 2:
DBExecute will create a stored procedure (SP). The SP only accepts a single input parameter (productID). It is designed to return multiple records on 2 columns.
Phase 3:
Randomly generated productID will be sent as an input parameter of the SP. Note the syntax of SQL query where the question mark serves as a placeholder for the input parameter. Also note that “Call as stored procedure” checkbox is selected and “Query input parameter” is defined in the DBExecute component. The last important part is the “Result set output fields” setting where I configured the 2 field metadata corresponding with the expected SP output.
More details on how to call a stored procedure from CloverDX graph can be found in our documentation.
Note: The example is designed for MySQL DB. You might need to adjust the DB connection configuration to be able to run this examle on your end.
Regards,callingStoredProcedure.grf (5.7 KB)
Thanks Vladimir - based on your design then, I’m trying to use 3 input parameters, so inside my DBExecute to call the sproc I have:
SQL query: {call [dbo].[mysproc](?,?,?}}
Call as stored procedure: true
Query input parameters: 1:=${param1};2:=${param2};3:=${param3}.
However I get:
17:45:52,936 ERROR [JobFinalizer_50]
------------------------------------------------------------------ Error details -------------------------------------------------------------------
Component [Calling Stored Procedure with Input Parameter:CALLING_STORED_PROCEDURE_WITH_INPUT_PARAMETER] finished with status ERROR. (Out0: 0 recs)
The value is not set for the parameter number 1.
Hi Nick,
The error message indicates issues with fetching value of the first input parameter of the stored procedure. At the first glance, the input parameter reference does not seem to be quite right. Curly brackets are not part of the syntax in this case and presumably, CloverDX tries to resolve those values (e.g. ${param1}) as graph parameters.
Please try changing the Query input parameters reference to: 1:=$param1;2:=$param2;3:=$param3 or use the visual mapping tool that opens up when clicking on the drop down button. If this does not help my suggestion would be to log a regular support ticket with CloverDX Support and provide both the current version of your graph and the definition of the stored procedure you intend to call.
Regards,
-Vladi
Sorry yes - the parameters I’ve changed to be graph parameters as it looks like there is only 1 input port in the DBExecute so only 1 parameter I can use. So I’m wondering how else I can more than one value in?
Actually I answered my own question. Instead of using the Query input parameters, I set the SQL query as follows:
{call [dbo].[mysproc](${param1},${param2},${param3}}}
Thanks for your help!
Hi Nick,
Thank you for letting me know and thumbs up on finding the best approach that suits you by yourself.
Happy to help. Cheers,
-Vladi