Is there a way to reconnect to a database?

[Anonymous]

I'm running into an issue where I deploy a REST service through ESB and it works but after a while it fails because the MSSQL components can't connect to the database because the connection was closed. As far as I can tell, it seems the database is killing the connection after a period of time. Is there a component I can use to automatically reconnect to the database if the connection closes or check if its closed? Or is there a parameter I can send that'll tell the database to keep the connection alive? Is this something I would have to configure on the database itself?

The database is Microsoft SQL Server if that helps. I'm on Talend Studio 6.2.1

[Anonymous]

Hi kelsontura,

It is possible but quite "complex". First step will be to externalise the connection with a tMSSQLConnection. When you do that, Talend actually create a GlobalMap value with your connection.

Then the second step, will be to put a tJavaFlex in front of where you want to perform your queries and test in it that the connection is still active, if not recall the tMSSQLConnection.

It should look like that:

 

And the content of tJavaFlex is (if condition is set to false) :

//not initialised
if(!globalMap.containsKey("conn_tMSSqlConnection_1"))
{
	tMSSqlConnection_1Process(globalMap);
}
else if(globalMap.containsKey("conn_tMSSqlConnection_1")) //initialised - testing if stilla ctive
{
	if(((java.sql.Connection)globalMap.get("conn_tMSSqlConnection_1")).isClosed())
	{
		tMSSqlConnection_1Process(globalMap);
	}
}

Thanks,

[vapukov]

what components are You use in Job?
How concurrent traffic? (mean - how many requests per timeframe- day, hour, night)

Add (after other post):
main idea was the same - if You use tRESTRequest, and loading not high - You can stop use shared connection (as variant). or define connection (if many database components) - for each request 
It could add some milliseconds for initialise connection for each request, but help avoid disconnections problems.

For high loading (really high) during the day, and low during night - need more information, and tests

[Anonymous]

We tried it with your solution and it partially works, however our tMSSqlLastInsertId component is still failing with a Connection Closed error. Our lookup tables which were previously what was causing the job to fail are now fine and are pulling data. Do we need to do something different for the LastInsertId component?

In it's code it looks like it gets the connection with this line:

java.sql.Connection conn_tMSSqlLastInsertId_1 = (java.sql.Connection)globalMap.get("conn_tMSSqlConnection_1");

but our tjava flex component should be setting that. This is what we have in its Main Code:

Connection conn = (java.sql.Connection)globalMap.get("conn_tMSSqlConnection_1");
if (conn == null || conn.isClosed()){
	System.out.println("DEBUG: CONNECTION WAS CLOSED");
	log.info("DEBUG: CONNECTION WAS CLOSED");
	tMSSqlConnection_1Process(globalMap);
	
}

We're stumped as to why its still getting a connection closed error.

[vapukov]

try the other solution

I do not have similar problem, even if service not work all night or weekend

[Anonymous]

Oh sorry if I understood your solution correctly its to stop using shared connection? We also did that for our other jobs and that did actually work, however the LastSqlInsertId component didn't return the right id from the database and we do need to return the correct id. So the LastSqlInsertId component has to use the shared connection and thats what keeps failing this particular job.

[vapukov]

You are right (half) - it use connection for each component in Job, but not leave it open between request. So each REST request open and close connections.

in Our case last inserted ID not important - it managed or auto increments or triggers.

if You use jTDS drivers, You can play with settings for keep Alive:
http://jtds.sourceforge.net/faq.html

socketKeepAlive (default - false)
true to enable TCP/IP keep-alive messages

but at least in case of mysql similar settings not help, still from time to time we was have problems with timeout, till not change work.