camel-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Claus Ibsen <claus.ib...@gmail.com>
Subject Re: Dynamic DataSource for SQL
Date Mon, 09 Jun 2014 20:57:42 GMT
Den mandag den 9. juni 2014 skrev Matt Raible <matt@raibledesigns.com>:

>
> On Jun 9, 2014, at 1:03 PM, Claus Ibsen <claus.ibsen@gmail.com
> <javascript:;>> wrote:
>
> > On Mon, Jun 9, 2014 at 8:50 PM, Matt Raible <matt@raibledesigns.com
> <javascript:;>> wrote:
> >> I'm not sure that's it. It's almost like the SQL Component doesn't like
> column names specified in the SQL. I tried changing it to "SELECT * FROM
> MEMBER" and then hard-coding the dataSource name to match a bean name, and
> it works. I guess I can live with using "select *", but you'd think any
> valid SQL would work.
> >>
> >> Now, to set my dataSource dynamically. I have a @Bean configured as
> follows:
> >>
> >>        @Bean(name = "ds.dsforclient1")
> >>        public DataSource upmcDataWarehouse() {
> >>                DriverManagerDataSource dataSource = new
> DriverManagerDataSource();
> >>                // set properties
> >>                return dataSource;
> >>        }
> >>
> >> I expect the following logic to set a "dataSource" header to
> "ds.dsforclient1".
> >>
> >>>>               switch (client) {
> >>>>                       case "client1":
> >>>>                               dataSourceName = "dsforclient1";
> >>>>                               break;
> >>>>                       case "client2":
> >>>>                               dataSourceName = "dsforclient2";
> >>>>                               break;
> >>>>                       default:
> >>>>                               // todo: propagate to error
> >>>>               }
> >>>>               exchange.getOut().setHeader("dataSource", "ds." +
> dataSourceName);
> >>
> >
> > Just a note about the getOut. You should favor getIn. See this FAQ
> > http://camel.apache.org/using-getin-or-getout-methods-on-exchange.html
> >
> >> However, I get the following error:
> >>
> >> Failed to resolve endpoint: sql://mycustomsql?dataSource=%23dataSource
> due to: No bean could be found in the registry for: dataSource of type:
> javax.sql.DataSource
> >>
> >> If I hard-code "?dataSource=ds.dsforclient1", it works fine. Is there a
> special syntax to tell the SQL component that this is a bean name?
> >>
> >
> > Yes see that link, section _Referring beans from Endpoint URIs_
> > http://camel.apache.org/how-do-i-configure-endpoints.html
> >
> > eg use #key to denote a bean lookup.
>
> I had to use ?dataSource=${header.dataSource} since it's a dynamic value.
>
> Any idea about why columns names aren't allowed in SQL? If I change from
> "SELECT * FROM" to "SELECT MEMBER_ID, FIRSTNAME FROM", I get the following
> error:
>
> Illegal character in scheme name at index 9
>
> It seems like the comma is causing Camel to think it's the end of the
> endpoint definition. Maybe it's caused by the "simple" expression language?
>
>
Ah yeah you need to change the separator on recipientlist to false og
something else than comma

See more at the recipient list docs

>
> >
> >
> >> Thanks,
> >>
> >> Matt
> >>
> >> On Jun 9, 2014, at 12:24 PM, Claus Ibsen <claus.ibsen@gmail.com> wrote:
> >>
> >>> Hi
> >>>
> >>> Ah dataSource=:#dataSource should just be #dataSource, eg as its a
> >>> regular lookup in the registry. See more at
> >>> http://camel.apache.org/how-do-i-configure-endpoints.html
> >>>
> >>> Its only the SQL component that has that special :#key for the SQL
> >>> placeholders (eg with a colon).
> >>>
> >>>
> >>> On Mon, Jun 9, 2014 at 7:34 PM, Matt Raible <matt@raibledesigns.com>
> wrote:
> >>>> Thanks for the advice. I've tried to implement this. Here's what my
> code looks like:
> >>>>
> >>>> from("direct:lookup")
> >>>>   .process(new Processor() {
> >>>>       public void process(Exchange exchange) throws Exception {
> >>>>               MemberRequest request =
> exchange.getIn().getBody(MemberRequest.class);
> >>>>               String client = request.getClient();
> >>>>               String memberId = request.getMemberId();
> >>>>               String dataSourceName = "";
> >>>>               switch (client) {
> >>>>                       case "client1":
> >>>>                               dataSourceName = "dsforclient1";
> >>>>                               break;
> >>>>                       case "client2":
> >>>>                               dataSourceName = "dsforclient2";
> >>>>                               break;
> >>>>                       default:
> >>>>                               // todo: propagate to error
> >>>>               }
> >>>>               exchange.getOut().setHeader("dataSource",
> dataSourceName);
> >>>>               exchange.getOut().setHeader("memberId", memberId);
> >>>>       }
> >>>>   })
> >>>> .recipientList(simple("sql:SELECT MEMBER_ID, ADDRESS1, ADDRESS2,
> ADDRESS3, " +
> >>>>       "COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7,
> COLUMN8, COLUMN9, COLUMN10, " +
> >>>>       "COLUMN11, COLUMN12, COLUMN13, COLUMN14, COLUMN15, COLUMN16,
> COLUMN17, COLUMN18, COLUMN19, COLUMN20 " +
> >>>>       "FROM MEMBER " +
> >>>>       "WHERE PATIENT.MEMBER_ID = :#memberId?dataSource=:#dataSource"))
> >>>> .process(new Processor() {
> >>>>       public void process(Exchange exchange) throws Exception {
> >>>>               List<HashMap> data = (ArrayList<HashMap>)
> exchange.getIn().getBody();
> >>>>               List<Member> members = new ArrayList<>();
> >>>>               for (HashMap<String, String> map : data) {
> >>>>                       Member member = new Member();
> >>>>                       member.setMemberId(map.get("MEMBER_ID"));
> >>>>                       member.setFirstName(map.get("



-- 
Claus Ibsen
-----------------
Red Hat, Inc.
Email: cibsen@redhat.com
Twitter: davsclaus
Blog: http://davsclaus.com
Author of Camel in Action: http://www.manning.com/ibsen
hawtio: http://hawt.io/
fabric8: http://fabric8.io/

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message