[
https://issues.apache.org/jira/browse/CAMEL-16761?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17379099#comment-17379099
]
Avinash Dongre commented on CAMEL-16761:
----------------------------------------
Hi [~simply_govind], it seems like this is happening because the IN exchange is
constant in every loop and all the headers for the in exchange are getting
copied to the new (Out) exchange in the SqlProducer.
exchange.getOut().getHeaders().putAll(exchange.getIn().getHeaders());
[https://github.com/apache/camel/blob/master/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlProducer.java#L134]
As a workaround, you can simply remove the CamelSqlGeneratedKeyRows header
after each loop.
{code:java}
.process(ex ->
{
Integer outerIteration = (Integer) ex.getProperty(Exchange.LOOP_INDEX);
log.info("Iteartion : {}", outerIteration);
.....
.....
.....
ex.getMessage().setBody(iterator);
ex.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true);
// Add this line at the end
ex.getIn().removeHeader(SqlConstants.SQL_GENERATED_KEYS_DATA);
})
{code}
> camel-sql - auto-generated primary keys are not retuned for Postgres when
> using loop iteration
> ----------------------------------------------------------------------------------------------
>
> Key: CAMEL-16761
> URL: https://issues.apache.org/jira/browse/CAMEL-16761
> Project: Camel
> Issue Type: Bug
> Components: camel-sql
> Affects Versions: 3.10.0
> Environment: OS: Mac OS Catalina
> Camel: 3.10.0
> Postgresql: 12.5
> Reporter: Govind Singh
> Priority: Major
> Labels: batch, camel-sql, postgres, sql
> Fix For: 3.12.0
>
> Attachments: sample batch insert.png
>
>
> I am facing a strange behavior in Camel.
> The use-case is batch insert into a table (student) and then just log the
> autogenerated Primary keys from that table. Database is Postgres. Note to
> keep it simple, this table has 2 columns, id (auto-generatedprimary key) &
> name (varchar 256). The (simplified) code is:
>
>
> \{{}}
> {code:java}
> from("direct:test_autogenerated_pk")
> .log("test autogenerated pk")
> .loop(3)
> .process(ex ->
> {
> Integer outerIteration = (Integer) ex.getProperty(Exchange.LOOP_INDEX);
> log.info("Iteartion : {}", outerIteration);
> final var iterator = IntStream.range(0, 2).boxed().map(x -> {
> final var query = new HashMap<String, Object>();
> Integer innerIteration = x.intValue();
> query.put("studentName", "abc_" + outerIteration + "_" + innerIteration);
> return query;
> }).iterator();
> ex.getMessage().setBody(iterator);
> ex.getIn().setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, true);
> })
> .to("sqlComponent:INSERT INTO student (name) VALUES
> (:#studentName);?batch=true")
> .setProperty("insertedIds", simple("${headers.CamelSqlGeneratedKeyRows}"))
> .process(ex ->
> {
> ArrayList<LinkedCaseInsensitiveMap> metadataList =
> (ArrayList<LinkedCaseInsensitiveMap>)ex.getIn().getExchange().getProperty("insertedIds");
>
> for (LinkedCaseInsensitiveMap metadata : metadataList)
> {
> Integer id = (Integer) metadata.get("id");
> log.info("student ID: {}", id);
> }
> })
> .end();{code}
> {{}}
> To keep the example simple, we iterate 3 times batch inserting 2 rows per
> iteration.
> Expected behavior: Every (batch) insert returns newly auto-generated Primary
> keys per iteration
> Actual behavior: Only the auto-generated primary keys in the first iteration
> are returned in every iteration
> Related logs:
>
> \{{}}
> {code:java}
> [INFO ] 2021-06-28 10:45:35.991 [Test worker] MyRouteBuilder - Iteartion : 0
> [INFO ] 2021-06-28 10:45:36.158 [Test worker] MyRouteBuilder - student ID:
> 8480
> [INFO ] 2021-06-28 10:45:36.159 [Test worker] MyRouteBuilder - student ID:
> 8481
> [INFO ] 2021-06-28 10:45:36.159 [Test worker] MyRouteBuilder - Iteartion : 1
> [INFO ] 2021-06-28 10:45:36.166 [Test worker] MyRouteBuilder - student ID:
> 8480
> [INFO ] 2021-06-28 10:45:36.166 [Test worker] MyRouteBuilder - student ID:
> 8481
> [INFO ] 2021-06-28 10:45:36.166 [Test worker] MyRouteBuilder - Iteartion : 2
> [INFO ] 2021-06-28 10:45:36.174 [Test worker] MyRouteBuilder - student ID:
> 8480
> [INFO ] 2021-06-28 10:45:36.175 [Test worker] MyRouteBuilder - student ID:
> 8481{code}
> {{}}
> ID 8480 & 8481 are the auto-genearated primary keys in the first iteration.
> Note: Auto generated PK's do work, if I don't use the batch insert logic,
> that is, without an Iterator and inserting only 1 row per iteration. But that
> is of course, extremely slow.
> Any suggestion/help here ?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
