JDBC connection hangs with no response from SQL Server 2008 r2

Question

When connecting to SQL Server 2008 (express locally, full server in production) this works fine for me when developing on my local machine, but this thing just hangs in production.

Here is the code :

package oata;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import sun.applet.Main;

public class Sql {

    public static final String SQLDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    protected Connection conn = null;
    private String ip = "";
    private int port = 0;
    private String databaseName = "";
    private String db_userid = "";
    private String db_password = "";

    public void callDb(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
        System.out.println("Initialising variables");
        ip = args[0];
        port =  Integer.parseInt(args[1]);
        databaseName = args[2];
        db_userid = args[3];
        db_password = args[4];
        try{
        Log logger = LogFactory.getLog(Main.class);
        System.out.println("Opening logger...");
        logger.debug("opening driver " + SQLDRIVER);
        System.out.println("Creating connection instance...");
        Class.forName(SQLDRIVER).newInstance();
        System.out.println("Driver Manager.getConnection...");
        conn = DriverManager.getConnection(getDBURL(), db_userid, db_password);
        System.out.println("Connection prepare statement...");
        PreparedStatement ps = conn.prepareStatement("select * from nstupersonal");
        System.out.println("Executing query...");
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            System.out.println(rs.getString("StudentId"));
        }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }

        conn.close();
    }


    private String getDBURL(){
                String url = "";
                try {
                    url = "jdbc:sqlserver://" + ip
                    +":" + port +";databaseName="+
                     databaseName + ";user=" + db_userid + ";password="+db_password;
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }   
                return url;
            } 

}

That's the code that gets run. In the console it literally just hangs in production. It always seems to hang on SQL server 2008 but works fine in production for all my other customers. Runs simiply with...

TCP/IP is enabled on 1433 in SQL Server and allow remote connections is set to true in the management console.

java -jar ipaddress port dbname user password

Any ideas? Is the SQL driver wrong? I'm using sqljdbc4.jar

The resulting command prompt is...

Initialising variables Opening logger... Driver Manager.getConnection...

No exception gets thrown

Thanks,

D

Where does it hang? Can you insert more logging statements to track down the line which causes it to hang? — beny23, Jan 24 '12 at 12:35
Updated code to have System outs and throw messages but an exception is never thrown. — David, Jan 24 '12 at 13:10

score 11 · Accepted Answer · answered Jan 24 '12 at 14:50

11

If production is running on Java 6 Update 29, then either upgrade to Java 6 Update 30 or downgrade to Java 6 update 27(?). Update 29 contains a bug where SSL doesn't work correctly and the connection to SQL Server hangs.

answered Jan 24 '12 at 14:50

Mark Rotteveel

100,966
191
140
197

score 1 · Answer 2 · answered Dec 31 '12 at 12:35

I encountered the same behavior with MS SQL Server 2012 + JDBC 4.0 driver and once again JDK 6.0 update 29 was the problem. The connection hanged on first call to executeQuery() or even with setAutoCommit(false) before any query was issued. After updating to JDK 6.0 update 37 all works fine.

HTH, Michal

score 1 · Answer 3 · answered Jan 24 '12 at 13:21

1

You can take a snapshot of your thread stack traces and see where it gets stuck. If its in socket reading - something wrong in db (I think this would be the case here).

answered Jan 24 '12 at 13:21

Mark Bramnik

39,963
4
57
97

hi, sorry, how do you view an exception of a single thread, would it not exception in the application? – David Jan 24 '12 at 13:33
Hi, no. Its not an exception per se, its a thread dump. It can be generated per thread. In java it can be done or programmatically (Thread.currentThread().getStackTrace(), there is also a method for all threads in the system ) or from JConsole – Mark Bramnik Jan 24 '12 at 14:51

JDBC connection hangs with no response from SQL Server 2008 r2

3 Answers3

Linked