On Jun 9, 2014, at 1:03 PM, Claus Ibsen <claus.ib...@gmail.com> wrote:
> On Mon, Jun 9, 2014 at 8:50 PM, Matt Raible <m...@raibledesigns.com> 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?
>
>
>
>> Thanks,
>>
>> Matt
>>
>> On Jun 9, 2014, at 12:24 PM, Claus Ibsen <claus.ib...@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 <m...@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("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 <claus.ib...@gmail.com> 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
>>>>> <antoine.dessai...@gmail.com> 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 <m...@raibledesigns.com>:
>>>>>>
>>>>>>> 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: cib...@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/
>>>>
>>>
>>>
>>>
>>> --
>>> Claus Ibsen
>>> -----------------
>>> Red Hat, Inc.
>>> Email: cib...@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/
>>
>
>
>
> --
> Claus Ibsen
> -----------------
> Red Hat, Inc.
> Email: cib...@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/
