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);
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?
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/
