One Star

[resolved] Get result of query as variable

I have a process that I'm running that will run a query against a database (imagine that). Though what I would like to do is run one query, then take the output from that (will be 1 row, 1 column) and load it into a variable that I can use in other queries.
Is there a component in Talend I can use for this or should I write my own custom java method?
- Peter S
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] Get result of query as variable

I discovered the answer to my own question, huzzah!
So for all the other folks out here that are as new to this as I am, here's how I did it...
1) Create a tmysqlinput with the query that returns the single result that you want
2) Drop a TSetGlobalVar component on the workspace
3) Draw your main link from the tmysqlinput to the TSetGlobalVar
4) Set a variable with the key name that you would like and give it the value of the "row" that come from the main link and the column name you gave it (see pic below)
You can now use this variable in sql queries by concatenating it into the query string. In this case it would look a little like...
"Start of query where stuff I'm looking for equals = " + ((String)globalMap.get("test")) + " End of my awesome query"
There are really very few reasons where I can see that you would like to use this instead of a look up or a join in your syntax, however in this case I'm working with an Infobright table with over 250 million rows. The table that I'm getting the variable from is a MYISAM table and no matter how you do the join, it will take a query that would have ran in 30 seconds and forces it to do a full table scan. Not good. So in this case this is incredbily handy. Way to go Talend guys!
- Peter S
2 REPLIES
One Star

Re: [resolved] Get result of query as variable

I discovered the answer to my own question, huzzah!
So for all the other folks out here that are as new to this as I am, here's how I did it...
1) Create a tmysqlinput with the query that returns the single result that you want
2) Drop a TSetGlobalVar component on the workspace
3) Draw your main link from the tmysqlinput to the TSetGlobalVar
4) Set a variable with the key name that you would like and give it the value of the "row" that come from the main link and the column name you gave it (see pic below)
You can now use this variable in sql queries by concatenating it into the query string. In this case it would look a little like...
"Start of query where stuff I'm looking for equals = " + ((String)globalMap.get("test")) + " End of my awesome query"
There are really very few reasons where I can see that you would like to use this instead of a look up or a join in your syntax, however in this case I'm working with an Infobright table with over 250 million rows. The table that I'm getting the variable from is a MYISAM table and no matter how you do the join, it will take a query that would have ran in 30 seconds and forces it to do a full table scan. Not good. So in this case this is incredbily handy. Way to go Talend guys!
- Peter S
One Star

Re: [resolved] Get result of query as variable

Thank you -- this is awesome... I was getting ready to try to figure out something like what you did for my own problem... Thanks again.