How to use SQL expressions in tDBOutput or tJDBCOutput component?

[Anonymous]

Hello,
I need to apply an SQL function on inserting rows in tJDBCOutput component.
I have attached the screen shot of where I am looking for a solution.
I need to use an SQL function (for e.g. LEFT) and I want to pass parameter to this SQL function from Talend rows.
As per the documentation reference, I just need to place the reference column name as shown in the snapshot but it gives error during execution. How can I achieve this? Did anybody use additional columns with SQL expressions?
This is important and urgent for me. Please respond soon if you have any answer.

[Anonymous]

Yes.
I have tried both with Batch size and without it.
I tried this on talend 5.4.1 also as I have 5.3 version.

[Anonymous]

I am not sure what is the problem, but
Try small work around
- Use tJavaRow after tMap.. assign value if col2 to context variable
- Use "LEFT(context.variable,2)" in the expression in tJDBCOutput and check again
Someone from Talend will make the POC with Vertica and respond you again.
Vaibhav

[Anonymous]

Thanks for the suggestion.
-LEFT(context.variable, 2) is not working as usual. Talend takes context.variable as a literal
- I tried "LEFT(" + context.variable + ",2)". but it is not functioning properly as it should. Query generator replaces col2 value with null!.
Does it have to do with underlying database i.e. Vertica?

[Anonymous]

If it is giving you null means you are working on same column and reference column is also the same.

[Anonymous]

Yes. And technically and logically that should be possible. That is the reason we have REPLACE option along with AFTER and BEFORE in Additional Columns.

[Anonymous]

So,
Have you concluded any thing to resolve the problem? or still it is pending with some questions?
Vaibhav

[Anonymous]

No.
Nothing is working for me as of now. The illustration which you mention should work for me also, but unfortunately it is not working!

[Anonymous]

Bump!!!
Any body has any solution to the issue? If yes then please share it with me.

[Anonymous]

just another try.. create context variable as context.vQuery as string datatype and then take tJava component and write your SQL statement along with variable which carry the actual value.
print context.vQuery variable and check whether query has right values or not. if yes then pass same context.vQuery to db component to execute.
if values are wrong then check which value is coming wrong and then do the needful.
if you already tried this solution then ignore it.