topic Re: Calling stored procedure with parameter from tSetGlobalVar in Talend Studio

Calling stored procedure with parameter from tSetGlobalVar

BartG — Fri, 15 Nov 2024 22:08:41 GMT

Hi all,

I use TOS to create a random number SessionID into a variable in my tSetGlobalVar, read a log file and write the log file data to a table in a SQL Server database. This works.

After that I want to call a stored procedure on SQL Server with that SessionID in tSetGlobalVar as a parameter. This stored procedure will parse and transform the data in the table, and return the data in an easy-to-process form for TOS to write to separate files again.

The problem is I don't see how I can pass this SessionID variable as a parameter for calling the stored prcedure.

In this schema I created a SessionID field, and I think this is the way to do it, but this is of course just a schema definition. It's not tied to the real SessionID variable from tSetGlobalVar yet. How do I pass this value to the stored procedure and execute it?

Thanks!

Re: Calling stored procedure with parameter from tSetGlobalVar

Anonymous — Fri, 03 Feb 2023 02:51:28 GMT

use a tFixedFlowInput to generate an input data flow that contains the SessionID data, define one column called 'SessionID' on the schema, and set its value as: (String)globalMap.get("keyName") in the 'Use Sing Table' model table.

...onsubjobok--tFixedFlowInput--main--tDataParseData...

Regards

Shong

Re: Calling stored procedure with parameter from tSetGlobalVar

BartG — Fri, 03 Feb 2023 11:12:39 GMT

Hi @Shicong Hong ,

Thanks for the advice to add tFixedFlowInput. I added this, but now I get an error about my recordset fields that come from the stored procedure not being found: "OutputLine cannot be resolved or is not a field". But I really can't see what is wrong.

I'll give you some screenshots to make things clear:

The error when I run:

This is what the stored procedure returns when I execute it in SSMS:

Re: Calling stored procedure with parameter from tSetGlobalVar

Anonymous — Fri, 03 Feb 2023 17:19:30 GMT

You don't set the value for each column to map the real field name on tParseRecordSet component, eg:

Column --> Value

OutputLine-->"OutputLine"

Re: Calling stored procedure with parameter from tSetGlobalVar

BartG — Tue, 07 Feb 2023 12:25:19 GMT

Thank you! That was less intuitive...I missed that one from the sentence "In the Attribute table area, fill in each Value column with the corresponding column name in the MS SQL Server table that holds the personal information." in the docs. It works now, thanks for your support!