MANY connections w/JDBC that die in "Connection reset by peer" error 1

[RebLazer]

I've been writing a Java application that parses flat files and inserts them into MS SQL Server 2000 using JDBC. The code itself works, just that as it runs, it gets slower and slower - to ultra-turtle speed - and occassionally even crashes with the following error:
"Connection reset by peer: socket write error"

Among the data it parses are Strings, which often are concatenations weighing in at 300,000 characters long, and commonly longer.

Our highly-qualified and always-willing-to-help DBA informed me that the number of connections I've been maintaining with the database has varied between 2 and over 100(!). I am not doing any parallel processing, therefore, the maximum number of connections I'd ever expect at any given time should be 1. Another strange thing she told me is that when she runs a trace of the activity my computer is generating to the database, it shows many, many logons and logoffs, and not necessarily one at a time. For example, it might look like this: logon, logon, logon, logon, logoff, logoff, logon, etc...

It seems that for each SQL call a new connection is being created. Or perhaps the connections are never getting closed properly.

I've attached code from two of my classes below: DBConnection and InsertSequence. There is another class (not attached) that instantiates DBConnection (once per file parsed) and InsertSequence (once per record). Shouldn't the finalize method in InsertSequence (code below) be implicitly called to close all open connections?

We have been banging our head against the proverbial wall for quite a while here and it is really starting to hurt. Any help would be completely and totally appreciated!

RebLazer
Chicago, IL

********************



import java.sql.*;

public class DBConnection
{
public Connection con;


public DBConnection()
{
//constructor loads SQL DB Driver and sets up connection and statement objects
try
{
Class.forName(SSSConstants.driverClass);
}
catch(ClassNotFoundException e)
{
System.out.println("Unable to load Driver Class"
return;
}

try
{
//Create Connection
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://10.3.55.117;selectMethod=cursor;programName=SSS_Parser", "reblazer", "reblazer1"
}

catch (SQLException se)
{
System.out.println("SQL Exception: " + se.getMessage());
se.printStackTrace(System.out);
}
}



public void finalize()
{
try
{

//release resources upon exiting
con.close();
this.finalize();
}
catch (SQLException se)
{
System.out.println("SQL Exception: " + se.getMessage());
se.printStackTrace(System.out);
}

}


/**
* Method to insert files that has been parsed from the database
* @param sqlString A variable of Type String. This is the Insert statement.
* @return res Returns variable of type boolean to indicate that rows have been inserted.
*/
public ResultSet insert(String sqlString)
{
ResultSet rs = null;
Statement sql = null;
try
{

//execute statement and return results
rs = sql.executeQuery(sqlString);
}
catch (SQLException se)
{
System.out.println("SQL Exception: " + se.getMessage());
se.printStackTrace(System.out);
}
return rs;
}

/**
* Method to run query against DB for sequences that match user input
* @param sqlString A variable of Type String. This is the Select statement.
* @return rs This is the Resultset which is passed back to the calling program
*/
public ResultSet query(String sqlString)
{
ResultSet rs = null;//ResultSet is not needed but it must be set to null

try
{

//Create Statement
Statement sql = con.createStatement();

//execute statement and return results
rs = sql.executeQuery(sqlString);

}
catch (SQLException se)
{
System.out.println("SQL Exception: " + se.getMessage());
se.printStackTrace(System.out);
}
return rs;
}
}


public class InsertSequence extends DBConnection
{
NucSeq nucSeqSingle;

public InsertSequence()
{
super();
}


public InsertionCodes checkAccession(NucSeq nucSeqSingle, DBConnection dbConn) {
try {

CallableStatement cs = con.prepareCall("{call Genomics.CHECK_SEQ_DATA (?,?,?,?,?,?,?,?)}"
cs.setString(1, nucSeqSingle.getComName());
cs.setString(2, nucSeqSingle.getOriginalDB());
cs.setString(3, nucSeqSingle.getCategory());
cs.setString(4, nucSeqSingle.getSubInd());
cs.setString(5, nucSeqSingle.getAccession());
cs.setString(6, nucSeqSingle.getAccVer());
cs.setInt (7, nucSeqSingle.getGi());
cs.setString(8, nucSeqSingle.getDescription());

ResultSet rs = cs.executeQuery();
rs.next();

InsertionCodes insCodes = new InsertionCodes(rs.getInt(2));
return insCodes;
}

catch (SQLException se) {
System.out.println("SQL Exception: " + se.getMessage());
se.printStackTrace(System.out);
return null;
}
}



public InsertionCodes doInsert(NucSeq nucSeqSingle, DBConnection dbConn) {
try {

CallableStatement cs = con.prepareCall("{call Genomics.INSERT_SEQUENCE (?,?,?,?,?,?,?,?,?)}"
cs.setString(1, nucSeqSingle.getComName());
cs.setString(2, nucSeqSingle.getOriginalDB());
cs.setString(3, nucSeqSingle.getCategory());
cs.setString(4, nucSeqSingle.getSubInd());
cs.setString(5, nucSeqSingle.getAccession());
cs.setString(6, nucSeqSingle.getAccVer());
cs.setInt (7, nucSeqSingle.getGi());
cs.setString(8, nucSeqSingle.getDescription());
cs.setString(9, nucSeqSingle.getSequence());

ResultSet rs = cs.executeQuery();
rs.next();

InsertionCodes insCodes = new InsertionCodes(rs.getInt(1), rs.getInt(2), rs.getString(3));
return insCodes;
}

catch (SQLException se) {
System.out.println("SQL Exception: " + se.getMessage());
se.printStackTrace(System.out);
return null;
}
}

 

[wushutwist]

You need to make sure that you explicitly close all of your ResultSets, Statements, and Connections in finally blocks. Putting the Connection close in a finalize() method does not help because that will only be called when your object is gc'ed and if the JVM crashes or is abruptly killed then it will never be called. Here is a quick example of good use of resources:

public void query() throws SQLException {
  Connection conn = null;
  CallableStatement stmt = null;
  ResultSet rs = null;

  try {
    conn = ... // Get your Connection Here
    stmt = conn.prepareCall(...); // Create CallableStatement
    // Do stuff

    rs = stmt.executeQuery();
    
    // Do stuff
  }
  finally {
    // clean up
    if (rs != null) { try { rs.close(); } catch (SQLException sqle) { } }
    if (stmt != null) { try { stmt.close(); } catch (SQLException sqle) { } }
    if (conn != null) { try { conn.close(); } catch (SQLException sqle) { } }  
  }
}

This code guarantees that all ResultSets, Statements, and Connection are closed regardless of errors.

 

[RebLazer]

Wushutwist,

Thank you very much for replying, but I honestly don't think that's it. The problem is *not* that it's "forgetting" to close extraneous connections that are just hanging around. The problem *is* that our DBA tells me that she sees I am repeatedly doing logins - over and over again; seemingly one login per SQL call.

The pattern might look like this:
LOGIN
LOGIN
LOGIN
LOGOUT
LOGIN
LOGOUT
LOGIN
LOGIN
LOGOUT
LOGOUT
LOGIN
LOGOUT
LOGOUT
LOGOUT

I've set the "selectMethod=cursor" already in the connection URL: ("jdbc:microsoft:sqlserver://10.3.55.117;selectMethod=cursor;programName=SSS_Parser", "reblazer", "reblazer1"

So, like I said, it seems that there are extra connections being created.

Any further help? ...please?!

Thanks so much,
RebLazer

 

[wushutwist]

You are not showing the code that actually uses your DBConnection class, is it creating a new object each time?

Just a couple notes:

Do you realize your InsertSequence IS a DBConnection? I only ask because the methods in InsertSequence take DBConnection as parameters which doesn't make much sense. Everytime you create an InsertSequence object you are creating a connection to the database.

I noticed you are called executeQuery() for your updates. This is a waste. For INSERTS, UPDATES, and DELETES you are better off calling executeUpdate().

Back to the finalize... regardless of the other problems, you are still going to want to be more proactive with your database resources. finalize() methods should not be used like destructors in C/C++.

 

[RebLazer]

Wushutwist,

Thanks again for your reply. Something you said switched on a light bulb for me (and I quote):
"...I only ask because the methods in InsertSequence take DBConnection as parameters which doesn't make much sense. Everytime you create an InsertSequence object you are creating a connection to the database."

My thoughts:
I only want/need 1 DBConnection instantiation in all of my classes. So in order to avoid creating new DBConnections every time I needed one in every class, I thought I'd just create 1, and pass that same one around from method to method as a method-call argument. I believe what you were pointing out is that is incorrect because every time a method receives a DBConnection as an argument, it makes a new copy of the DBConnection object for itself. And that's the reason why I have a new connection being created for each SQL call - because as I pass it around, in actuality I'm really creating a new one with each method call!

Is that correct?

If that is all correct, it would seem to me the solution is as follows:
Eliminate DBConnection from being passed as an argument between methods. Instantiate it only once (in the main class) - as I have been doing - but I must make it public and static. In that way I could access that same singular instantiation from all classes without ever once replicating the object itself.

How does that sound?

Thanks again,
RebLazer

 

[RebLazer]

Well, I tried what I said in the previous post (namely having the lone instantiation of DBConnection as a public static instantitation in my main class instead of passing it around between methods as an argument). But it did not work. I'm still bombing out with "Error creating socket" errors and still have as many as 12 connections.

If anyone has any ideas, suggestions, or wild guesses, please share...

Thanks!
RebLazer

 

[wushutwist]

Actually you are quite a bit off. You are missing the basics of OOP.

My point was that InsertSequence IS a DBConnection, in other words InsertSequence extends from DBConnection. This means that InsertSequence has all of the characteristics of a DBConnection and therefore it makes no logical sense to have the methods in InsertSequence take a DBConnection as a reference. The way it is right now, everytime you create an InsertSequence object then it is also going to open a Connection to the database.

Your best bet is to change InsertSequence so that it does not extend from DBConnection and then it would make sense to take a DBConnection as a parameter.

As for your comment about passing parameters. All objects in Java are passed by reference and therefore a new copy of the object is NOT created.