setting up JDBC connection pool in Talend ESB container

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>

 

Four Stars

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.

 

NOTE

The instructions for creating an oracle connection are somewhat different, Since the oracle driver is neither in maven central or an osgi bundle. Before executing pax-jdbc-oracle in step 5 you will need to perform the following:

  1. You will need to download the ojdbc oracle Jar and copy it to you deployment environment
  2. execute the following command:
    bundle:install wrap:file:/path_to_download/ojdbc-7.jar\$Bundle-SymbolicName=oracle.jdbc&Bundle-Version=1.0&Bundle-Name='JDBC Driver for Oracle'
  3. The above command is tricky to get right since if you have made an error you will not get anything in the logs. Execute the list command in the talend client in order to determine if the driver has been installed.
    Screenshot from 2019-01-24 17-23-59.png
  4. Once this is complete you can then install the pax driver
    feature:install pax-jdbc-oracle

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Talend API Designer – Technical Overview

Take a look at this technical overview video of Talend API Designer

Watch Now

Getting Started with APIs

Find out how to get started with APIs

Read