You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by Christian Stark <st...@gmail.com> on 2017/11/13 12:41:47 UTC
Need Example, use result from SQL-Query as input into Second Database/Table
Hello,
Assuming the following example: I'd like to know how to control/manipulate
the variables to insert the input from the first query from table/database1
into the second table/database2.
What is the best option to manipulate $input1,$input2,$input3
Do we need a transformer or a converter?
<route id="demo_route">
<from id="route-timer" uri="timer:foo?period=20000"/>
<to id="_to1" uri="sql:SELECT column1,column2,column3 FROM
testtable where key = ':3685127';?dataSource=myDataSource"/>
<to id="_to2" uri="sql:INSERT INTO USERS
(column1,column2,column3) values
('$input1,$input2,$input3)?dataSource=myDataSource2"/>
</route>
Any input is appreciated
Thanks
Christian
Re: Need Example, use result from SQL-Query as input into Second Database/Table
Posted by Christian Stark <cs...@redhat.com>.
Hello Claus,
This was exactly what I needed
Thank you so much
Christian
On Tue, Nov 14, 2017 at 12:05 PM, Claus Ibsen <cl...@gmail.com> wrote:
> On Tue, Nov 14, 2017 at 11:53 AM, Christian Stark <st...@gmail.com>
> wrote:
> > Thanks Claus
> >
> > I found the following example very helpful.
> > The SQL returns multiple rows and I need to map the Columns to other
> > Objects.
> >
> > http://www.javarticles.com/2015/07/apache-camel-jdbc-select-example.html
> >
> >
> > The only thing I need to find out is how to reuse the Output of my
> > Transformer Class as Input for the Insert.
> >
>
> What information from the Article java class do you want to store in
> the SQL insert?
>
> You are doing now: NSERT INTO USERS (column1) values(*${body[0]})
>
> Where body[0] means that you access a java.util.list or array and grab
> the first element.
>
> Instead you may want to call a getter/property on the Article class,
> for example the getAuthor method
>
> INSERT INTO USERS (column1) values(${body.getAuthor()})
>
> And if its a getter you can use a short-hand version
>
> INSERT INTO USERS (column1) values(${body.author})
>
> And you may need to add single quotes around the values, as if its a
> varchar/string type, eg to be SQL compliant
>
> INSERT INTO USERS (column1) values('${body.author'})
>
>
>
>
>
> >
> >
> > <camelContext id="camelContext-1b6e9b83-bc33-4311-9e9b-0f2458fcedd9"
> xmlns="
> > http://camel.apache.org/schema/spring">
> > <route id="_route1">
> > <from id="_from1" uri="timer://timer1?period=2s"/>
> > <setBody id="_setBody1">
> > <constant>
> > select user_nm,email_id from drupal_users where
> user_id
> > < 5000
> > </constant>
> > </setBody>
> > <to id="_to1" uri="jdbc:myDataSource"/>
> > <split id="_split1">
> > <simple>${body}</simple>
> > <to uri="bean:rowProcessor" />
> > <to uri="stream:out"/>
> > <setBody id="_setBody2">
> > <simple>INSERT INTO USERS (column1)
> values(*${body[0]})*
> > </simple>
> > </setBody>
> > <to id="_to3" uri="jdbc:myDataSource2"/>
> > </split>
> > </route>
> > <bean class="org.mycompany.MyTransformer" id="rowProcessor"/>
> >
> >
> > import java.util.Map;
> >
> > import org.apache.camel.Exchange;
> > import org.apache.camel.Processor;
> >
> > public class MyTransformer implements Processor {
> >
> > public void process(Exchange exchange) throws Exception {
> > Map<String, Object> row = exchange.getIn().getBody(Map.class);
> > System.out.println("Processing " + row);
> > Article article = new Article();
> >
> > article.setAuthor((String) row.get("email_id"));
> >
> >
> > *exchange.getOut().setBody(article);*
> >
> > }
> >
> >
> >
> >
> >
> >
> > 2017-11-13 13:48 GMT+01:00 Claus Ibsen <cl...@gmail.com>:
> >
> >> Hi
> >>
> >> If the 1st query return only 1 row, then you can set
> outputType=SelectOne
> >>
> >> Then the returned value is a Map
> >> Then you can use the column names as-is in the 2nd query
> >>
> >> See the docs at
> >> https://github.com/apache/camel/blob/master/components/
> >> camel-sql/src/main/docs/sql-component.adoc
> >>
> >> On Mon, Nov 13, 2017 at 1:41 PM, Christian Stark <st...@gmail.com>
> >> wrote:
> >> > Hello,
> >> >
> >> > Assuming the following example: I'd like to know how to
> >> control/manipulate
> >> > the variables to insert the input from the first query from
> >> table/database1
> >> > into the second table/database2.
> >> > What is the best option to manipulate $input1,$input2,$input3
> >> >
> >> > Do we need a transformer or a converter?
> >> >
> >> >
> >> > <route id="demo_route">
> >> > <from id="route-timer" uri="timer:foo?period=20000"/>
> >> > <to id="_to1" uri="sql:SELECT column1,column2,column3 FROM
> >> > testtable where key = ':3685127';?dataSource=myDataSource"/>
> >> >
> >> > <to id="_to2" uri="sql:INSERT INTO USERS
> >> > (column1,column2,column3) values
> >> > ('$input1,$input2,$input3)?dataSource=myDataSource2"/>
> >> > </route>
> >> >
> >> >
> >> >
> >> > Any input is appreciated
> >> >
> >> >
> >> > Thanks
> >> > Christian
> >>
> >>
> >>
> >> --
> >> Claus Ibsen
> >> -----------------
> >> http://davsclaus.com @davsclaus
> >> Camel in Action 2: https://www.manning.com/ibsen2
> >>
>
>
>
> --
> Claus Ibsen
> -----------------
> http://davsclaus.com @davsclaus
> Camel in Action 2: https://www.manning.com/ibsen2
>
--
Beste Grüsse / Bien cordialement / Kind Regards,
*Christian Stark*
Technical Account Manager
Red Hat GmbH
Werner-von-Siemens-Ring 14, 85630 Grasbrunn
Phone: +4989205071106
Mobile: +4915161659068
Email: christian.stark@redhat.com
Video: bluejeans.com/9885521768 <https://redhat.bluejeans.com/9885521768/>
Re: Need Example, use result from SQL-Query as input into Second Database/Table
Posted by Claus Ibsen <cl...@gmail.com>.
On Tue, Nov 14, 2017 at 11:53 AM, Christian Stark <st...@gmail.com> wrote:
> Thanks Claus
>
> I found the following example very helpful.
> The SQL returns multiple rows and I need to map the Columns to other
> Objects.
>
> http://www.javarticles.com/2015/07/apache-camel-jdbc-select-example.html
>
>
> The only thing I need to find out is how to reuse the Output of my
> Transformer Class as Input for the Insert.
>
What information from the Article java class do you want to store in
the SQL insert?
You are doing now: NSERT INTO USERS (column1) values(*${body[0]})
Where body[0] means that you access a java.util.list or array and grab
the first element.
Instead you may want to call a getter/property on the Article class,
for example the getAuthor method
INSERT INTO USERS (column1) values(${body.getAuthor()})
And if its a getter you can use a short-hand version
INSERT INTO USERS (column1) values(${body.author})
And you may need to add single quotes around the values, as if its a
varchar/string type, eg to be SQL compliant
INSERT INTO USERS (column1) values('${body.author'})
>
>
> <camelContext id="camelContext-1b6e9b83-bc33-4311-9e9b-0f2458fcedd9" xmlns="
> http://camel.apache.org/schema/spring">
> <route id="_route1">
> <from id="_from1" uri="timer://timer1?period=2s"/>
> <setBody id="_setBody1">
> <constant>
> select user_nm,email_id from drupal_users where user_id
> < 5000
> </constant>
> </setBody>
> <to id="_to1" uri="jdbc:myDataSource"/>
> <split id="_split1">
> <simple>${body}</simple>
> <to uri="bean:rowProcessor" />
> <to uri="stream:out"/>
> <setBody id="_setBody2">
> <simple>INSERT INTO USERS (column1) values(*${body[0]})*
> </simple>
> </setBody>
> <to id="_to3" uri="jdbc:myDataSource2"/>
> </split>
> </route>
> <bean class="org.mycompany.MyTransformer" id="rowProcessor"/>
>
>
> import java.util.Map;
>
> import org.apache.camel.Exchange;
> import org.apache.camel.Processor;
>
> public class MyTransformer implements Processor {
>
> public void process(Exchange exchange) throws Exception {
> Map<String, Object> row = exchange.getIn().getBody(Map.class);
> System.out.println("Processing " + row);
> Article article = new Article();
>
> article.setAuthor((String) row.get("email_id"));
>
>
> *exchange.getOut().setBody(article);*
>
> }
>
>
>
>
>
>
> 2017-11-13 13:48 GMT+01:00 Claus Ibsen <cl...@gmail.com>:
>
>> Hi
>>
>> If the 1st query return only 1 row, then you can set outputType=SelectOne
>>
>> Then the returned value is a Map
>> Then you can use the column names as-is in the 2nd query
>>
>> See the docs at
>> https://github.com/apache/camel/blob/master/components/
>> camel-sql/src/main/docs/sql-component.adoc
>>
>> On Mon, Nov 13, 2017 at 1:41 PM, Christian Stark <st...@gmail.com>
>> wrote:
>> > Hello,
>> >
>> > Assuming the following example: I'd like to know how to
>> control/manipulate
>> > the variables to insert the input from the first query from
>> table/database1
>> > into the second table/database2.
>> > What is the best option to manipulate $input1,$input2,$input3
>> >
>> > Do we need a transformer or a converter?
>> >
>> >
>> > <route id="demo_route">
>> > <from id="route-timer" uri="timer:foo?period=20000"/>
>> > <to id="_to1" uri="sql:SELECT column1,column2,column3 FROM
>> > testtable where key = ':3685127';?dataSource=myDataSource"/>
>> >
>> > <to id="_to2" uri="sql:INSERT INTO USERS
>> > (column1,column2,column3) values
>> > ('$input1,$input2,$input3)?dataSource=myDataSource2"/>
>> > </route>
>> >
>> >
>> >
>> > Any input is appreciated
>> >
>> >
>> > Thanks
>> > Christian
>>
>>
>>
>> --
>> Claus Ibsen
>> -----------------
>> http://davsclaus.com @davsclaus
>> Camel in Action 2: https://www.manning.com/ibsen2
>>
--
Claus Ibsen
-----------------
http://davsclaus.com @davsclaus
Camel in Action 2: https://www.manning.com/ibsen2
Re: Need Example, use result from SQL-Query as input into Second Database/Table
Posted by Christian Stark <st...@gmail.com>.
Thanks Claus
I found the following example very helpful.
The SQL returns multiple rows and I need to map the Columns to other
Objects.
http://www.javarticles.com/2015/07/apache-camel-jdbc-select-example.html
The only thing I need to find out is how to reuse the Output of my
Transformer Class as Input for the Insert.
<camelContext id="camelContext-1b6e9b83-bc33-4311-9e9b-0f2458fcedd9" xmlns="
http://camel.apache.org/schema/spring">
<route id="_route1">
<from id="_from1" uri="timer://timer1?period=2s"/>
<setBody id="_setBody1">
<constant>
select user_nm,email_id from drupal_users where user_id
< 5000
</constant>
</setBody>
<to id="_to1" uri="jdbc:myDataSource"/>
<split id="_split1">
<simple>${body}</simple>
<to uri="bean:rowProcessor" />
<to uri="stream:out"/>
<setBody id="_setBody2">
<simple>INSERT INTO USERS (column1) values(*${body[0]})*
</simple>
</setBody>
<to id="_to3" uri="jdbc:myDataSource2"/>
</split>
</route>
<bean class="org.mycompany.MyTransformer" id="rowProcessor"/>
import java.util.Map;
import org.apache.camel.Exchange;
import org.apache.camel.Processor;
public class MyTransformer implements Processor {
public void process(Exchange exchange) throws Exception {
Map<String, Object> row = exchange.getIn().getBody(Map.class);
System.out.println("Processing " + row);
Article article = new Article();
article.setAuthor((String) row.get("email_id"));
*exchange.getOut().setBody(article);*
}
2017-11-13 13:48 GMT+01:00 Claus Ibsen <cl...@gmail.com>:
> Hi
>
> If the 1st query return only 1 row, then you can set outputType=SelectOne
>
> Then the returned value is a Map
> Then you can use the column names as-is in the 2nd query
>
> See the docs at
> https://github.com/apache/camel/blob/master/components/
> camel-sql/src/main/docs/sql-component.adoc
>
> On Mon, Nov 13, 2017 at 1:41 PM, Christian Stark <st...@gmail.com>
> wrote:
> > Hello,
> >
> > Assuming the following example: I'd like to know how to
> control/manipulate
> > the variables to insert the input from the first query from
> table/database1
> > into the second table/database2.
> > What is the best option to manipulate $input1,$input2,$input3
> >
> > Do we need a transformer or a converter?
> >
> >
> > <route id="demo_route">
> > <from id="route-timer" uri="timer:foo?period=20000"/>
> > <to id="_to1" uri="sql:SELECT column1,column2,column3 FROM
> > testtable where key = ':3685127';?dataSource=myDataSource"/>
> >
> > <to id="_to2" uri="sql:INSERT INTO USERS
> > (column1,column2,column3) values
> > ('$input1,$input2,$input3)?dataSource=myDataSource2"/>
> > </route>
> >
> >
> >
> > Any input is appreciated
> >
> >
> > Thanks
> > Christian
>
>
>
> --
> Claus Ibsen
> -----------------
> http://davsclaus.com @davsclaus
> Camel in Action 2: https://www.manning.com/ibsen2
>
Re: Need Example, use result from SQL-Query as input into Second Database/Table
Posted by Claus Ibsen <cl...@gmail.com>.
Hi
If the 1st query return only 1 row, then you can set outputType=SelectOne
Then the returned value is a Map
Then you can use the column names as-is in the 2nd query
See the docs at
https://github.com/apache/camel/blob/master/components/camel-sql/src/main/docs/sql-component.adoc
On Mon, Nov 13, 2017 at 1:41 PM, Christian Stark <st...@gmail.com> wrote:
> Hello,
>
> Assuming the following example: I'd like to know how to control/manipulate
> the variables to insert the input from the first query from table/database1
> into the second table/database2.
> What is the best option to manipulate $input1,$input2,$input3
>
> Do we need a transformer or a converter?
>
>
> <route id="demo_route">
> <from id="route-timer" uri="timer:foo?period=20000"/>
> <to id="_to1" uri="sql:SELECT column1,column2,column3 FROM
> testtable where key = ':3685127';?dataSource=myDataSource"/>
>
> <to id="_to2" uri="sql:INSERT INTO USERS
> (column1,column2,column3) values
> ('$input1,$input2,$input3)?dataSource=myDataSource2"/>
> </route>
>
>
>
> Any input is appreciated
>
>
> Thanks
> Christian
--
Claus Ibsen
-----------------
http://davsclaus.com @davsclaus
Camel in Action 2: https://www.manning.com/ibsen2