You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by "John F. Berry" <bo...@yahoo.com.INVALID> on 2018/08/09 14:33:33 UTC
[Solved] Java DSL route now needs a MS SQL .to( How do I declare
connection info?
Sorry to have missed it.. but I simply needed to add this dependency to the POM:
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>7.0.0.jre10</version>
</dependency>
Thank you all!!
On Wednesday, August 1, 2018, 4:39:18 PM EDT, Quinn Stevenson <qu...@pronoia-solutions.com> wrote:
The driver needs to be in the class path so the BasicDataSource can load it.
Also, if you’re using Karaf, you’ll probably need to add an import for the JDBC driver package in your manifest.
You may want to use basicDataSource.setDriver (instead of setDriverClassName) - that would remove the need for the OSGi import (if you’re using OSGi).
> On Aug 1, 2018, at 1:40 PM, John F. Berry <bo...@yahoo.com.INVALID> wrote:
>
>
>
> Thank you!
>
> I do now get this error:
> 2018-08-01 15:31:08,943 [105.13.206:8888] ERROR DefaultErrorHandler - Failed delivery for (MessageId: ID-MGH16557-1533151818778-0-6 on ExchangeId: ID-MGH16557-1533151818778-0-4). Exhaustedafter delivery attempt: 1 caught: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot load JDBC driver class 'co
> m.microsoft.sqlserver.jdbc.SQLServerDriver'
>
> org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot load JDBC driver class 'com.microsoft.sqlserver.jdbc.SQLServ
> erDriver'
>
>
> Do I need to import this driver manually somehow?
>
>
>
>
> On Wednesday, August 1, 2018, 3:25:57 PM EDT, Claus Ibsen <cl...@gmail.com> wrote:
>
>
>
>
>
> Oh and use psoft-sql as the component name instead of sql, eg
>
> to("psoft-sql:xxxx")
>
> On Wed, Aug 1, 2018 at 9:24 PM, Claus Ibsen <cl...@gmail.com> wrote:
>> Hi
>>
>> You have set the datasource already on the sql component, so in your
>> Camel sql endpoint you should not have ?dataSource=#xxxx - that can be
>> removed
>>
>> On Wed, Aug 1, 2018 at 8:53 PM, John F. Berry
>> <bo...@yahoo.com.invalid> wrote:
>>> OK.. So I attached the override to my route... but Camel execution now complains I do not have a valid DataSource:
>>>
>>>
>>>
>>> public class MyRouteBuilder extends RouteBuilder { @Override
>>> public void configure() throws Exception {
>>> BasicDataSource basicDataSource = new BasicDataSource();
>>> basicDataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
>>> basicDataSource.setUrl("jdbc:sqlserver://XXXXXX:52739;databaseName=XXXX;");
>>> basicDataSource.setUsername("XXXX");
>>> basicDataSource.setPassword("XXXXX");
>>>
>>> SqlComponent sqlComponent = new SqlComponent();
>>> sqlComponent.setDataSource(basicDataSource);
>>>
>>> getContext().addComponent("psoft-sql", sqlComponent);
>>>
>>>
>>> from("mllp://XXXXXX:8888")
>>> .log("..Received HL7 message with control id ${header.CamelMllpMessageControlId}")
>>> .convertBodyTo(String.class)
>>> .unmarshal()
>>> .hl7(false)
>>> .process(new Processor() {
>>> public void process(Exchange exchange) throws Exception {
>>> Message message = exchange.getIn().getBody(Message.class);
>>> ca.uhn.hl7v2.util.Terser terser = new Terser(message);
>>> String obx5 = terser.get("/.OBX-5-5");
>>> String EDMId = terser.get("/.OBR-3") + ".pdf";
>>> String voucher = terser.get("/.OBR-2");
>>>
>>> byte[] decoded = Base64.getDecoder().decode(obx5);
>>> exchange.getOut().setBody(decoded);
>>> exchange.getOut().setHeader("voucher", voucher);
>>> exchange.getOut().setHeader("CamelFileName", EDMId );
>>> }
>>> } )
>>> .log("..Processed voucher ${header.voucher} to file ${header.CamelFileName}")
>>> .to("file:target/messages/others")
>>> .to("sql:INSERT INTO lawsonprod.PeopleSoftVCR (Voucher, Facility, image) VALUES ('12345', '1', 'something')?dataSource=#psoft-sql" )
>>> ;
>>>
>>> }
>>>
>>> }
>>>
>>>
>>> error is:
>>> No bean could be found in the registry for: psoft-sql of type: javax.sql.DataSource
>>>
>>>
>>> On Tuesday, July 31, 2018, 2:04:32 PM EDT, John F. Berry <bo...@yahoo.com> wrote:
>>>
>>> Nevermind... found to import org.apache.camel.component.sql.SqlComponent
>>>
>>> On Tuesday, July 31, 2018, 1:27:58 PM EDT, John F. Berry <bo...@yahoo.com.INVALID> wrote:
>>>
>>> Also, in compiling in maven, it doesn't find "SQLComponent". Isn't that part of camel-sql? or am I configuring a different entity?
>>>
>>> On Tuesday, July 31, 2018, 12:29:49 PM EDT, John F. Berry <bo...@yahoo.com.INVALID> wrote:
>>>
>>>
>>> Sorry, that was a bit of an incomplete thought:
>>> In another java IDE that came with a product of ours we use this driver:
>>> com.microsoft.sqlserver.jdbc.SQLServerDriver
>>> as well as this URI:
>>> jdbc:sqlserver://blablabla:52739;databaseName=dbName;
>>>
>>> So what is the choice of using a "derby" driver? I cannot find a writeup about it.
>>>
>>> On Tuesday, July 31, 2018, 12:17:09 PM EDT, John F. Berry <bo...@yahoo.com> wrote:
>>>
>>> Thanks again Quinn,
>>> What is the "derby" element in this solution? I can't seem to find information in this in my searches.
>>> I'm trying to analyze the parameter string in the .setURL line and the values within.
>>>
>>> On Tuesday, July 31, 2018, 10:55:42 AM EDT, Quinn Stevenson <qu...@pronoia-solutions.com> wrote:
>>>
>>> Forgot to mention to make sure that when you use this component, use the component name that you register it with - in this example I used “my-sql”.
>>>
>>>> On Jul 31, 2018, at 8:49 AM, Quinn Stevenson <qu...@pronoia-solutions.com> wrote:
>>>>
>>>> There’s a few ways to go about this, but I’d probably start out with doing it in the configure method of the RouteBuilder - something like the following (note - I didn’t test this so I don’t even know if it compiles).
>>>>
>>>> @Override
>>>> public void configure() throws Exception {
>>>> BasicDataSource basicDataSource = new BasicDataSource();
>>>> basicDataSource.setDriverClassName("org.apache.derby.jdbc.EmbeddedDriver");
>>>> basicDataSource.setUrl("jdbc:derby:memory:orders;create=true");
>>>> basicDataSource.setUsername("");
>>>> basicDataSource.setPassword("");
>>>>
>>>> SqlComponent sqlComponent = new SqlComponent();
>>>> sqlComponent.setDataSource(basicDataSource);
>>>>
>>>> getContext().addComponent("my-sql", sqlComponent);
>>>>
>>>> from()
>>>> ...
>>>> }
>>>> HTH
>>>>
>>>>> On Jul 30, 2018, at 9:45 AM, John F. Berry <bohnjerry@yahoo.com.INVALID <ma...@yahoo.com.INVALID>> wrote:
>>>>>
>>>>> Thanks Quinn for the helping hand.
>>>>>
>>>>> I've been looking for examples of how to declare the servername/instance name, username password to utilize a MS SQL endpoint. Looking at the Apache Camel: SQL Component page, it shows utilizing the declared object like "mydbconnection" that is the named DataSource option, and shows all on what you can throw through that connection... except seems to skip over how to specify connection criteria. It does reference the DataSource option as a pointer to look up in the "registry" but I don't know if that is an inherited existing entity it's talking about or the need to declare a new registry and import a form of camel registry or something. The only examples where I've seen servername, un/pw declared in an example was in a Spring one. I don't know if we want to "un-translate" a Spring example, but point me in the direction of where I should natively set these things in Java DSL. The above mentioned page even says "This component uses spring-jdbc behind the scenes for the actual SQL handling", so I figured that is why I cannot seem to get away from Spring.
>>>>> When I look at the Apache Camel: SQL Example( http://camel.apache.org/sql-example.html <http://camel.apache.org/sql-example.html> ) page, it talks about "In the camel-context.xml file in the src/main/resources/META-INF/spring folder we have the Spring XML file to setup and configure the database"
>>>>>
>>>>> I see connection parameters set like in this other example:
>>>>>
>>>>> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
>>>>> < property name = "driverClassName" value = "org.apache.derby.jdbc.EmbeddedDriver" />
>>>>>
>>>>> < property name = "url" value = "jdbc:derby:memory:orders;create=true" />
>>>>>
>>>>> < property name = "username" value = "" />
>>>>>
>>>>> < property name = "password" value = "" />
>>>>>
>>>>> </bean>
>>>>>
>>>>>
>>>>> but not declared in the Java DSL, if that's even possible.. Perhaps .setheaders() ? but then.. what are the paramater names?
>>>>>
>>>>>
>>>>> Sorry.. I know it's a bit of a "thrashing" response...
>>>>>
>>>>>
>>>>>
>>>>> On Monday, July 30, 2018, 10:43:34 AM EDT, Quinn Stevenson <quinn@pronoia-solutions.com <ma...@pronoia-solutions.com>> wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> From my experience, I’ve always been able to to more with the Java DSL than with Spring. I think routes written using the Spring XML are easier to read than routes written the Java DSL, but that’s just me.
>>>>>
>>>>> If you could post you’re Spring XML that you’re trying to translate, I’m sure we can help with that.
>>>>>
>>>>>> On Jul 30, 2018, at 8:00 AM, John F. Berry <bohnjerry@yahoo.com.INVALID <ma...@yahoo.com.INVALID>> wrote:
>>>
>>>>>>
>>>>>> I've been perusing ways to declare connection to a MS SQL server. There are plenty of Spring examples.. but how about Java DSL? I have been attempting to utilizing the JDBC endpoint provided though camel-sql.. but I cannot seem to either find documentation of how to place a configuration file with an unknown name in an unknown location in the project, or declare it in-line. Been looking at setting it first in the "camel registry", but no luck so far.
>>>>>> I did not include any code, since I have nothing functional or "in progress" to show for my efforts. I'm not to the point I need to form any sort of SQL statement yet, since I cannot establish a connection.It seems so simple, but I cannot find how to simply declare server:port, un/pw without bringing a Spring context into the mix. If that is needed to satisfy the camel vanilla SQL endpoint needs, how do I tie that into the Java DSL route I've already got going? I went the Java DSL route simply because some other endpoints didn't have a straight forward Camel Spring solution to them. Is this mixed environment normal? Is there really not a choice to keep a route "clean" in one coding style or the other?
>>>>>> Thanks!
>>>>
>>
>>
>>
>> --
>> 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