[resolved] DB connection doubt in Mediation route

[Anonymous]

Hi,
I am implementing a service using cHTTP in Router (TOS 6.0), and my requirements are pretty simple: to store HTTP body into a database table.
My Route is: cHTTP (Service) --> cTalendJob
and my job is: tRouteInput --> tPostgresqlConnection --> tPostgresqlOutput --> tPostgresqlCommit / tPostgresqlRollback
However, my doubt is will it create a new db connection every time when new request is received from HTTP client? How to implement "db connect pool" in this case?

[Anonymous]

Hi,
you need to use the: Data source alias field with the tProsgreSQLConnection component and on teh container you need to defined a related PostgreSQL DataSource (an example of a related file is in the ESB Package. \Talend-ESB-V6.1.1\add-ons\datasources\dataservice\datasource-postgres.xml

Additional info is in the Studio Guide: Appendix J. Theory into practice: Data service and routing examples --> Chapter: J.3. Route using connection pooling

By this you get a Connection Pooling on the Container Level (even across routes) 

HTH,
Dietmar

[Anonymous]

Hi Dietmar,
I went through that Infrastructure CG as well as that "Route using connection pooling", however i still cannot setup a database connection pool. 
Basically there are two problems:
1. After installing Postgresql JDBC driver version 9.4 ( bundle:install  mvnrg.postgresql/postgresql/9.4-1206-jdbc42) for my Java v1.8, there is an warning in tesb log file shows: 
23:24:41,741 | WARN  | tp1012633657-344 | r.handler.ContextHandler$Context 2062 | 188 - org.eclipse.jetty.util - 9.2.10.v20150310 | %bundles.pluginTitle: Cannot start org.osgi.framework.BundleException: Exception in org.postgresql.osgi.PGBundleActivator.start() of bundle org.postgresql.jdbc42.


2. I tried to use the JDBC driver which is mentioned in datasource-postgres.xml file with (wrap:mvn ostgresql/postgresql/9.1-901.jdbc4), it can be started, but I found my job is still using in-build DB setup instead of using DB setup in deployed datasource-postgres.xml. Also I doubt if this JDBC drive could work properly with my database which is postgresql v9.4.

[Anonymous]

1. http://stackoverflow.com/questions/30363304/how-can-i-install-postgresqljdbc-to-work-in-karaf-osgi

[Anonymous]

Thank you, aLexx! Now it works fine.