closed Connection

[Frank Boeschemeier]

Hello,

i have a problem with closed connections when i begin a transaction. At the project im working on the database is connectet at 4.00 am to get data. the connection is not closed after that. At about 9.00 am the next transaction should be done. At this time sometime the statement could not be executed. The code looks like this:

setAutocommit(false);
(do something)
commit();

my problem is, that setAutoCommit(false) throws the following exception:

java.sql.SQLException: Invalid state, the Connection object is closed.at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java(Inlined Compiled Code)) at net.sourceforge.jtds.jdbc.ConnectionJDBC2.setAutoCommit(ConnectionJDBC2.java(Compiled Code)) at logware.common.db.arts.physical.DatabaseSession.beginTransaction(DatabaseSession.java:765)

I know, that i have to catch the Exception and try to reconnect, but why is the connection not opend anymore?

At the code there is never called "Connection.close".

We use M$SQL-Server 2000 on a Windows 2000 Server.

Formerly we used the "una"-jdb-driver from i-net software without these problems.

The exception is more often thrown the longer the connection is unused.

Any ideas?

Frank

[John Craig]

Hi Frank,

If it weren't for the fact that this worked without this problem previously with another JDBC package, I'd say it was clearly a problem with the socket being closed by the server or network infrastructure. However, that's still something to check. It's pretty common for network infrastructure to disallow open, but inactive sockets (with the actual timeout varying based on settings). You might also check the keep-alive settings on your server and client IP stacks--if they're not exchanging some messages periodically, the socket is going to close anyway.

If your transactions with the database are that infrequent, you might consider restructuring the program so that it didn't attempt to keep the connection open (as that will always be somewhat unreliable--a hiccup on the network will cause problems from time-to-time even if it's not a consistent problem, I'd guess).

At any rate, check your network settings; that might be the source of the changed behavior (if your IT folks slipped in some extra protections without announcing it, for instance).

John

[cfwong]

Can I use idle-timeout-minutes="0" in the xml file to solve the problem?

[Frank Boeschemeier]

Hi John,

thanks for your answer.

we still have these problems. To prevent them, i wrote a wrapper, which checks the connection. If the connection was closed, the wrapper makes a new connection. But when i deliver i.e. a createStatement-Object, there are still problems when this Object was deliverd well but the execution of the statement fails.

I take a look at the sourcecoude and found that every time there is a IOException within the coomunication with the database the connection will be closed. Perhaps its possible not to close the connection immediately but rather tries it to make the action once more.

[Frank Boeschemeier]

Hello @all,

The problem occurs on different places by pure chance.
Enclosed i made a cutaway from the logfile.
The connection cloud be established, but after all the statement could not be executed. jTDS v.1.1 is used.

---------------shnipp------------
12:57:01 Error writing result
java.sql.SQLException: I/O Error: Connection reset
at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java(Compiled Code))
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:436)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:418)
at logware.common.db.arts.physical.LwJobExecution.update(LwJobExecution.java:503)
at logware.kaufland.common.jobs.io.database.DBSerializer.internalWriteResult(DBSerializer.java:1572)
at logware.kaufland.common.jobs.io.database.DBSerializer.internalWriteResult(DBSerializer.java:1580)
at logware.kaufland.common.jobs.io.database.DBSerializer.writeResult(DBSerializer.java:1140)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.write(PersistentExecutionResult.java:126)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.setEndTime(PersistentExecutionResult.java:117)
at logware.kaufland.common.jobs.AbstractExecutable.executionFinished(AbstractExecutable.java:127)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:155)
at logware.kaufland.common.jobs.JobImpl.executeStep(JobImpl.java:241)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:268)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:302)
at logware.kaufland.common.jobs.JobSequenceImpl.internalExecute(JobSequenceImpl.java:69)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AbstractJobSequenceSchedule.internalExecute(AbstractJobSequenceSchedule.java:200)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AsynchronousExecutableDecorator.run(AsynchronousExecutableDecorator.java:132)
at java.lang.Thread.run(Thread.java(Compiled Code))
Caused by: java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(SocketInputStream.java(Compiled Code))
at java.io.DataInputStream.readFully(DataInputStream.java(Compiled Code))
at java.io.DataInputStream.readFully(DataInputStream.java(Inlined Compiled Code))
at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java(Compiled Code))
at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java(Inlined Compiled Code))
at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java(Inlined Compiled Code))
at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java(Compiled Code))
at net.sourceforge.jtds.jdbc.ResponseStream.peek(ResponseStream.java(Inlined Compiled Code))
at net.sourceforge.jtds.jdbc.TdsCore.wait(TdsCore.java(Compiled Code))
... 20 more
12:57:01 Failed to rollback transaction
java.sql.SQLException: Invalid state, the Connection object is closed.
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java(Compiled Code))
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:1707)
at logware.common.db.arts.physical.DatabaseSession.rollbackTransaction(DatabaseSession.java:819)
at logware.kaufland.common.jobs.io.database.DBSerializer.cancelTransaction(DBSerializer.java:304)
at logware.kaufland.common.jobs.io.database.DBSerializer.writeResult(DBSerializer.java:1147)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.write(PersistentExecutionResult.java:126)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.setEndTime(PersistentExecutionResult.java:117)
at logware.kaufland.common.jobs.AbstractExecutable.executionFinished(AbstractExecutable.java:127)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:155)
at logware.kaufland.common.jobs.JobImpl.executeStep(JobImpl.java:241)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:268)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:302)
at logware.kaufland.common.jobs.JobSequenceImpl.internalExecute(JobSequenceImpl.java:69)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AbstractJobSequenceSchedule.internalExecute(AbstractJobSequenceSchedule.java:200)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AsynchronousExecutableDecorator.run(AsynchronousExecutableDecorator.java:132)
at java.lang.Thread.run(Thread.java(Compiled Code))
12:57:01 Failed to rollback transaction
java.sql.SQLException: Invalid state, the Connection object is closed.
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java(Compiled Code))
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:1707)
at logware.common.db.arts.physical.DatabaseSession.rollbackTransaction(DatabaseSession.java:819)
at logware.kaufland.common.jobs.io.database.DBSerializer.cancelTransaction(DBSerializer.java:304)
at logware.kaufland.common.jobs.io.database.DBSerializer.writeResult(DBSerializer.java:1147)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.write(PersistentExecutionResult.java:126)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.setResult(PersistentExecutionResult.java:101)
at logware.kaufland.common.jobs.AbstractExecutable.executionFinished(AbstractExecutable.java:128)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:155)
at logware.kaufland.common.jobs.JobImpl.executeStep(JobImpl.java:241)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:268)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:302)
at logware.kaufland.common.jobs.JobSequenceImpl.internalExecute(JobSequenceImpl.java:69)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AbstractJobSequenceSchedule.internalExecute(AbstractJobSequenceSchedule.java:200)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AsynchronousExecutableDecorator.run(AsynchronousExecutableDecorator.java:132)
at java.lang.Thread.run(Thread.java(Compiled Code))
12:57:01 Error executing step 'Fetch Import Files'
logware.common.exception.DatabaseRuntimeException: TransactionAlreadyOpen
at logware.common.db.arts.physical.DatabaseSession.beginTransaction(DatabaseSession.java:775)
at logware.kaufland.common.jobs.io.database.DBSerializer.beginTransaction(DBSerializer.java:254)
at logware.kaufland.common.jobs.io.database.DBSerializer.writeResult(DBSerializer.java:1139)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.write(PersistentExecutionResult.java:126)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.setResult(PersistentExecutionResult.java:101)
at logware.kaufland.common.jobs.AbstractExecutable.executionFinished(AbstractExecutable.java:128)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:155)
at logware.kaufland.common.jobs.JobImpl.executeStep(JobImpl.java:241)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:268)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:302)
at logware.kaufland.common.jobs.JobSequenceImpl.internalExecute(JobSequenceImpl.java:69)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AbstractJobSequenceSchedule.internalExecute(AbstractJobSequenceSchedule.java:200)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AsynchronousExecutableDecorator.run(AsynchronousExecutableDecorator.java:132)
at java.lang.Thread.run(Thread.java(Compiled Code))
12:57:01 Failed to rollback transaction
java.sql.SQLException: Invalid state, the Connection object is closed.
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java(Compiled Code))
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:1707)
at logware.common.db.arts.physical.DatabaseSession.rollbackTransaction(DatabaseSession.java:819)
at logware.kaufland.common.jobs.io.database.DBSerializer.cancelTransaction(DBSerializer.java:304)
at logware.kaufland.common.jobs.io.database.DBSerializer.writeResult(DBSerializer.java:1147)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.write(PersistentExecutionResult.java:126)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.setEndTime(PersistentExecutionResult.java:117)
at logware.kaufland.common.jobs.AbstractExecutable.executionFinished(AbstractExecutable.java:127)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:292)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:302)
at logware.kaufland.common.jobs.JobSequenceImpl.internalExecute(JobSequenceImpl.java:69)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AbstractJobSequenceSchedule.internalExecute(AbstractJobSequenceSchedule.java:200)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AsynchronousExecutableDecorator.run(AsynchronousExecutableDecorator.java:132)
at java.lang.Thread.run(Thread.java(Compiled Code))
12:57:01 Failed to execute job 'Process Import Files'
logware.common.exception.DatabaseRuntimeException: TransactionAlreadyOpen
at logware.common.db.arts.physical.DatabaseSession.beginTransaction(DatabaseSession.java:775)
at logware.kaufland.common.jobs.io.database.DBSerializer.beginTransaction(DBSerializer.java:254)
at logware.kaufland.common.jobs.io.database.DBSerializer.writeResult(DBSerializer.java:1139)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.write(PersistentExecutionResult.java:126)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.setEndTime(PersistentExecutionResult.java:117)
at logware.kaufland.common.jobs.AbstractExecutable.executionFinished(AbstractExecutable.java:127)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:292)
at logware.kaufland.common.jobs.JobImpl.execute(JobImpl.java:302)
at logware.kaufland.common.jobs.JobSequenceImpl.internalExecute(JobSequenceImpl.java:69)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AbstractJobSequenceSchedule.internalExecute(AbstractJobSequenceSchedule.java:200)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:153)
at logware.kaufland.common.jobs.AsynchronousExecutableDecorator.run(AsynchronousExecutableDecorator.java:132)
at java.lang.Thread.run(Thread.java(Compiled Code))
12:57:01 Failed to rollback transaction
java.sql.SQLException: Invalid state, the Connection object is closed.
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java(Compiled Code))
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:1707)
at logware.common.db.arts.physical.DatabaseSession.rollbackTransaction(DatabaseSession.java:819)
at logware.kaufland.common.jobs.io.database.DBSerializer.cancelTransaction(DBSerializer.java:304)
at logware.kaufland.common.jobs.io.database.DBSerializer.writeResult(DBSerializer.java:1147)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.write(PersistentExecutionResult.java:126)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.setEndTime(PersistentExecutionResult.java:117)
at logware.kaufland.common.jobs.AbstractExecutable.executionFinished(AbstractExecutable.java:127)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:155)
at logware.kaufland.common.jobs.AsynchronousExecutableDecorator.run(AsynchronousExecutableDecorator.java:132)
at java.lang.Thread.run(Thread.java(Compiled Code))
12:57:01 Execution of executable 'Process Import Files' failed
logware.common.exception.DatabaseRuntimeException: TransactionAlreadyOpen
at logware.common.db.arts.physical.DatabaseSession.beginTransaction(DatabaseSession.java:775)
at logware.kaufland.common.jobs.io.database.DBSerializer.beginTransaction(DBSerializer.java:254)
at logware.kaufland.common.jobs.io.database.DBSerializer.writeResult(DBSerializer.java:1139)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.write(PersistentExecutionResult.java:126)
at logware.kaufland.common.jobs.io.PersistentExecutionResult.setEndTime(PersistentExecutionResult.java:117)
at logware.kaufland.common.jobs.AbstractExecutable.executionFinished(AbstractExecutable.java:127)
at logware.kaufland.common.jobs.AbstractExecutable.execute(AbstractExecutable.java:155)
at logware.kaufland.common.jobs.AsynchronousExecutableDecorator.run(AsynchronousExecutableDecorator.java:132)
at java.lang.Thread.run(Thread.java(Compiled Code))

Perhaps this could be usefull.

best regards

Frank

[Alin Sinpalean]

Here's what I see is happening:

java.sql.SQLException: I/O Error: Connection reset
at ...
Caused by: java.net.SocketException: Connection reset
at ...

Followed by a number of "java.sql.SQLException: Invalid state, the Connection object is closed.".

Which means that the socket connection is reset first (because of a timeout or network outage or something else out of jTDS' or Java's control). This triggers the "java.sql.SQLException: I/O Error: Connection reset" thrown by jTDS and causes jTDS to forcefully close the JDBC Connection. After that all method calls on the JDBC Connection return the "Invalid state, the Connection object is closed" message.

Nothing unusual here. Use a pool with a validation query and you should be all set.

Alin.

[Frank Boeschemeier]

hello @all

Sorry, that i haven´t answered allready.

The problem occurs not over a wired connection. The Database is just stored on the same computer as the application. So there is no effort to communicate over the ethernet. I know, that the communication is made via the TCP/IP layer. Using named pipes was successfull but slower than the communication over the TCP/IP-layer!?! I don´t know, why. Perhaps there is anybody who has the same problem and has already resolved it.

Frank

[eringo]

I encountered a similar problem in my system too when I am using jtds-1.2.jar on a linux of solaris environment. I am seeing "Invalid state, the Connection object is closed." when a connection tries to create a statement or when a connection is committed.

I don't believe the connection-closed problem is related to the socket setting of my environment, because I managed to find out why the connection is forced to be closed. Whenever TdsCore.nextToken() method sees an invalid token from RespondStream, it will throw a ProtocolException. In this case, TdsCore will close the connection.

Here is one example. When a result set is closed, an invalid protocol occurs:
net.sourceforge.jtds.jdbc.ProtocolException: Invalid packet type 0x0
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2277)
at net.sourceforge.jtds.jdbc.TdsCore.getNextRow(TdsCore.java:762)
at net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:593)
at net.sourceforge.jtds.jdbc.JtdsResultSet.close(JtdsResultSet.java:486)
When this happens, no matter the connection is committed or rolled back, it will report the connection is closed:
java.sql.SQLException: Invalid state, the Connection object is closed.
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1494)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.commit(ConnectionJDBC2.java:1874)

In what case will packet type 0x0 occur?

Ringo.

[Alin Sinpalean]

Ringo,

Actually the connection could be closed for a lot of reasons (timeouts, I/O errors and protocol errors, as you discovered).

In your case it seems like a very weird issue that is not supposed to happen. A protocol exception means that jTDS did not understand the server's response; and from the error message ("Invalid packet type 0x0") it seems like it's not actually a problem of jTDS not understanding the answer but probably a synchronization or buffering issue.

But looking at the stacktrace I don't think it's generated by jTDS 1.2, or even 1.1. Please check your classpath and make sure you don't have an older version of jTDS lying around.

Alin.

[eringo]

Hi Alin,

Thanks a lot for your analysis. You are correct that the stack trace is not from the distributed jtds-1.2.jar. But it is from the jar that I compiled from the jtds 1.2 source codes that I downloaded. Here is the part of source codes of TdsCore.nextToken() from the 1.2 source:
.......
.......
} catch (ProtocolException pe) {
pe.printStackTrace();
connection.setClosed();
throw Support.linkException(
new SQLException(
Messages.get(
"error.generic.tdserror", pe.getMessage()),
"08S01"), pe);
The stack trace is from pe.printStackTrace(). I used this stack trace to determine where the problem was.

A little background about my work. My application contains jtds-1.2.jar only. This is the first time that we use jtds driver, so we don't have any older versions. And we use this driver to connect to Sybase ASE 12.5. We will create multiple statements for different queries within one single transaction. Each of these statements will create one single result set. When all the result sets are exhausted, they are closed. So are thier statement objects. And then the connection will be committed. I believe jtds driver is allowed to have multiple statements object created per connection in each transaction, right? Do you think there are some kinds of synchronization issue in this condition?

-Ringo.

[Alin Sinpalean]

Ringo,

It does sound, indeed, like there's a race condition somewhere in jTDS. We've been fixing this type of issues for years (literally) and it looks like there may still be some very uncommon situations in which problems can occur. It would be great if you could provide a repro case (even if it reproduces the error once in 100 runs). Otherwise it's virtually impossible for us to see where this may come from.

It is indeed possible and (theoretically) perfectly safe to have multiple Statements, each with an opened ResultSet at the same time. Although this may lead to some possibly serious performance issues. Also, not reading all rows in a ResultSet is perfectly fine from a standards point of view; however, in practice this means that the driver will still have to go through all the unread rows and consume them.

The recommended approach is to limit the number of returned rows before executing the query (using setMaxRows). This may not be possible in some very unusual situations, but in most cases it's just common sense.

As I said, we would really appreciate if you could provide a simple (or not so simple) repro case.

Alin.

[AP]

Hi Ringo,
Did you get any solution on this issue? Thanks

-AP

[eringo]

Hi Alin,

Here is another information I'd like to add. In our testing, we won't read all the rows from result sets before closing them. This way of using jtds driver may attribute to the invalid packet in closing result sets.

-Ringo.

[eringo]

Hi Alin,

I am more than happy to write a reproducible case for you. Right now I am stuck with my project. When I am done with it, I will provide you a test case for this issue. I'll keep you posted.

Thank you,
-Ringo.

[eringo]

Hi Alin,

I have two small programs that can reproduce the different invalid packet problems. For the first program, you need to pass a query that takes a long time to finish executing. This will give enough time for Statement.close() to interfere with Statement.executeQuery() call. Here is the first program:

import java.io.;
import java.sql.;
import java.util.*;

public class TestInvalidPacket {
public static void main(String[] args) {
String driver = args[0];
String url = args[1];
String login = args[2];
String pwd = args[3];
String sql1 = args[4];
Connection conn = null;

    System.out.println(url);
    System.out.println(login);
    System.out.println(pwd);
    try {
        Class.forName(driver);

        conn = DriverManager.getConnection(url, login, pwd);
        conn.setAutoCommit(false);

        Statement stmt = conn.createStatement();
        Thread t1 = new ExecThread(stmt, sql1);
        Thread t2 = new CloseThread(stmt);
        t1.start();
        t2.start();

        t1.join();
        t2.join();
        conn.commit();
    } catch (Exception e) {
        System.out.println("Unable to commit transaction: " + e);
    } finally {  
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception ignore) {}
        }
    }
}

static class ExecThread extends Thread {
    Statement stmt = null;
    String sql = null;

    ExecThread(Statement st, String str) {
        stmt = st;
        sql = str;
    }

    public void run() {
        ResultSet rs = null;
        try {
            //Note: the query to be run here needs to be a long running query.
            //It needs to take a long time for the query to return the first row here.
            //We have to do it right so that stmt.close() is called in the middle of
            //stmt.executeQuery().
            rs = stmt.executeQuery(sql);

            ResultSetMetaData meta = rs.getMetaData();
            int size = meta.getColumnCount(); 
            while (rs.next()) {
                for (int ndx = 1; ndx <= size; ndx++) {
                    String value = rs.getString(ndx);
                }
            }
        } catch (Exception e) {
            System.out.println("Fail to run query: ");
            e.printStackTrace();
        } finally {
            closeResultSet(rs);
        }
    }

    private void closeResultSet(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception ignore) {}
    }
}

static class CloseThread extends Thread {
    Statement stmt = null;

    CloseThread(Statement s) {
        stmt = s;
    }

    public void run() {
        try {
            Thread.sleep(100);
            if (stmt != null) {
                stmt.close();
            } 
        } catch (Exception e) {
            System.out.println("Fail to close statement: ");
            e.printStackTrace();
        }

    }
}

}

The second program will be in a separate message.

-Ringo.

[eringo]

The second program will cause "invalid packet" problem between ResultSet.next() and ResultSet.close() methods.

import java.io.;
import java.sql.;
import java.util.*;

public class TestInvalidPacket2 {
public static void main(String[] args) {
String driver = args[0];
String url = args[1];
String login = args[2];
String pwd = args[3];
String sql1 = args[4];
Connection conn = null;
Statement stmt = null;

    System.out.println(url);
    System.out.println(login);
    System.out.println(pwd);
    while (true) {
        try {
            Class.forName(driver);

            conn = DriverManager.getConnection(url, login, pwd);
            conn.setAutoCommit(false);

            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql1);
            Thread t1 = new ExecThread(rs);
            Thread t2 = new CloseThread(rs);
            t1.start();
            t2.start();

            t1.join();
            t2.join();
            stmt.close();
            conn.commit();
            System.out.print(".");
        } catch (Exception e) {
            System.out.println("Unable to commit transaction: " + e);
        } finally {  
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception ignore) {}
            }
        }
    }
}

static class ExecThread extends Thread {
    ResultSet rs;

    ExecThread(ResultSet r) {
        rs = r;
    }

    public void run() {
        try {
            //Note: in this case, there needs to be lots of rows to be 
            //processed.  rs.next() can't return false before rs.close() finishes.
            //In another words, the result set needs to still contain rows when
            //rs.close() is called.
            ResultSetMetaData meta = rs.getMetaData();
            int size = meta.getColumnCount(); 
            while (rs.next()) {
                for (int ndx = 1; ndx <= size; ndx++) {
                    String value = rs.getString(ndx);
                }
            }
        } catch (Exception e) {
            System.out.println("Fail to run get results: ");
            e.printStackTrace();
        }
    }
}

static class CloseThread extends Thread {
    ResultSet rs = null;

    CloseThread(ResultSet r) {
        rs = r;
    }

    public void run() {
        try {
            Thread.sleep(100);
            closeResultSet(rs);
        } catch (Exception e) {
            System.out.println("Fail to close statement: ");
            e.printStackTrace();
        }

    }

    private void closeResultSet(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception ignore) {}
    }
}

}

In most cases, developers will not call ResultSet.next() and ResultSet.close() in separate threads. But it is necessary in our system. Could you see if there is a way to avoid this probelm in the driver side?

Thank you,
-Ringo.

[Navaneetha Krishnan J]

I also get the same exception like Alin said before

java.sql.SQLException: I/O Error: Connection reset
Caused by: java.net.SocketException: Connection reset

Followed by a number of "java.sql.SQLException: Invalid state, the Connection object is closed.".
Some time Invalid state, the Statement object is closed."

As suggested, i have checked out the network problem and time out, and both does not seems to be an issue for me. Because my application send queries continuously (Checked it with mssql profiler). Meantime i do not see any wrong packet exception.

Is there any other thing i needto/could check out. Any kind of Help is much appreciated.

Thanks for the help,
Navaneeth.

[AP]

Hi,
I am also facing the same problem. Is there any final solution? I am using jtds1.2.4.jar

Your help appeciated! I am facing this problem for past 6 months.

[Pavi]

I also get the same exception . Restarting the server solved this issue. Does anyone know the reason.