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]

As per screenshot, your reference column and actual column names are same? is it the case then what is the use of additional column?
It it is true, then what is the name and use of additional column?
Vaibhav

[Anonymous]

My question is not about the use of additional column. My question is about how to use an SQL expression which uses an SQL function with parameter.
For your doubt: I want to apply an SQL function on col2 and use the output and don't want to use col2 coming from tMap. Please don't advise me to use the function in tMap itself as a few functions are available only in database and not in tMap.

[Anonymous]

"StringHandling.LEFT(col2,2)"

[Anonymous]

Thanks for your reply sanvaibhav, but I clearly asked not to advise me to use Talend functions in tMap.
I want to use an SQL function of the underlying database on incoming column.

[Anonymous]

What is the error that you are getting...
It works.. pl check the screenshot.
Vaibhav

[Anonymous]

Here is the error.
Basically, the SQL expression is not able to identify what the col2 is.
and I do have col2 declared in the Database tables.

[Anonymous]

Whether your job is working without additional columns?

[Anonymous]

Yes I have checked it/
The problem is with the query generated through Talend. With the use of this addional column, query generated for the insert is
INSERT INTO table_name (col1, col2) values(col1, LEFT(col2,2)).
Basically, Talend is taking literally whatever written between "" (double quotes) in SQL Expression field. Rather it should replace the col2 with a proper value.
Please let me know what query talend generates. You may check the MySQL logs to see the insert query.
Thanks again for your reply.

[Anonymous]

Sorry, I don't have logs configured with mysql.
Have you tried by enabled the batchsize checkbox?.
Vaibhav