[jira] [Commented] (CAMEL-16761) camel-sql - auto-generated primary keys are not retuned for Postgres when using loop iteration

["Govind Singh (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CAMEL-16761?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17410535#comment-17410535 ] 

Govind Singh commented on CAMEL-16761:
--------------------------------------

Thanks [~avi5kdonrh] & [~klease78] for your respective comments. I have followed the solution by Karen of using copy() on the loop() command. This issue is resolved. Thanks everyone :) 

> 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)