Hi Ronny, yeah, I just tried to explain what happens, and you can reason for yourself by looking at the source code I linked to, so if you don't specify `CamelSqlRetrieveGeneratedKeys` header and the PreparedStatement::execute returns false (like for INSERT statement) then output message is not created by the SQL component at all. This means that by default Camel exchange will copy the incoming message to the output message.
Now what is copied in all cases from the incoming message to the outgoing message is headers and attachments, so maybe you could put a processor before the SQL endpoint and set the header or attachment of the incoming message to the body of the incoming message, and then after the SQL endpoint set it back to the body? But, this seams to me to be the case for the recepient list EIP whereby you would send the same message to two endpoints. Oh, and yeah, the data change tables are quite powerful feature of some databases, and you can have INSERTs and UPDATEs return ResultSets, quite nifty if you want atomicity without transactions, but it might not be applicable to your use case, zoran On Fri, Mar 31, 2017 at 1:53 PM, Ronny Aerts <ronny.ae...@intris.be> wrote: > Hello, > > Even if my sql insert would return a resultset (which looks strange to me for > an insert), I don't need it because I still need to have the original body. > Therefore I tried with the outputHeader option not to overwrite the body. > > I repeat that there is a difference when working with the > CamelSqlRetrieveGeneratedKeys or not. When the header is set, the body is > null. When CamelSqlRetrieveGeneratedKeys is not set, the body is kept. > > -- > vriendelijke groeten, > Ronny Aerts – Intris nv – Wapenstilstandlaan 47, 2600 Berchem, België > Product Owner Integrations / Integrations Manager > Prince II certified – ITIL certified > Tel: +32-3-326.50.75 > > -----Original Message----- > From: Zoran Regvart [mailto:zo...@regvart.com] > Sent: vrijdag 31 maart 2017 12:14 > To: users@camel.apache.org > Subject: Re: camel sql insert clear body with CamelSqlRetrieveGeneratedKeys > and MariaDB > > Hi Ronny, > from what I can see from the SqlProducer source code the body is set if the > PreparedStatement::execute method returns `true`[1]. I could be reading this > wrong, but to me it seems that you're using insert/update statement that does > not return a ResultSet, so in that case the body will be null. > > Some databases support a feature called data change tables (well, DB2 and > MSSQL at least), so you could do something like: `SELECT FROM FINAL TABLE > (INSERT ...)` in MariaDB?. > > But if you wanted the same message delivered to multiple endpoints, perhaps > you are looking for the recipient list EIP[2]? > > zoran > > [1] > https://github.com/apache/camel/blob/master/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlProducer.java#L161 > [2] https://camel.apache.org/recipient-list.html > > On Fri, Mar 31, 2017 at 10:56 AM, Ronny Aerts <ronny.ae...@intris.be> wrote: >> Hello integrations community, >> >> I have a route (camel 2.17.6) where I need to insert a record into a MariaDB >> database and I need to capture the generated id of the record. I therefore >> set the CamelSqlRetrieveGeneratedKeys header to true. The record is inserted >> correctly and the id generated BUT the content of the body becomes null. How >> can I prevent to have the null AND returning the keys? >> >> The body is not cleared when I don't set the CamelSqlRetrieveGeneratedKeys >> header but then I don't have the id of the record. >> >> I already tried with the outputHeader and noop=true but this didn't help. >> >> -- >> kind regards, >> Ronny Aerts<mailto:ronny.ae...@intris.be> - Intris nv - >> Wapenstilstandlaan 47, 2600 Berchem, België Product Owner Integrations >> / Integrations Manager Prince II<http://nl.wikipedia.org/wiki/PRINCE2> >> certified - >> ITIL<http://nl.wikipedia.org/wiki/Information_Technology_Infrastructur >> e_Library> certified >> Tel: +32-3-326.50.75 >> >> [https://s19.postimg.org/3pi2w9i2b/BBEU_email_Intris_nv.jpg]<http://ww >> w.breakbulk.com/events/breakbulk-europe/breakbulk-europe-2017/register >> /> >> <http://www.breakbulk.com/events/breakbulk-europe/breakbulk-europe-201 >> 7/register/> >> >> >> Intris nv >> Wapenstilstandlaan 47 >> B-2600 Berchem Tel. +32 3 326 50 75 >> Fax +32 3 326 42 23 >> www.intris.be<http://www.intris.be/> >> DISCLAIMER >> This is an e-mail from Intris. The information contained in this >> communication is intended solely for use by the individual or entity to whom >> it is addressed. >> Use of this communication by others is prohibited. If the e-mail message was >> sent to you by mistake, please notify >> supp...@intris.be<mailto:supp...@intris.be>, destroy it without reading, >> using, copying or disclosing its contents to any other person. >> We accept no liability for damage related to data and/or documents which are >> communicated by electronic mail. > > > > -- > Zoran Regvart > [https://s19.postimg.org/3pi2w9i2b/BBEU_email_Intris_nv.jpg]<http://www.breakbulk.com/events/breakbulk-europe/breakbulk-europe-2017/register/> > > <http://www.breakbulk.com/events/breakbulk-europe/breakbulk-europe-2017/register/> > > > Intris nv > Wapenstilstandlaan 47 > B-2600 Berchem Tel. +32 3 326 50 75 > Fax +32 3 326 42 23 > www.intris.be<http://www.intris.be/> > DISCLAIMER > This is an e-mail from Intris. The information contained in this > communication is intended solely for use by the individual or entity to whom > it is addressed. > Use of this communication by others is prohibited. If the e-mail message was > sent to you by mistake, please notify > supp...@intris.be<mailto:supp...@intris.be>, destroy it without reading, > using, copying or disclosing its contents to any other person. > We accept no liability for damage related to data and/or documents which are > communicated by electronic mail. -- Zoran Regvart
