You are not logged in.

Unanswered posts



Important! This site has been replaced. All content here is read-only. Please visit our brand-new community at https://community.talend.com/. We look forward to hearing from you there!



#1 2014-08-28 16:05:31

kleinmat
Member
130 posts

kleinmat said:

Connection pooling?

Hi there,

I have created a simple, synchronous Web Service which reads records out of a database, processes them and returns some values.
For the data fetching part, I used a tOracleInput component.
But I have the feeling that every request leads to the web service opening a new connection to the DB, fetching rows, and closing the DB connection. Is that correct?

If so: how can I avoid such overhead and use a connection pool (which automatically restores a DB connection if it has been lost)?

Thanks
Matt

Offline

#2 2014-08-28 16:33:27

bhough
Talend Team


bhough said:

Re: Connection pooling?

Hi Matt,
In your tOracleInput Component tab, take a look at the bottom box that says 'Data Source'.  There you can check 'Specify data source alias' and name your data source.  For instance, I call my sandbox pool 'mysql-ds'.  As the note says, this will only apply in Karaf.
Before you deploy the service to Karaf you'll need to deploy a connection pool.  To do this, create a Springframework configuration file that defines the data source.  You will have to make sure any required JDBC drivers or other dependencies are installed as well.
Just drop the Spring file into the deploy/ directory.  Then you will have connection pooling.  You just have to make sure the value you use in osgi:service-properties matches with the one you used in your component data source alias.

Here is a sample file:

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

If you want to see the datasource in JMX (like you can with Tomcat), then just add this bean with appropriate reference/naming adjustments:

	<!-- Export key DataSource Pool properties into JMX for Management and Monitoring -->
	<bean id="exporter" class="org.springframework.jmx.export.MBeanExporter" lazy-init="false">
		<property name="beans">
			<map>
				<entry key="org.talend.DataSource:name=MySQL" value-ref="mysql-ds"/>
			</map>
		</property>
		<property name="assembler">
			<bean class="org.springframework.jmx.export.assembler.MethodNameBasedMBeanInfoAssembler">
				<property name="managedMethods">
					<value>
						isClosed,
						getDefaultAutoCommit,
						setDefaultAutoCommit,
						getDefaultTransactionIsolation,
						setDefaultTransactionIsolation,
						getDriverClassName,
						getInitialSize,
						getMaxActive,
						setMaxActive,
						getMaxIdle,
						setMaxIdle,
						getMaxOpenPreparedStatements,
						setMaxOpenPreparedStatements,
						getMaxWait,
						setMaxWait,
						getMinEvictableIdleTimeMillis,
						setMinEvictableIdleTimeMillis,
						getMinIdle,
						getNumActive,
						getNumIdle,
						getNumTestsPerEvictionRun,
						setNumTestsPerEvictionRun,
						isPoolPreparedStatements,
						setPoolPreparedStatements,
						getRemoveAbandoned,
						setRemoveAbandoned,
						getRemoveAbandonedTimeout,
						setRemoveAbandonedTimeout,
						getTestOnBorrow,
						setTestOnBorrow,
						getTestOnReturn,
						setTestOnReturn,
						getTestWhileIdle,
						setTestWhileIdle,
						getTimeBetweenEvictionRunsMillis,
						setTimeBetweenEvictionRunsMillis,
						getUrl,
						getValidationQuery,
						setValidationQuery,
						getValidationQueryTimeout,
						setValidationQueryTimeout
					</value>
				</property>
			</bean>
		</property>
	</bean>

HTH,
Ben

Offline

#3 2014-08-28 16:39:54

kleinmat
Member
130 posts

kleinmat said:

Re: Connection pooling?

Wow, that is really complicated. Is this actually the only way? If so: would you mind passing this on to your product management to make connection pooling available "at the click of a button"?

Offline

#4 2017-03-17 22:46:37

email3888
Member
11 posts

email3888 said:

Re: Connection pooling?

I am following your steps. I got the following error in the Studio. Do you know what is missing?
Configuration problem: Unable to locate Spring NamespaceHandler for XML schema namespace []
Offending resource: class path resource [META-INF/spring/demodatasource.xml]
org.springframework.beans.factory.parsing.BeanDefinitionParsingException: Configuration problem: Unable to locate Spring NamespaceHandler for XML schema namespace []
Offending resource: class path resource [META-INF/spring/demodatasource.xml]
    at org.springframework.beans.factory.parsing.FailFastProblemReporter.error(FailFastProblemReporter.java:68)
    at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:
    at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:80)
    at org.springframework.beans.factory.xml.BeanDefinitionParserDelegate.error(BeanDefinitionParserDelegate.java:315)
    at org.springframework.beans.factory.xml.BeanDefinitionParserDelegate.parseCustomElement(BeanDefinitionParserDelegate.java:1429)
    at org.springframework.beans.factory.xml.BeanDefinitionParserDelegate.parseCustomElement(BeanDefinitionParserDelegate.java:1422)
    at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.parseBeanDefinitions(DefaultBeanDefinitionDocumentReader.java:187)
    at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.doRegisterBeanDefinitions(DefaultBeanDefinitionDocumentReader.java:147)
    at org.springframework.beans.factory.xml.DefaultBeanDefinitionDocumentReader.registerBeanDefinitions(DefaultBeanDefinitionDocumentReader.java:101)
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.registerBeanDefinitions(XmlBeanDefinitionReader.java:495)
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.doLoadBeanDefinitions(XmlBeanDefinitionReader.java:391)
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:335)
    at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:303)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:174)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:209)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:180)
    at org.springframework.beans.factory.support.AbstractBeanDefinitionReader.loadBeanDefinitions(AbstractBeanDefinitionReader.java:243)
    at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions(AbstractXmlApplicationContext.java:127)
    at org.springframework.context.support.AbstractXmlApplicationContext.loadBeanDefinitions(AbstractXmlApplicationContext.java:93)
    at org.springframework.context.support.AbstractRefreshableApplicationContext.refreshBeanFactory(AbstractRefreshableApplicationContext.java:130)
    at org.springframework.context.support.AbstractApplicationContext.obtainFreshBeanFactory(AbstractApplicationContext.java:539)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:451)
    at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
    at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
    at demo_project.demodatasource_0_1.DemoDatasource$2.createContext(DemoDatasource.java:186)
    at org.apache.camel.main.Main.getCamelContextMap(Main.java:148)
    at org.apache.camel.main.MainSupport.postProcessContext(MainSupport.java:386)
    at org.apache.camel.main.Main.doStart(Main.java:124)
    at org.apache.camel.support.ServiceSupport.start(ServiceSupport.java:61)
    at org.apache.camel.main.MainSupport.run(MainSupport.java:124)
    at demo_project.demodatasource_0_1.DemoDatasource.run(DemoDatasource.java:252)
    at demo_project.demodatasource_0_1.DemoDatasource.runJobInTOS(DemoDatasource.java:314)
    at demo_project.demodatasource_0_1.DemoDatasource.main(DemoDatasource.java:284)
Job DemoDatasource ended at 14:08 17/03/2017. [exit code=1]

Thanks,
email3888

Offline

#5 2017-03-18 01:26:53

archenroot
Member
237 posts

archenroot said:

Re: Connection pooling?

Look into documentation step-by-step example: https://help.talend.com/display/TalendE … nd+Runtime


Emperor wants to control outer space Yoda wants to explore inner space that's the fundamental difference between good and bad sides of the Force

Offline

#6 2017-03-18 16:40:18

email3888
Member
11 posts

email3888 said:

Re: Connection pooling?

Thanks archenroot! I am able to create the datasource. Now, I have issue to use camel-sql in TOS 6.21
After I updated the Spring file with the datasource info, I have the following error. Do you have experience with camel-sql
Configuration problem: Unable to locate Spring NamespaceHandler for XML schema namespace []
Thanks,
email3888

Offline

Board footer

Talend Contributor Agreement - Talend Website Privacy Policy