[resolved] Using Stored Procedures in Talend

[_AnonymousUser]

Hi,
I?m a beginner in using Talend software for ETL applications and have the following question:
Is there a thorough visual tutorial which explains how to call stored procedures and get the results back? I searched in Talend?s help but couldn?t find what I need.
I have a stored procedure which returns not only output parameters, but also a result set which contains many records and I couldn?t configure Talend?s components to support this stored procedure (MS SQL server stored procedures).
I really appreciate your help and thank you in advance.

[Anonymous · Accepted solution]

Hello
Here is an example about sp components.
https://community.talend.com/t5/Design-and-Development/Get-Stored-Procedure-return-Value/td-p/85353
If you return a recordset, you need a tParseRecordset to parse the recordset after sp components see
https://community.talend.com/t5/Design-and-Development/resolved-use-of-tsybasesp/td-p/106446
Best regards

shong

[_AnonymousUser]

Thank you Shong. That was helpful.

[Anonymous]

Hi Shong,
I can successfully retrieve the data from the two output variables after running my SP using tJDBCSP component (Passing three input variables).
Also, I can retrieve the data from the record set after running my SP using tJDBCSP component (Passing three input variables) with the help of tParseRecordset.
But my SP returns data in two output variables and a result set and the problem is, I can't use both the output variables and the record set together in tJDBCSP component. I use a tMap component to pass the record set to tParseRecordset and the output variable values to tLogRow but get a long list of error that starts with the following:
Exception in component tJDBCSP _4 com.microsoft.sqlserver.jdbc.SQLServerException: The index 6 is out of range
I would really appreciate it if you kindly let me know what I should do in order to retrieve both the output variables and record set data successfully when used together or if it is not possible.
I have attached the screenshot for you to check.
Best regards

[Anonymous]

Hi

I can't use both the output variables and the record set together in tJDBCSP component

Sorry, I don't understand what is your problem. Can you please upload a screenshot of the basic settings of tJDBCSP_4 component?
Shong

[Anonymous]

Sorry about the delay Shong. Please find the screenshot. I am trying to get a resultset in the result object and two outputs in the OUT variables but I can't do that. I can either use the output variables or the 'result' recordset but not both together.
Thanks