One Star

How to use SQL expressions in tDBOutput or tJDBCOutput component?

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.
18 REPLIES
Four Stars

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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
One Star

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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.
Four Stars

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

"StringHandling.LEFT(col2,2)"
One Star

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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.
Four Stars

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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.
Four Stars

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

Whether your job is working without additional columns?
One Star

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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.
Four Stars

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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.
Four Stars

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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
One Star

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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?
Four Stars

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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.
Four Stars

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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!
One Star

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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

Re: How to use SQL expressions in tDBOutput or tJDBCOutput component?

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.