Querry fails in cMessageEndpoint

Six Stars

Querry fails in cMessageEndpoint

Why does this querry fails with this error?

"sql:UPDATE TLD_VP_DIM_CONTRACTS SET TLD_INSERT_DATE = (CURRENT_TIMESTAMP) WHERE ID =:#CONTRACT_ID?dataSource=myDataSource"
, StackTrace: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "URRENT_TIMESTAMPHERE": invalid identifier

	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645)
...

But this querry doesnt fail:

"sql:UPDATE TLD_VP_DIM_CONTRACTS SET TLD_INSERT_DATE = (CURRENT_TIMESTAMP) WHERE ID =261?dataSource=myDataSource"

And the header in both cases

Headers: {breadcrumbId=ID:<xxx>, CamelSqlRowCount=1, CONTRACT_ID=261,

Accepted Solutions
Highlighted
Six Stars

Re: Querry fails in cMessageEndpoint

A collegue pointed out the solution. The problem were the empty spaces around '='.

All Replies
Nine Stars

Re: Querry fails in cMessageEndpoint

Hi,

 

Could you try this syntax ?

"sql:UPDATE TLD_VP_DIM_CONTRACTS SET TLD_INSERT_DATE = (CURRENT_TIMESTAMP) WHERE ID =:#${header.CONTRACT_ID}?dataSource=myDataSource"

 

Eric

Six Stars

Re: Querry fails in cMessageEndpoint

Fails with the same error.

Nine Stars

Re: Querry fails in cMessageEndpoint

It works on mine... My studio version is 6.2.1 and my camel version is 2.16.3. What are yours ?

Six Stars

Re: Querry fails in cMessageEndpoint

how do I see Camel Version?
Six Stars

Re: Querry fails in cMessageEndpoint

Talend 6.3.1
Nine Stars

Re: Querry fails in cMessageEndpoint

Regarding camel version, you can see it in module view (Pick up any camel module)

Six Stars

Re: Querry fails in cMessageEndpoint

Camel-aws-2.17.jar
Nine Stars

Re: Querry fails in cMessageEndpoint

What is your ID field type in database ? String ? Integer ?

Six Stars

Re: Querry fails in cMessageEndpoint

Number(8,0)
Six Stars

Re: Querry fails in cMessageEndpoint

Works

"sql:UPDATE TLD_VP_DIM_CONTRACTS SET TLD_INSERT_DATE = (CURRENT_TIMESTAMP) WHERE ID ='261'?dataSource=myDataSource"
"sql:UPDATE TLD_VP_DIM_CONTRACTS SET TLD_INSERT_DATE = (CURRENT_TIMESTAMP) WHERE ID =261?dataSource=myDataSource"

Doesn't work

"sql:UPDATE TLD_VP_DIM_CONTRACTS SET TLD_INSERT_DATE = (CURRENT_TIMESTAMP) WHERE ID =:#${header.CONTRACT_ID}?dataSource=myDataSource"
"sql:UPDATE TLD_VP_DIM_CONTRACTS SET TLD_INSERT_DATE = (CURRENT_TIMESTAMP) WHERE ID =:#CONTRACT_ID?dataSource=myDataSource"
Six Stars

Re: Querry fails in cMessageEndpoint

Is this a bug and something for Talend Support to solve ?
Highlighted
Six Stars

Re: Querry fails in cMessageEndpoint

A collegue pointed out the solution. The problem were the empty spaces around '='.
Moderator

Re: Querry fails in cMessageEndpoint

Hello,

Thanks for posting your solution here.

We have accepted your solution which allows others to see what has worked.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.