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
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?
Highlighted
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 ?
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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read