Return-Path: X-Original-To: apmail-camel-users-archive@www.apache.org Delivered-To: apmail-camel-users-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 388A2D147 for ; Thu, 7 Mar 2013 08:25:58 +0000 (UTC) Received: (qmail 83823 invoked by uid 500); 7 Mar 2013 08:25:57 -0000 Delivered-To: apmail-camel-users-archive@camel.apache.org Received: (qmail 83775 invoked by uid 500); 7 Mar 2013 08:25:56 -0000 Mailing-List: contact users-help@camel.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: users@camel.apache.org Delivered-To: mailing list users@camel.apache.org Received: (qmail 83759 invoked by uid 99); 7 Mar 2013 08:25:56 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 07 Mar 2013 08:25:56 +0000 X-ASF-Spam-Status: No, hits=2.8 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS,URI_HEX X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of jflebescont@gmail.com designates 209.85.214.182 as permitted sender) Received: from [209.85.214.182] (HELO mail-ob0-f182.google.com) (209.85.214.182) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 07 Mar 2013 08:25:49 +0000 Received: by mail-ob0-f182.google.com with SMTP id va7so145269obc.13 for ; Thu, 07 Mar 2013 00:25:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:in-reply-to:references:date:message-id :subject:from:to:content-type; bh=3N0i8ENeTJPnPwYQZnA0SiYm8d9rsdjw9xtVG5ulWnw=; b=Ed4PbPCwQEPulqqfJqM6fC6S1Oj8kHxOHZYd0aelXw+QkMAy0O4U5blIzCH7wvTYOa 5IxTi724Bt40tUEN7XayAhrHLUlejfGQ3aJrBqIE2ZSQfGl+iYIyXsWIU/Fp2b2IwvUV TWNqbSyhfD0YpIcCdnbAXlhvg31m8EsLhavNMKdjN6rD4cy4GyGNjKyBDwLGO8IwAmWR 5m/XPezrBiSaCVkTCfmOhr500EcNm4g3RGwPmI7bBlYZNDBN+/FKeP7JGvR8SaSwLzfy Zq7BRvlTHFxjNMCc77sh8cFlxmreloQNdFhJzoBKa6HoPDjcNeZ3u00+uNu+/Gx+qj8c tDdw== MIME-Version: 1.0 X-Received: by 10.182.122.41 with SMTP id lp9mr25621926obb.65.1362644728351; Thu, 07 Mar 2013 00:25:28 -0800 (PST) Received: by 10.60.141.73 with HTTP; Thu, 7 Mar 2013 00:25:28 -0800 (PST) In-Reply-To: References: <62222C31-6563-4671-8933-6A2F11FC3DCE@evosent.com> Date: Thu, 7 Mar 2013 09:25:28 +0100 Message-ID: Subject: Re: Enrich with data from SQL From: Jean Francois LE BESCONT To: users@camel.apache.org Content-Type: multipart/alternative; boundary=bcaec5396856fd128804d7517210 X-Virus-Checked: Checked by ClamAV on apache.org --bcaec5396856fd128804d7517210 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Sorry if my explanation are not good ... :) I have a csv file. I would like to process this file to enrich with data in sql table (then write enriched line in output). I process file with a binding in business class : .from("file://mycsv.csv") .split() .tokenize("\n") .streaming() .unmarshal() .bindy(BindyType.Csv, AED.class) Then I would like to enrich the line converted in AED to add element from a DB table : .enrich("direct:datasource", new AggregationEnrichissementZone()) The AggregationEnrichissementZone do my business transformation with both the current line and the content of the database which is in direct:datasource. It works no problem ! The direct:datasource is called correctly and retrieve the data from sql table. Initially I wrote it like this : from("direct:datasource") .setBody(constant("SELECT * FROM TEST")) .to("jdbc:dataSource") .to("direct:ds") .end(); But direct:datasource is called for each line which cost to much time. I would like to make the database query only once for my csv file. Thanks JF 2013/3/7 Claus Ibsen > On Thu, Mar 7, 2013 at 8:03 AM, Jean Francois LE BESCONT > wrote: > > it is a really simple studies case no ? I can't be the first guy who > > don't want to reload the datasource on each line isn't it ? > > > > I dont think people can understand what you try to do and pasting a > lot of code and with little detail to go along. > I suggest to debug your application and see what goes on and figure > out your solution. > > > > > > > 2013/3/6 Jean Francois LE BESCONT > > > >> I made a mistake in my test. It doesn't do what expected ... > >> > >> Still the same example : > >> > >> .from(...) > >> .split() > >> .tokenize("\n") > >> .streaming() > >> .unmarshal() > >> .bindy(BindyType.Csv, AED.class) > >> .enrich("direct:refreshReferentielDynamic", new > >> AggregationEnrichissementZone()) > >> > >> And a : > >> > >> from("direct:refreshReferentielDynamic") > >> .processRef("validatingProcessor") > >> > >> .end(); > >> > >> > >> And > >> > >> > >> public class ValidatingProcessor implements Processor { > >> > >> @Autowired > >> DriverManagerDataSource dataSource; > >> public void process(Exchange exchange) throws Exception { > >> > >> > >> Integer idxParse =3D (Integer) exchange.getProperty(Exchange.SPLIT_IND= EX); > >> if (idxParse !=3D null & idxParse =3D=3D 0) { > >> JdbcTemplate jdbcTemplate =3D new JdbcTemplate(dataSource); > >> List> ref =3D jdbcTemplate > >> .queryForList("SELECT ID , CODE , LIBELLE FROM ZONE_T "); > >> Map newRef =3D new HashMap(); > >> for (Map map : ref) { > >> int id=3D(Integer) map.get("ID"); > >> String code =3D(String) map.get("CODE"); > >> String libelle =3D(String)map.get("LIBELLE"); > >> newRef.put(code, new Zone(id,code,libelle)); > >> } > >> exchange.getOut().setBody(newRef) ; > >> > >> > >> } > >> } > >> > >> > >> The problem is that in my AggregationStrategy zones which is populate > >> before has data only for the first line ( the first spit index). > >> > >> > >> public class AggregationEnrichissementZone implements > AggregationStrategy { > >> > >> Logger LOG =3D > LoggerFactory.getLogger(AggregationEnrichissementZone.class); > >> > >> @Override > >> public Exchange aggregate(Exchange original, Exchange resource) { > >> > >> Map zones =3D (Map) > resource.getIn().getBody(); > >> > >> > >> Any idea ? > >> > >> > >> Thanks ! > >> > >> > >> > >> > >> 2013/3/6 Jean Francois LE BESCONT > >> > >>> Thanks Claus ! > >>> > >>> Tricky but it works :) > >>> > >>> > >>> 2013/3/6 Claus Ibsen > >>> > >>>> On Wed, Mar 6, 2013 at 1:05 PM, Jean Francois LE BESCONT > >>>> wrote: > >>>> > When we do an enrich, the *resourceUri ( in my example > >>>> *.enrich("direct:ds", > >>>> > new AggregationStrategyRessource())) is called every time. > >>>> > > >>>> > When I parse a file, big query with all the datasource while be > called > >>>> > every, is there a trick to tell the route to execute only one ? I > have > >>>> > watch camel cache but I have understand that it is used for > something > >>>> else. > >>>> > > >>>> > Thanks ! > >>>> > > >>>> > >>>> You can use a filter / content based route and only use the enrich t= he > >>>> 1st time. > >>>> When you use the splitter the exchange has a property with the split > >>>> index, so the 1st time the index is zero. > >>>> > >>>> The key for that property is Exchange.SPLIT_INDEX > >>>> > >>>> > > >>>> > > >>>> > > >>>> > 2013/3/6 Jean Francois LE BESCONT > >>>> > > >>>> >> Thanks Claus for the link ( have you an HashMap off the FAQ in th= e > >>>> head ? > >>>> >> :) > >>>> >> > >>>> >> Don't forget to add : > >>>> >> > >>>> >> > >>>> >> original.getOut().setHeaders(original.getIn().getHeaders()); > >>>> >> > >>>> >> > >>>> >> When enrich with strategy ! > >>>> >> > >>>> >> > >>>> >> 2013/3/6 Claus Ibsen > >>>> >> > >>>> >>> On Wed, Mar 6, 2013 at 10:26 AM, Jean Francois LE BESCONT > >>>> >>> wrote: > >>>> >>> > My mistake ...I used resource.getOut().getBody(); instead > >>>> >>> > od resource.getIn().getBody(); > >>>> >>> > > >>>> >>> > >>>> >>> Yeah see this FAQ > >>>> >>> > >>>> > http://camel.apache.org/using-getin-or-getout-methods-on-exchange.html > >>>> >>> > >>>> >>> > > >>>> >>> > public Exchange aggregate(Exchange original, Exchange > resource) { > >>>> >>> > > >>>> >>> > AED originalBody =3D (AED)original.getIn().getBody(); > >>>> >>> > Object resourceResponse =3D (Object) resource.getIn().getBody(= ); > >>>> >>> > > >>>> >>> > LOG.info("originalBody " + originalBody ) ; > >>>> >>> > LOG.info("resourceResponse " + resourceResponse ) ; > >>>> >>> > // do stuff with > >>>> >>> > > >>>> >>> > return resource; > >>>> >>> > } > >>>> >>> > > >>>> >>> > Thanks raul for the help ! :) > >>>> >>> > > >>>> >>> > > >>>> >>> > 2013/3/6 Jean Francois LE BESCONT > >>>> >>> > > >>>> >>> >> Thanks Raul ! > >>>> >>> >> > >>>> >>> >> I haven't found simple example enrich here ( > >>>> >>> >> http://camel.apache.org/content-enricher.html, > direct:resource in > >>>> >>> never > >>>> >>> >> explains ). Perharps that I haven't understand fondamental bu= t > >>>> it's a > >>>> >>> >> huge framework and learning needs time :) > >>>> >>> >> > >>>> >>> >> So I have a querie : > >>>> >>> >> > >>>> >>> >> from("direct:refreshReferentielDynamic") > >>>> >>> >> .setBody(constant("SELECT * FROM TEST")) > >>>> >>> >> .to("jdbc:dataSource") > >>>> >>> >> .to("direct:ds") > >>>> >>> >> .end(); > >>>> >>> >> > >>>> >>> >> Plus : > >>>> >>> >> > >>>> >>> >> from("direct:ds").to("log:dans.directds").end(); > >>>> >>> >> > >>>> >>> >> Otherwise it doesn't works. > >>>> >>> >> > >>>> >>> >> > >>>> >>> >> And an " main route " : > >>>> >>> >> > >>>> >>> >> from("file://....") > >>>> >>> >> .split() > >>>> >>> >> .tokenize("\n") > >>>> >>> >> .enrich("direct:ds", new AggregationStrategyRessource()) > >>>> >>> >> > >>>> >>> >> With : > >>>> >>> >> > >>>> >>> >> > >>>> >>> >> public class AggregationStrategyRessource implements > >>>> >>> AggregationStrategy { > >>>> >>> >> > >>>> >>> >> > >>>> >>> >> public Exchange aggregate(Exchange original, Exchange > >>>> resource) { > >>>> >>> >> > >>>> >>> >> return resource; // for the moment > >>>> >>> >> } > >>>> >>> >> > >>>> >>> >> } > >>>> >>> >> > >>>> >>> >> But original is always null ... > >>>> >>> >> > >>>> >>> >> Do you see with ? > >>>> >>> >> > >>>> >>> >> > >>>> >>> >> > >>>> >>> >> > >>>> >>> >> > >>>> >>> >> > >>>> >>> >> > >>>> >>> >> 2013/3/6 Jean Francois LE BESCONT > >>>> >>> >> > >>>> >>> >>> Hey ! > >>>> >>> >>> > >>>> >>> >>> A picture is sometime better than explication :) I try to > make a > >>>> >>> really > >>>> >>> >>> simple test ( equivalent of MAP with lookup in ETL) : > >>>> >>> >>> > >>>> >>> >>> > >>>> >>> >>> > >>>> ------------------------- > >>>> >>> >>> | TABLE TEST | > >>>> >>> >>> > >>>> ------------------------- > >>>> >>> >>> | COL1 | COL2 | > >>>> >>> >>> | jack | 2 > | > >>>> >>> >>> | bob | 3 > | > >>>> >>> >>> > >>>> ------------------------ > >>>> >>> >>> | > >>>> >>> >>> | > >>>> >>> >>> | > >>>> >>> >>> | > >>>> >>> >>> | > >>>> >>> >>> ---------------- > ---------------------- > >>>> >>> >>> ------------------------ > >>>> >>> >>> | FILE INPUT | | ENRICH ? | > >>>> >>> >>> | FILE OUTPUT | > >>>> >>> >>> ---------------- > ---------------------- > >>>> >>> >>> ----------------------- > >>>> >>> >>> | jack | cool | >>>>>>>>> | | > >>>>>>>>> > >>>> | > >>>> >>> jack > >>>> >>> >>> | cool | 2 | > >>>> >>> >>> | bob | foo | | > | > >>>> >>> >>> | bob | foo | 3 | > >>>> >>> >>> ---------------- > >>>> ----------------------- > >>>> >>> >>> ------------------------ > >>>> >>> >>> > >>>> >>> >>> > >>>> >>> >>> > >>>> >>> >>> > >>>> >>> >>> > >>>> >>> >>> > >>>> >>> >>> > >>>> >>> >>> 2013/3/6 Jean Francois LE BESCONT > >>>> >>> >>> > >>>> >>> >>>> If i do: > >>>> >>> >>>> public class AggregationStrategyRessource implements > >>>> >>> AggregationStrategy > >>>> >>> >>>> { > >>>> >>> >>>> > >>>> >>> >>>> @Override > >>>> >>> >>>> public Exchange aggregate(Exchange original, Exchange > >>>> resource) { > >>>> >>> >>>> int i =3D 1 / 0; > >>>> >>> >>>> return original; > >>>> >>> >>>> } > >>>> >>> >>>> } > >>>> >>> >>>> > >>>> >>> >>>> no exception are thows. > >>>> >>> >>>> > >>>> >>> >>>> > >>>> >>> >>>> > >>>> >>> >>>> 2013/3/6 Jean Francois LE BESCONT > >>>> >>> >>>> > >>>> >>> >>>>> thks Raul > >>>> >>> >>>>> > >>>> >>> >>>>> For the moment it's a simple : > >>>> >>> >>>>> > >>>> >>> >>>>> public class AggregationStrategyRessource implements > >>>> >>> >>>>> AggregationStrategy { > >>>> >>> >>>>> > >>>> >>> >>>>> @Override > >>>> >>> >>>>> public Exchange aggregate(Exchange original, Exchange > >>>> resource) > >>>> >>> { > >>>> >>> >>>>> return original; > >>>> >>> >>>>> } > >>>> >>> >>>>> > >>>> >>> >>>>> } > >>>> >>> >>>>> > >>>> >>> >>>>> If I put a debug point on it, it is never fired .... > >>>> >>> >>>>> > >>>> >>> >>>>> > >>>> >>> >>>>> > >>>> >>> >>>>> > >>>> >>> >>>>> > >>>> >>> >>>>> 2013/3/6 Ra=FAl Kripalani > >>>> >>> >>>>> > >>>> >>> >>>>>> Can we see the code of AggregationStrategyRessource? > >>>> >>> >>>>>> > >>>> >>> >>>>>> Regards, > >>>> >>> >>>>>> Ra=FAl. > >>>> >>> >>>>>> > >>>> >>> >>>>>> On Mar 5, 2013, at 23:30, Jean Francois LE BESCONT wrote: > >>>> >>> >>>>>> > >>>> >>> >>>>>> > Hey, > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > I have this > > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > >>>> >>> > >>>> > http://camel.465427.n5.nabble.com/Enrich-message-with-data-from-dynamic-s= ql-query-td5329427.html > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > But I haven't found a solution to my question. > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > How can enrich my stream with data from a queries ? > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > I have a a route called from the start : > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > from("direct:refreshReferentielDynamic") > >>>> >>> >>>>>> > // > >>>> >>> >>>>>> > .setBody(constant("SELECT * FROM TEST")) > >>>> >>> >>>>>> > // > >>>> >>> >>>>>> > .to("jdbc:dataSource") > >>>> >>> >>>>>> > // > >>>> >>> >>>>>> > .to("direct:ds") > >>>> >>> >>>>>> > // > >>>> >>> >>>>>> > .end(); > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > End I would like to acces data in memory in direct:ds > from > >>>> an > >>>> >>> other > >>>> >>> >>>>>> route. > >>>> >>> >>>>>> > I have try this : > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > from("foo"). > >>>> >>> >>>>>> > [...] > >>>> >>> >>>>>> > .enrich("direct:ds", new AggregationStrategyRessource()= ) > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > But code in AggregationStrategyRessource is not called > ... > >>>> and > >>>> >>> body > >>>> >>> >>>>>> is > >>>> >>> >>>>>> > replace by the content of the query. > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > Thks ! > >>>> >>> >>>>>> > > >>>> >>> >>>>>> > Thks ! > >>>> >>> >>>>>> > >>>> >>> >>>>>> > >>>> >>> >>>>> > >>>> >>> >>>> > >>>> >>> >>> > >>>> >>> >> > >>>> >>> > >>>> >>> > >>>> >>> > >>>> >>> -- > >>>> >>> Claus Ibsen > >>>> >>> ----------------- > >>>> >>> Red Hat, Inc. > >>>> >>> FuseSource is now part of Red Hat > >>>> >>> Email: cibsen@redhat.com > >>>> >>> Web: http://fusesource.com > >>>> >>> Twitter: davsclaus > >>>> >>> Blog: http://davsclaus.com > >>>> >>> Author of Camel in Action: http://www.manning.com/ibsen > >>>> >>> > >>>> >> > >>>> >> > >>>> > >>>> > >>>> > >>>> -- > >>>> Claus Ibsen > >>>> ----------------- > >>>> Red Hat, Inc. > >>>> FuseSource is now part of Red Hat > >>>> Email: cibsen@redhat.com > >>>> Web: http://fusesource.com > >>>> Twitter: davsclaus > >>>> Blog: http://davsclaus.com > >>>> Author of Camel in Action: http://www.manning.com/ibsen > >>>> > >>> > >>> > >> > > > > -- > Claus Ibsen > ----------------- > Red Hat, Inc. > FuseSource is now part of Red Hat > Email: cibsen@redhat.com > Web: http://fusesource.com > Twitter: davsclaus > Blog: http://davsclaus.com > Author of Camel in Action: http://www.manning.com/ibsen > --bcaec5396856fd128804d7517210--