Solved

jdbc connection timeout SQL Server not working

Posted on 2009-07-14
17
2,216 Views
Last Modified: 2012-05-07
I am trying to set a connection timeout from Linux/jsp to Windows/SQL Server. I've added "loginTimeout=10;" to my connection string per the driver instructions: https://www.ohprs.org/ohprs/jdbchelp/html/f1b62700-f046-488d-bd6b-a5cd8fc345b7.htm, but it doesn't work. The connection attempt hangs forever waiting on the server if it is down. Am I doing something wrong? Incorrectly? Not enough?

I am using Microsoft SQL Server 2005 JDBC Driver 1.1 on Linux, SQL Server 2005 on Windows
0
Comment
Question by:jmarkfoley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +1
17 Comments
 
LVL 14

Expert Comment

by:flob9
ID: 24847649
Try to upgrade to jdbc driver 1.2.  
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24847790
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 24847988
I downloaded Microsofts 2.0 driver. That does seem to be giving me a timeout (I think. The db server was down during my first test. Now it is back up and I am altering the connect string to give a fake db name). Anyway, the 'catch' code shown below traps the error, but I have a status of 0. The message displayed is:

Sorry, unable to connect with Database Server. Please try again later. Status: 0 The connection to the host server, named instance traverse106 has failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names, check that no firewall is blocking UDP traffic to port 1434, and for SQL Server 2005 or later verify that the SQL Server Browser Service is running on the host.

I'm not sure why it's returning a zero status. Should I be trying to trap a different exception? I could arbitrarily do a System.exit() with a made-up status, but I'd rather exit with the correct error.

Thots?

    try {
        Class.forName( dbDriver );
 
        con = DriverManager.getConnection( dbURL );
    }
    catch (SQLException e) {
        System.out.println("Sorry, unable to connect with Database Server. " +
            "Please try again later. Status: " + e.getErrorCode() + " " +
            e.getMessage());
        System.exit(e.getErrorCode());
    }
    catch (ClassNotFoundException x) {
        System.out.println(x);
    }

Open in new window

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 86

Expert Comment

by:CEHJ
ID: 24848011
You can only really work with the code you're given. Zero could be correct though
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24848015
(although not correct for System.exit of course)
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 24848220
what if I try catching java.net.SocketTimeoutException? but it seems to me it would do the catch logic for SQLException if there were a SOcketTImeoutException
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 24848240
Nope. Trying to use SocketTimeoutException gives me:

nasql.java:136: exception java.net.SocketTimeoutException is never thrown in body of corresponding try statement
    catch (SocketTimeoutException x) {
    ^
1 error

so, zero is it, eh?
0
 
LVL 14

Expert Comment

by:flob9
ID: 24848263
Yes you should catch the socketexception. The sql error code does not means anything since the problem occur before connection to the database.

0
 
LVL 14

Expert Comment

by:flob9
ID: 24848333
You can catch the default Exception after the ClassNotFoundException ...
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24848339
What does the following give you?

a. System.out.println(e.getClass());

b. System.out.println(e.getCause().getClass());
0
 
LVL 14

Expert Comment

by:flob9
ID: 24848411
Also check e.getSQLState() and e.getNextException()
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24848527
Probably a good idea to cross-post the question to the SQL Server zone next time.

Have you configured the SQL Server to listen for network connections? Out of the box it does not without a config change.

If not sure: Run SQL Server Configuration Manager -> SQL Server 2005 Network COnfiguration -> Protocols for {DB} -> Double click TCP/IP
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 24851742
There seems to be lots of good suggestions here ...

mrjoltcola, the SQL server connection works fine normally. The application at hand is a java program run from a bash shell as a scheduled (cron) job. I'm just trying to catch those rare instances where the server is down so I can exit with an appropriate code to prevent continuation of the down-stream bash script.

 CEHJ/flob9, my try/catch code is shown above in post 24847988. where do I stick your recommended System.out.println(e.getClass()); System.out.println(e.getCause().getClass()); e.getSQLState() and e.getNextException() ? In the catch (SQLException e) block? When I do that, System.out.println(e.getClass()): gives me:
class com.microsoft.sqlserver.jdbc.SQLServerException

System.out.println(e.getCause().getClass()); give me: nullPointerException

System.out.println(e.getSQLState()); gives me: 08S01

Not quite sue what to do with e.getNextException(). doing ...println() just gives me 'null'.

flob9, when I stick: catch (Exception q) { System.out.println(q); } after the class not found exception  per your suggestion in 24848333 it never goes there. It is still trapped in the SQLException
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 500 total points
ID: 24851886
You just need to use (preferably) a unique id for that error. getErrorCode or getSQLState should be OK. Then exit the app
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 24853688
OK, I'll just do:  System.exit((e.getErrorCode() == 0) ? -1 : e.getErrorCode()));

I suppose that's the best I can do? Other thots, anyone?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24853885
Well there could be other exceptions where you would want to exit the script so you should probably be thinking about that too
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24854288
:-)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Android development question 2 108
sql import cannot be resolved jsp 3 113
spring maven example issues 3 97
Find out all repos that a user is most active on Github 1 56
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Suggested Courses

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question