topic Re: Querry fails in cMessageEndpoint in Talend Studio

Querry fails in cMessageEndpoint

Anonymous — Sat, 16 Nov 2024 09:13:29 GMT

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,

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 06:57:49 GMT

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

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 07:28:14 GMT

Fails with the same error.

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 07:31:06 GMT

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

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 07:38:13 GMT

how do I see Camel Version?

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 07:40:24 GMT

Talend 6.3.1

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 07:51:05 GMT

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

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 07:58:17 GMT

Camel-aws-2.17.jar

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 09:08:16 GMT

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

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 11:06:05 GMT

Number(8,0)

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 11:12:13 GMT

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"

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 11:58:48 GMT

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

Re: Querry fails in cMessageEndpoint

Anonymous — Wed, 04 Oct 2017 14:38:44 GMT

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

Re: Querry fails in cMessageEndpoint

Anonymous — Fri, 20 Oct 2017 09:33:26 GMT

Hello,

Thanks for posting your solution here.

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

Best regards

Sabrina