Re: CamelSqlGeneratedKeyRows is not updated

[Karen Lease]

Hi,

There was a similar issue a while back (CAMEL-16761). In that case, the 
issue occurred in a loop but I think the root cause is the same.
The solution suggested by Jeremy should work; you need to insert
.removeHeader("CamelSqlGeneratedKeyRows")
before your second ".to("sql:INSERT...").
Regards,
Karen


On 24/03/2022 20:07, Jeremy Ross wrote:
To clarify, I'm recommending *clearing* the CamelSqlGeneratedKeyRows header
prior to the sql call.

On Wed, Mar 23, 2022 at 9:34 PM Reto Peter <reto.pe...@advanceit.ch> wrote:

Hi

I tried both with no success
a) it works as normal, I get the keys back if I only run the 2nd select
b) I set the Header before first statement and before second statement but
no change.

-----Original Message-----
From: Claus Ibsen <claus.ib...@gmail.com>
Sent: Thursday, March 24, 2022 3:51 AM
To: users@camel.apache.org
Subject: Re: CamelSqlGeneratedKeyRows is not updated

Hi

a)
Can you try with just 1 sql insert,
            .to("sql:INSERT INTO message_out(identifier) VALUES (1)")

It may be that this SQL does not return any generated keys


b)
Can you try to set the header to get keys before calling the 2nd insert
  .setHeader("CamelSqlRetrieveGeneratedKeys").simple("true")

On Wed, Mar 23, 2022 at 9:59 AM Reto Peter <reto.pe...@advanceit.ch>
wrote:

Hi

I have a route with 2 following SQL INSERT commands.
One inserts into table A (CamelSqlGeneratedKeyRows is correct) Next
inserts into table B (CamelSqlGeneratedKeyRows is wrong, the value is
not updated, it remains the same)

I am using mySQL and Java 11

My simplified route:

from("file:{{dir.from.flowline.test}}?preMove=.inprogress&move=.done&delay=1000")
                     .tracing()
                     .log("Processing file: ${file:name}")
                     .convertBodyTo(String.class)

  .setHeader("CamelSqlRetrieveGeneratedKeys").simple("true")
                     .to("sql:INSERT INTO files_from_partner(filename,
filecontent, partner_id) VALUES (:#${file:name},:#${body}, 1)")
                     .log("${headers.CamelSqlGeneratedKeyRows}")
                     .to("sql:INSERT INTO message_out(identifier) VALUES
(1)")
                     .log("${headers.CamelSqlGeneratedKeyRows}");

Output when I process 1 files is:
Processing file: GDELR_1848540_BL-V2100029_55234 use it 4 TESTS II.xml
[{GENERATED_KEY=58}] correct [{GENERATED_KEY=58}] wrong

Do I do something wrong, is it a bug?
I also tried to set the Header CamelSqlGeneratedColumns as follows:
@Component
class SetFilesFromPartnerKeyColumnsBean {
        public void setKeys(String message, @Headers Map<String, Object>
outHeaders) {
              outHeaders.put("CamelSqlGeneratedColumns", new String[]
{"files_id"});
        }
} And before executing the SQL INSERT in the route:
.bean(setFilesFromPartnerKeyColumnsBean)
But there was no effect, same results

MY workaround is with the "SELECT LAST_INSERT_ID()" which works but of
course not the way we should ...

Regards Reto



--
Claus Ibsen
-----------------
http://davsclaus.com @davsclaus
Camel in Action 2: https://www.manning.com/ibsen2