[CAMEL-20010] camel-sql - Can't change table name in JdbcMessageIdRepository by adding suffix/prefix

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.21.1, 4.0.1, 4.1.0
• Fix Version/s: 3.20.8, 3.21.2, 3.22.0, 4.0.2, 4.2.0
• Component/s: camel-sql

Environment

• Apache Camel 3.21.1
• Spring Boot 2.7
• PostgreSQL

• Estimated Complexity: Unknown
• Assignee: Unassigned
• Reporter: Bruno Mendola
• Created: 18/Oct/23 10:33
• Updated: 19/Oct/23 06:06
• Resolved: 18/Oct/23 15:50

Description

If I want to use JdbcMessageIdRepository and add the schema name as prefix to the CAMEL_MESSAGEPROCESSED table name, I can do something like this:

idempotentRepository.setTableName("myschema.CAMEL_MESSAGEPROCESSED");

(the idempotentRepository is registered as bean in the CamelContext).

But when using the idempotentRepository with an idempotentConsumer in a route, I get the following error:

org.apache.camel.RuntimeCamelException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SELECT 1 FROM myschema.myschema.CAMEL_MESSAGEPROCESSED WHERE 1 = 0]; SQL state [0A000]; error code [0]; ERROR: cross-database references are not implemented: "myschema.myschema.camel_messageprocessed"   Position: 15; nested exception is org.postgresql.util.PSQLException: ERROR: cross-database references are not implemented: "myschema.myschema.camel_messageprocessed"   Position: 15

The query is generated with a double schema name prefix (myschema.myschema.camel_messageprocessed).

JdbcMessageIdRepository is designed to replace the CAMEL_MESSAGEPROCESSED substring in the queries with the custom table name in the onInit() function, but looks like this function is called multiple times and it keeps replacing the substring that is present also in the custom table name.

Issue Links

• links to: GitHub Pull Request #11759