setting up JDBC connection pool in Talend ESB container

Highlighted
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!!!

 

 


Accepted Solutions
Six 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>

 


All 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?
Six 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>

 

One Star

Re: setting up JDBC connection pool in Talend ESB container

The best way to create multiple connection pools is to use pax-jdbc-pool-dbcp2, do the following, ensure talend is running:

 

  1. create a datapool configuration file under runtimePath/container/etc/org.ops4j.datasource-db_your_data_source_name.cfg
  2. Update the file accordingly: 
    1. osgi.jdbc.driver.name=mysql
      pool=dbcp2
      xa=true
      databaseName=your_database_name
      user=your_database_userName
      password=your_database_username_password
      dataSourceName=your_data_source_name
      jdbc.pool.maxTotal=8
    2. note in this approach you can name your data source anything and not for example "ds-mysql"
  3. Note in the tesb.log that the file has been loaded 
    2019-01-10T18:16:00,636 | INFO  | fileinstall-XXXXX/TOS_ESB-20180116_1512-V6.5.1/Runtime_ESBSE/container/etc | install.internal.Util$OsgiLogger  205 | 8 - org.apache.felix.fileinstall - 3.5.8 | Creating configuration from org.ops4j.datasource-db_your_data_source_name.cfg
  4. go into the talend client i.e ./client in the container/bin folder
  5. execute the following commands:
    feature:repo-add mvn:org.ops4j.pax.jdbc/pax-jdbc-features/1.0.0/xml/features
    feature:install transaction jndi pax-jdbc-mysql pax-jdbc-config pax-jdbc-pool-dbcp2
    1. in the above I am using mysql "pax-jdbc-mysql" should you require another database the following options can be used:
      1. pax-jdbc-oracle
        pax-jdbc-db2
        pax-jdbc-derby
        pax-jdbc-derbyclient
        pax-jdbc-h2
        pax-jdbc-hsqldb
        pax-jdbc-jtds
        pax-jdbc-mariadb
        pax-jdbc-mssql
        pax-jdbc-mysql
        pax-jdbc-postgresql
        pax-jdbc-sqlite
        pax-jdbc-teradata
  6. Confirm that the datasource has been created by executing  service:list javax.sql.DataSource
    [javax.sql.DataSource]
    ----------------------
     service.factoryPid = org.ops4j.datasource
     felix.fileinstall.filename = file:/XXX/Runtime_ESBSE/container/etc/org.ops4j.datasource-db_your_data_source_name.cfg
     service.pid = org.ops4j.datasource.9d02b595-6315-49f3-9ed4-17537e99b126
     Jdbc.pool.maxTotal = 8
     password = password
     databaseName = databaseName
     user = user
     osgi.jndi.service.name = your_data_source_name
     dataSourceName = your_data_source_name
     osgi.jdbc.driver.name = mysql
     service.id = 491
     service.bundleid = 271
     service.scope = singleton
    Provided by : 
     OPS4J Pax JDBC Config (271)

 

Should you wish to create a second connection pool merely create a new configuration file to each database you are trying to connect to, ensure that the osgi.jdbc.driver.name is set to the correct database. Be sure to set your alias name in your db connector in the studio to what the dataSourceName name is in your configuration file.

 

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.