Re: CamelSqlGeneratedKeyRows is not updated
This is a regression. I filed an issue
https://issues.apache.org/jira/projects/CAMEL/issues/CAMEL-17866.
On Fri, Mar 25, 2022 at 1:20 PM Karen Lease wrote:
> 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
> 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
> >> 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
> >> 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=.done=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 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
> >>
> >
>
Re: CamelSqlGeneratedKeyRows is not updated
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 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
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
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=.done=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
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
Re: CamelSqlGeneratedKeyRows is not updated
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 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
> 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
> 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=.done=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
> 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
>
RE: CamelSqlGeneratedKeyRows is not updated
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
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 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=.done=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
> 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
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 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=.done=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
> 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
Re: CamelSqlGeneratedKeyRows is not updated
FYI, I think I worked around this by removing the CamelSqlGeneratedKeyRows
header prior to a sql call.
On Wed, Mar 23, 2022 at 4:42 AM Reto Peter wrote:
> Ya sorry.
> Camel version: 3.15.0
>
> On 23 March 2022 17:03:19 Andrea Cosentino wrote:
>
> Report the camel version, please
>
> Il mer 23 mar 2022, 09:59 Reto Peter ha scritto:
>
> 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=.done=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
> 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
>
>
>
> Sent with Aqua Mail for Android<
> https://play.google.com/store/apps/details?id=org.kman.AquaMail>
> https://www.aqua-mail.com
>
Re: CamelSqlGeneratedKeyRows is not updated
Ya sorry.
Camel version: 3.15.0
On 23 March 2022 17:03:19 Andrea Cosentino wrote:
Report the camel version, please
Il mer 23 mar 2022, 09:59 Reto Peter ha scritto:
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=.done=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
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
Sent with Aqua Mail for
Android
https://www.aqua-mail.com
Re: CamelSqlGeneratedKeyRows is not updated
Report the camel version, please
Il mer 23 mar 2022, 09:59 Reto Peter ha scritto:
> 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=.done=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
> 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
>
CamelSqlGeneratedKeyRows is not updated
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=.done=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
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
