On Mon, Jun 9, 2014 at 8:50 PM, Matt Raible <[email protected]> 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. > Thanks, > > Matt > > On Jun 9, 2014, at 12:24 PM, Claus Ibsen <[email protected]> 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 <[email protected]> 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("FIRST_NAME")); >>> members.add(member); >>> } >>> exchange.getOut().setBody(members.get(0)); >>> } >>> }); >>> >>> Unfortunately, this results in the following error: >>> >>> org.apache.camel.NoSuchEndpointException: No endpoint could be found for: >>> ADDRESS1, please check your classpath contains the needed Camel component >>> jar. >>> >>> I tried switching it to Antoine's recommendation, but it results in the >>> same error. >>> >>> .setHeader("target", 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")) >>> .recipientList(header("target")) >>> >>> >>> On Jun 8, 2014, at 1:07 AM, Claus Ibsen <[email protected]> wrote: >>> >>>> Hi >>>> >>>> >>>> If you se <to> then the uri is computed statically, eg once when the >>>> route is initialized. >>>> >>>> So the "dynamic to" is a good practice for dynamic uris >>>> http://camel.apache.org/how-to-use-a-dynamic-uri-in-to.html >>>> >>>> As you can the pass in the dataSource parameter dynamically. As well >>>> construct the SQL dynamic too. >>>> >>>> And if you want this CASE SELECT construct in the routes as well, then >>>> you can use the content based router. But having that logic in a java >>>> class is maybe better. >>>> >>>> >>>> >>>> On Sat, Jun 7, 2014 at 5:31 PM, Antoine DESSAIGNE >>>> <[email protected]> wrote: >>>>> Hello Matt, >>>>> >>>>> You'll have to use the recipient list [1] pattern: >>>>> >>>>> from("direct:projects") >>>>> .setProperty("ds", /* logic to convert client param to datasource name >>>>> */) >>>>> .setHeader("target", simple("sql:select * from projects order by >>>>> id?dataSource=${property.ds}") >>>>> .recipientList(header("target")) >>>>> .to(/* what you would do next after the SQL request */) >>>>> >>>>> Antoine. >>>>> >>>>> [1] http://camel.apache.org/recipient-list.html >>>>> >>>>> >>>>> 2014-06-07 0:08 GMT+02:00 Matt Raible <[email protected]>: >>>>> >>>>>> Hello, >>>>>> >>>>>> I'm converting services written with IBM Message Broker 6.1 to Apache >>>>>> Camel. Here's some of the logic I'm trying to convert: >>>>>> >>>>>> SET Environment.Variables.dataSource = ''; >>>>>> CASE UPPER(InputRoot.XMLNSC.ns:memberLookupRequest.ns:args0.ax21:Client) >>>>>> WHEN 'client1' THEN SET Environment.Variables.dataSource = 'foo'; >>>>>> WHEN 'client2' THEN SET Environment.Variables.dataSource = 'bar'; >>>>>> WHEN 'client3' THEN SET Environment.Variables.dataSource >>>>>> = 'baz'; >>>>>> END CASE; >>>>>> >>>>>> Basically, a parameter comes in and the dataSource is dynamically >>>>>> configured based on it. What's the best way to do this with Apache Camel? >>>>>> I'm guessing something like this might work (adopted from >>>>>> http://camel.apache.org/sql-component.html): >>>>>> >>>>>> from("direct:projects") >>>>>> .setProperty("ds", /* logic to convert client param to datasource name >>>>>> */) >>>>>> .to("sql:select * from projects order by id?dataSource=#${property.ds}") >>>>>> >>>>>> Is this the best way to configure dynamic datasources? For each >>>>>> datasource, I realize I'll have to configure it as a @Bean with Spring's >>>>>> JavaConfig. >>>>>> >>>>>> Thanks, >>>>>> >>>>>> Matt >>>> >>>> >>>> >>>> -- >>>> Claus Ibsen >>>> ----------------- >>>> Red Hat, Inc. >>>> Email: [email protected] >>>> Twitter: davsclaus >>>> Blog: http://davsclaus.com >>>> Author of Camel in Action: http://www.manning.com/ibsen >>>> hawtio: http://hawt.io/ >>>> fabric8: http://fabric8.io/ >>> >> >> >> >> -- >> Claus Ibsen >> ----------------- >> Red Hat, Inc. >> Email: [email protected] >> Twitter: davsclaus >> Blog: http://davsclaus.com >> Author of Camel in Action: http://www.manning.com/ibsen >> hawtio: http://hawt.io/ >> fabric8: http://fabric8.io/ > -- Claus Ibsen ----------------- Red Hat, Inc. Email: [email protected] Twitter: davsclaus Blog: http://davsclaus.com Author of Camel in Action: http://www.manning.com/ibsen hawtio: http://hawt.io/ fabric8: http://fabric8.io/
