Context variable in Repository Query

[Anonymous]

I am working on one issue where tOracleRow is used to execute Dynamic DML, query will be passed at run time so created a context variable to get the query and that context variable is used in tOracleRow...within that Query context variable...there is another context variable for eg:

String SQL Context Variable  - UpdateSQL

Value passed to Talend Jon - Insert into TableX (columnname) values (context.Value)

Here context.Value will be derived within the job.

In tOracleRow component, I am using context.UpdateSQL

Getting an error while running this job...my question is can I give context variable within other context variable.

[Anonymous]

You cannot do this as the context variable you specify will be treated as a literal. All that you are doing is manipulating Strings in Java. This is pretty straightforward and can be very powerful, but there are rules. You can get around this issue in a number of ways depending on how complicated your requirement is. For example, why does the Insert statement need to be held in a context variable? I can understand why the value and possibly the table, columns, etc, need to be dynamic, but there should be some static parts to your statement. If that is the case, it might be better for you to consider achieving this in slightly different way.....

"Insert into "+context.MyTable+" ("+context.MyColumn+") values ("+context.Value+")"

[Anonymous]

Thanks! Table Name and Column are static... I tried this but as I am trying to insert string values so getting an error of "column not defined here", I tried

"Insert into TableX (MyColumn) values ('"+context.Value+"')"

quotes before and after context.Value but it didn't work...in table value is inserted as "+context.Value+"

[Anonymous]

Please refer snapshots

Context.JPG
tOracleRow.JPG

[Anonymous]

This is because the whole of the text you have written is being treated as a literal String. The way to do this is to put the code you current have (where you are trying to set the context variable now) into the tOracleRow component. 

This ....

"Insert into TableX (MyColumn) values ('"+context.Value+"')"

....should be in the tOracleRow component

[Anonymous]

I was trying to make it dynamic...I mean there is another query...."INSERT INTO TableB (column1, column2) VALUES (context.column_1,context.column_2 )...for testing I was hardcoding the query in the job...In prod...query will be passed as external paramater in --contextparam value 

[Anonymous · Accepted solution]

You can make this as dynamic as you like.....but not the way you were trying. You will always be tripped up by th eproblem you have experienced with this. However, you have Java at your disposal and this is incredibly useful. This approach might suit you better....

Create a routine (Java class) that uses static methods to generate this functionality. You want to pass in the SQL as a parameter. The problem you have is that you do not know at design time which query you are passing in and how many dynamic values are needed. So why not build a method that will receive your SQL statement and carry out a replacement of values based on tokens. For example, if you pass in the following.....

"INSERT INTO [table] ([col1], [col2], [col3])
VALUES ([val1], [val2], [val3])"

...your method can replace text in "[" and "]" with corresponding values which can be supplied via a HashMap. Your job will simply have to be able to dynamically add the required values to a HashMap with the keys shown above (or whatever your choose).

That really wouldn't be a difficult solution to build and allows you to easily extend it without having to worry about multiple context variables.

[Anonymous]

Thanks a lot! I will try this.