Five Stars

setting up JDBC connection pool in Talend ESB container

Hi,

 

I'm trying to setup my REST services jobs to use JDBC connection pooling when deployed on Talend ESB runtime (Karaf).

I've followed the instructions in the documentation guide here

 

From the docs I've run the following:

  1. place the SQL Server JDBC driver in ./runtime/deploy/sqljdbc42.jar
  2. feature:install tesb-datasource-sqlserver
  3. feature:list | grep sqlserver  
    tesb-datasource-sqlserver | 6.2.1 | x | Started | tesb-6.2.1 |
  4. edited ./runtime/etc/org.talend.esb.datasource.sqlserver.cfg 
    dataSource.url=jdbc:sqlserver://MYHOST:1433;databaseName=MYDB
    dataSource.user=MYUSER
    dataSource.password=MYPASS
    datasource.pool.maxActive=20
    datasource.pool.maxIdle=5
    datasource.pool.maxWait=30000
  5. Actually I'm not sure where the JDBC Driver class name is set above! It should be "com.microsoft.sqlserver.jdbc.SQLServerDriver". Anyway!
  6. bundle:list | grep MSSQL
    355 | Active | 80 | 0.8.0 | OPS4J Pax JDBC MSSQL Driver Adapter
  7. bundle:refresh 355
  8. bundle:restart 355

Now, I have a job that sets the data source alias on my MSSQLServerConnection:

sqlserver-1.png

 

But when I deploy my job, the bundle stays in "GracePeriod" state. 

When I look at the logs I see that the job is waiting on a dependency with the following error message:

 

2017-05-04 18:25:59,919 | INFO | ool-491-thread-1 | BlueprintContainerImpl | 16 - org.apache.aries.blueprint.core - 1.6.1 | Bundle demo.rest1/0.5.0.SNAPSHOT is waiting for dependencies [(&(osgi.jndi.service.name=ds-sqlserver)(objectClass=javax.sql.DataSource))] 

 

What's the dependency feature that needs to be started??

I'm feeling Karaf has not started the ds-sqlserver connection pool!!!

 

 

  • ESB
2 ACCEPTED SOLUTIONS

Accepted Solutions
Five Stars

Re: setting up JDBC connection pool in Talend ESB container

I do that not using tesb but pure Camel commands :

Install the Camel JDBC feature

feature:install jdbc

Create a Datasource

 jdbc:ds-create -url jdbc:oracle:thin:@//hostname:1521/dbname -u login -p password -dc oracle.jdbc.OracleDriver jdbc/sample

Check you can list it

jdbc:ds-list

Even test some SQL

jdbc:query jdbc/sample select * from tablename
Five Stars

Re: setting up JDBC connection pool in Talend ESB container

Thanks so much for your reply @vharcq. I actually got another solution as well but forgot to post it here.

 

Here's the other solution I found:

 

I had to drop a Spring Bean XML config file into the Karaf deploy directory. Here's a sample Spring Bean configuration for MySQL but it can easily be modified for SQL Server.

Edit this file based on your JDBC connection URL and drop it into the deploy directory of Karaf; afterwards you can use the connection name as the "alias" in your t****Connection component.

 

 

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:osgi="http://www.springframework.org/schema/osgi"
  xmlns:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
   http://www.springframework.org/schema/osgi http://www.springframework.org/schema/osgi/spring-osgi.xsd
   http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
  
	<!-- The Connection Pool -->
	<bean id="mysql-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://localhost/demo"/>
		<property name="username" value="talend"/>
		<property name="password" value="talend"/>
		<property name="initialSize" value="4"/>
		<property name="maxActive"  value="30"/>
		<property name="maxIdle" value="10"/>
		<property name="maxWait" value="3000"/>
		<property name="validationQuery" value="SELECT 1"/>
	</bean>

	<!-- Publish the pool as an OSGi and JNDI Service -->
	<osgi:service interface="javax.sql.DataSource" ref="mysql-ds">
		<osgi:service-properties>
			<entry key="osgi.jndi.service.name" value="mysql-ds"/>
		</osgi:service-properties>
	</osgi:service>
</beans>

 

4 REPLIES
Five Stars

Re: setting up JDBC connection pool in Talend ESB container

Follow up question... how do you set up multiple connection pools? for example how do you create a new or change "ds-sqlserver" pool?

 

Five Stars

Re: setting up JDBC connection pool in Talend ESB container

Follow up question... how do you set up multiple connection pools? for example how do you create a new or change "ds-sqlserver" pool?
Five Stars

Re: setting up JDBC connection pool in Talend ESB container

I do that not using tesb but pure Camel commands :

Install the Camel JDBC feature

feature:install jdbc

Create a Datasource

 jdbc:ds-create -url jdbc:oracle:thin:@//hostname:1521/dbname -u login -p password -dc oracle.jdbc.OracleDriver jdbc/sample

Check you can list it

jdbc:ds-list

Even test some SQL

jdbc:query jdbc/sample select * from tablename
Five Stars

Re: setting up JDBC connection pool in Talend ESB container

Thanks so much for your reply @vharcq. I actually got another solution as well but forgot to post it here.

 

Here's the other solution I found:

 

I had to drop a Spring Bean XML config file into the Karaf deploy directory. Here's a sample Spring Bean configuration for MySQL but it can easily be modified for SQL Server.

Edit this file based on your JDBC connection URL and drop it into the deploy directory of Karaf; afterwards you can use the connection name as the "alias" in your t****Connection component.

 

 

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:osgi="http://www.springframework.org/schema/osgi"
  xmlns:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
   http://www.springframework.org/schema/osgi http://www.springframework.org/schema/osgi/spring-osgi.xsd
   http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
  
	<!-- The Connection Pool -->
	<bean id="mysql-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://localhost/demo"/>
		<property name="username" value="talend"/>
		<property name="password" value="talend"/>
		<property name="initialSize" value="4"/>
		<property name="maxActive"  value="30"/>
		<property name="maxIdle" value="10"/>
		<property name="maxWait" value="3000"/>
		<property name="validationQuery" value="SELECT 1"/>
	</bean>

	<!-- Publish the pool as an OSGi and JNDI Service -->
	<osgi:service interface="javax.sql.DataSource" ref="mysql-ds">
		<osgi:service-properties>
			<entry key="osgi.jndi.service.name" value="mysql-ds"/>
		</osgi:service-properties>
	</osgi:service>
</beans>