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
>
