Java DSL route now needs a MS SQL .to( How do I declare connection info?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry
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!
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

Quinn Stevenson
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 <[hidden email]> 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!

Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry
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 )  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 <[hidden email]> 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 <[hidden email]> 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!
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

Mark Nuttall
Are you using Spring with the Java DSL? Just not sure if you are / aren't.

On Mon, Jul 30, 2018 at 11:45 AM, John F. Berry <[hidden email]
> 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 )  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 <
> [hidden email]> 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 <[hidden email]>
> 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!
>
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry
 I desire NOT to incorporate Spring with the mix I have.  I will if that is the standard way, but not sure how you would do that.

    On Monday, July 30, 2018, 12:29:20 PM EDT, Mark Nuttall <[hidden email]> wrote:  
 
 Are you using Spring with the Java DSL? Just not sure if you are / aren't.

On Mon, Jul 30, 2018 at 11:45 AM, John F. Berry <[hidden email]
> 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 )  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 <
> [hidden email]> 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 <[hidden email]>
> 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!
>  
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

Mark Nuttall
It just makes it so easy especially with spring boot. I've never tried it
without Spring. I only do the Java DSL.

To do it in Spring (Boot)

Go here - https://start.spring.io/
Pick Web, Camel, and SQL Server
create the zip and import to your favorite IDE. (if you are using IntelliJ
then you should have support for making it easy to run Spring Boot apps.
Otherwise, use STS or netbeans plus the Spring plugin.)
add the camel-jdbc dependency to your pom
create a route as per the Java DSL and JDBC component instructions
add the sql server connection to the applications.properties like this
1
2
3
4
5
6
7
spring.datasource.url=jdbc:sqlserver://localhost;databaseName=springbootdb
spring.datasource.username=sa
spring.datasource.password=Projects@123
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.
SQLServerDriver

spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect



run app as spring boot app


On Mon, Jul 30, 2018 at 2:08 PM, John F. Berry <[hidden email]>
wrote:

>  I desire NOT to incorporate Spring with the mix I have.  I will if that
> is the standard way, but not sure how you would do that.
>
>     On Monday, July 30, 2018, 12:29:20 PM EDT, Mark Nuttall <
> [hidden email]> wrote:
>
>  Are you using Spring with the Java DSL? Just not sure if you are / aren't.
>
> On Mon, Jul 30, 2018 at 11:45 AM, John F. Berry
> <[hidden email]
> > 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 )  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 <
> > [hidden email]> 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 <[hidden email]
> >
> > 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!
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

Quinn Stevenson
In reply to this post by John F. Berry
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 <[hidden email]> 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 )  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 <[hidden email]> 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 <[hidden email]> 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!

Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

Quinn Stevenson
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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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!
>

Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry

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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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!
>
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry

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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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!
>
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry
 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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!
>  
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry
Nevermind... found to import org.apache.camel.component.sql.SqlComponent

On Tuesday, July 31, 2018, 1:27:58 PM EDT, John F. Berry <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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!

Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

Quinn Stevenson
The example you gave me was for the Derby DB driver - so that’s what I put in the BasicDataSource.  Or course, you want to use what’s appropriate for your system.


> On Jul 31, 2018, at 12:04 PM, John F. Berry <[hidden email]> wrote:
>
> Nevermind... found to import org.apache.camel.component.sql.SqlComponent
>
> On Tuesday, July 31, 2018, 1:27:58 PM EDT, John F. Berry <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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!
>>  

Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry
In reply to this post by John F. Berry
 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 <[hidden email]> wrote:  
 
 Nevermind... found to import org.apache.camel.component.sql.SqlComponent

On Tuesday, July 31, 2018, 1:27:58 PM EDT, John F. Berry <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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!
>   
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

Claus Ibsen-2
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
<[hidden email]> 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 <[hidden email]> wrote:
>
>  Nevermind... found to import org.apache.camel.component.sql.SqlComponent
>
> On Tuesday, July 31, 2018, 1:27:58 PM EDT, John F. Berry <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

Claus Ibsen-2
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 <[hidden email]> 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
> <[hidden email]> 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 <[hidden email]> wrote:
>>
>>  Nevermind... found to import org.apache.camel.component.sql.SqlComponent
>>
>> On Tuesday, July 31, 2018, 1:27:58 PM EDT, John F. Berry <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry


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 <[hidden email]> 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 <[hidden email]> 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
> <[hidden email]> 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 <[hidden email]> wrote:
>>
>>  Nevermind... found to import org.apache.camel.component.sql.SqlComponent
>>
>> On Tuesday, July 31, 2018, 1:27:58 PM EDT, John F. Berry <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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
Reply | Threaded
Open this post in threaded view
|

Re: Java DSL route now needs a MS SQL .to( How do I declare connection info?

Quinn Stevenson
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
>> <[hidden email]> 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 <[hidden email]> wrote:
>>>
>>>   Nevermind... found to import org.apache.camel.component.sql.SqlComponent
>>>
>>> On Tuesday, July 31, 2018, 1:27:58 PM EDT, John F. Berry <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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

Reply | Threaded
Open this post in threaded view
|

[Solved] Java DSL route now needs a MS SQL .to( How do I declare connection info?

John F. Berry
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
>> <[hidden email]> 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 <[hidden email]> wrote:
>>>
>>>  Nevermind... found to import org.apache.camel.component.sql.SqlComponent
>>>
>>> On Tuesday, July 31, 2018, 1:27:58 PM EDT, John F. Berry <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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 <[hidden email] <mailto:[hidden email]>> 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