Context variable not being used in tmysqlinput

Highlighted
Six Stars

Context variable not being used in tmysqlinput

Hi,

I am trying to restrict the number of rows  by using context variable but it's sending all the rows from the table. All record are coming as output in query window too.

Kindly  help in this regardCapture.JPGCapture1.JPGCapture2.JPGCapture3.JPG.


Accepted Solutions
Community Manager

Re: Context variable not being used in tmysqlinput

Your query is wrong. You are building a SQL query using Java String manipulation. Your query should be more like this....

 

"SELECT  *  FROM  actor  where actor_id> '"+context.MAXID+"'"

I'd argue that you probably do not need the single quotes either, but that shouldn't cause a massive issue.


All Replies
Forteen Stars

Re: Context variable not being used in tmysqlinput

where are you storing into memory,in Talend job?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Community Manager

Re: Context variable not being used in tmysqlinput

I think I know what is wrong, but you haven't shown what I need to see. Can you show me the query as text (not a screenshot) and explain the flow of data. It looks like the order of processing is wrong. Your context variable when the query is processed and fired will always be 0 as it is not set until the end of the flow. You also have a timing issue with when queries are generated and when context variables are set. A rule of thumb is that if you want to use a context variable in a query, that it needs to be set in a prior subjob (or in a previous iteration).

Six Stars

Re: Context variable not being used in tmysqlinput

There is no other job associated with it . it's a single job. Capture.JPG

 Even I am able to print the MAXID coming from the variable. but issue is not restricting.

 

context.MAXID=input_row.MAXID;

>creating  variable 

 

"SELECT  IFNULL(MAX(actor_id),0) AS MAXID FROM actor  WHERE flag='Y'"

>This is to get the maximul actor_id from the table with flag='Y'

If table has 100 rows I need to show only 10 rows based on variable

 

"SELECT  *  FROM  actor  where actor_id> '+context.MAXID+' "

This is to restrict the rows.

 

Let me know if you need more info.

Thanks

 

Forteen Stars

Re: Context variable not being used in tmysqlinput

where are you loading MAXID into context?
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Community Manager

Re: Context variable not being used in tmysqlinput

Your query is wrong. You are building a SQL query using Java String manipulation. Your query should be more like this....

 

"SELECT  *  FROM  actor  where actor_id> '"+context.MAXID+"'"

I'd argue that you probably do not need the single quotes either, but that shouldn't cause a massive issue.

Forteen Stars

Re: Context variable not being used in tmysqlinput

you need to load MAXID into context using tcontextload.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: Context variable not being used in tmysqlinput

 I had that initially (extra " ") but removed , anyway changed query, it's working as expected.

Thanks a lot..

 

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch