topic tDBInput query calling stored procedure with parameter from globalMap in Talend Studio

tDBInput query calling stored procedure with parameter from globalMap

BartG — Fri, 15 Nov 2024 22:12:21 GMT

Hi,

I used a tDBInput as a lookup query for a tMap.

tDBInput connects to a SQL Server via tDBConnection.

The query in tDBInput has to call a stored procedure with one string parameter. This works when I hardcode the parameter value. It doesn't work when I replace it with a reference to a global variable I set through tSetGlobalVar. There's no error, but I see no rows come back. When I use a SQL Profiler I see that the stored procedure is executed, but with an empty parameter string. So I'm not sure what happens...I suppose executing the query is not one of the first things TOS does, before I assign the value to the global variables in tJavaFlex1?

This is the hardcoded version that works:

"exec proc_EDA_prd_fuji_GetPanelLayout '13341201';"

This is the query I want to use that doesn't work (no error, just no result):

"exec proc_EDA_prd_fuji_GetPanelLayout '" + ((String)globalMap.get("inHeader_SerialNo_stripped")) + "';"

I'm new to TOS so probably I'm missing something obvious here...

Thanks!

Re: tDBInput query calling stored procedure with parameter from globalMap

Anonymous — Thu, 12 Jan 2023 04:24:22 GMT

Hello,

As this component is generally used as a one-component subJob, please use OnsubjobOk connection type to trigger your main job.

tSetGlobalVar-->onsubjobok-->tDBconnection

OnsubjobOK

twaitforfile-->tfileinputfullrow-->tjavaflex--.tjavarow-->tmap-->tjavaflex

(Look up tdbinput)

Best regards

Sabrina

Re: tDBInput query calling stored procedure with parameter from globalMap

Anonymous — Thu, 12 Jan 2023 05:53:23 GMT

In this tutorial topic we will cover how to create SQL Server stored procedures that use input parameters.

Re: tDBInput query calling stored procedure with parameter from globalMap

BartG — Thu, 12 Jan 2023 10:50:13 GMT

Hi, in what tutorial topic?

Re: tDBInput query calling stored procedure with parameter from globalMap

BartG — Thu, 12 Jan 2023 10:55:10 GMT

Hi,

I'm sorry but I don't understand how I should connect everything based on your feedback 😕

If I connect tSetGlobalVar --> tDBConnection --> tDBInput, I can't seem to connect tDBInput to tMap anymore to use it as a lookup?

Re: tDBInput query calling stored procedure with parameter from globalMap

Anonymous — Fri, 13 Jan 2023 08:39:05 GMT

Hello,

Please connect tSetGlobalVar --> tDBConnection and then trigger your main job.

Best regards

Sabrina

Re: tDBInput query calling stored procedure with parameter from globalMap

BartG — Fri, 13 Jan 2023 12:42:49 GMT

Hi Sabrina,

Sorry, I misunderstood. I now used your suggestion, but it still looks like the variable that is given to the stored procedure parameter is empty.

Like I said earlier: the variable is globalMap.get("inHeader_SerialNo_stripped") is being set in step "tJavaFlex1". I think the 5th line in the file contains a serial number that I need to pass to tDBInput as a parameter. So the variable "inHeader_SerialNo_stripped" will only be filled somewhere after processing a few lines of the input file.

How can I make sure tDBInput is only executed when "inHeader_SerialNo_stripped" is filled?

In fact, the flow could check for variable "inHeader_SerialNo_stripped" to be filled in tJavaFlex_1 and only pass through the lines once the variable is filled. The input file contains some header lines first that I need to process, but those lines are not necessary for tJavaRow_1 and what follows. So I need some kind of condition that can decide whether the flow can continue or not, per line.

I'll describe my input file a bit, I hope it makes things more clear...

* PASS *

Model:Bottom PAN.SWX2

Serial No.:13341201

DATE 15/11/2022 10:19:44

Test time:54 s

Test ID :8000

Index :TV007

01 "13341201" 000001 "IG1P1 " "PASS " "P-Auto " 400 409.5 0 +50% -50%

01 "13341201" 000002 "C9* " "PASS " "1.2uF " 1.1E-6 1.084E-6 0 +15% -15%

01 "13341201" 000003 "IG1P3 " "PASS " "P-Auto " 400 202.8 0 +50% -50%

01 "13341201" 000004 "C10* " "PASS " "440.47uF " 0.00044 0.000423 0 +20% -20%

...

So between "@B" and "@" are the header lines. Between "@" and "@E" are the body lines.

I use tJavaFlex_1 to parse the contents of the lines one by one, to see whether we're in the header or the body. When we're in the header, I fill some variables in my tSetGlobalVar_1 to use later on when writing my output files (tJavaFlex_5).

One of the variables in the header is "Serial No." as you can see. This is the variable I need to pass to tDBInput_1 to execute the stored procedure.

So tJavaFlex_1 makes sure the header lines go to tSetGlobalVar_1 to be used later on, and the body lines go further through the flow to tJavaRow_1 etc.

Thanks!