?
Solved

abnormal closing sql connection

Posted on 2002-05-24
17
Medium Priority
?
184 Views
Last Modified: 2010-03-31
I have many processes connect to SQL server(host pc) and there are running at the backend all the time. If i try to terminate the process abnomally(eg : Shutdown pc), but the connection to SQL server is still alive. The connection is not close properly. If the process restart again, it will create another new connection. The maximum connection to sql server is set to 100 , So if the connection is more than 100, error will occur. My question is how can I handle the SQL connection so that the process termination will close the SQL connection properly?
0
Comment
Question by:chencc77
[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
17 Comments
 
LVL 92

Accepted Solution

by:
objects earned 200 total points
ID: 7031502
I'm not sure if there is anything you can do to close them automatically, you can just do your best to ensure that you close them before exitting the app. But for example if someone pulls the plug on the PC then there's not much you can do.
Setting a timeout on the server is probably the best you can do.
0
 

Author Comment

by:chencc77
ID: 7031525
My application is a scheduler, so it maybe sleep for a long time when there is no job to be executed. If i set the timeout for it, the server would stop the scheduler. So how server determine the connection is still activate.
0
 
LVL 92

Expert Comment

by:objects
ID: 7031531
I'm talking about the server timing out the JDBC connection. If your apps not using a connection for long periods of time it shouldn't really be keeping a connection open, especially if you're concerned about having too many connections open.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:chencc77
ID: 7031535
i am using java.sql class, i am not sure how to set the timing out properties.
0
 
LVL 92

Expert Comment

by:objects
ID: 7031547
The timeout is setup on the server, not the client.
0
 

Author Comment

by:chencc77
ID: 7031555
DO Timeout in server will solve the problem? How long the timeout will be set? if the scheduler only live once in a day, would timeout is usable?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 7031561
As objects says, the only way you could justify keeping an open connection for a scheduler is if its schedules were very frequent. Otherwise, you should open a connection when its schedule is due. In SQL Server 2000 there are the 'Connection Timeout' and 'Query Timeout' configuration settings. By default, these are set to 0, i.e. no timeouts. You could change one or other of these if necessary.
0
 
LVL 92

Expert Comment

by:objects
ID: 7031567
> DO Timeout in server will solve the problem?

They'll provide the best solution I can think of.
(I use them for exactly the same problem).

> How long the timeout will be set?

A little longer than the maximum time a JDBC connection will be left idle by the client.

> if the scheduler only live once in a day, would timeout is usable?

Does the scheduler keep a JDBC connection open all the time?
If you're concerned about how many connections are open, then I'd recomend that it doesn't, and only opens one when needed.
0
 

Author Comment

by:chencc77
ID: 7031590
>Does the scheduler keep a JDBC connection open all the time?
Yes

The app does open only one connection when it is started. The problem is if the app is stop abnormally (without using closeConnection), then the connection is still alive. So the number of connection is not reduce if app is terminate abnormally.

Is there any method to test the connection is still activate?
0
 
LVL 92

Expert Comment

by:objects
ID: 7031599
> Is there any method to test the connection is still activate?

On the server?? What is going to test it?
Not that I know of, and would depend on the db.


Holding a connection open indefinitely is not a good idea considering the fact that you are concerned about the number of open connections. I'd recomend closing it when not required.
This would also solve your original problem.
0
 

Author Comment

by:chencc77
ID: 7031600
import java.sql.*;
import java.rmi.*;
import java.rmi.server.UnicastRemoteObject;
import java.text.*;

public class RmiJdbcImpl extends UnicastRemoteObject implements RmiJdbc {

       private RJConn jrc [] = new RJConn [100];
     
        public RmiJdbcImpl () throws RemoteException {
          super();
       }

        public int openConnection() throws RemoteException, SQLException, ClassNotFoundException {
               
                int connectionId;

          // Loop through connection table until an empty slot is found.
          for (connectionId = 0; connectionId < jrc.length; connectionId++) {
                        if (jrc[connectionId] == null)
                                break;
              }

          // If no empty slots found, generate an error.
          if (connectionId >= jrc.length) {
                        System.out.println("WARNING: No more connection objects available");
               return -1;
              }

          // Create a connection for the new process and run it.
          jrc[connectionId] = new RJConn();
          jrc[connectionId].openConnection();

          // Return the connection identifier.
          return connectionId;
        }


        public void closeConnection(int id) throws RemoteException, SQLException {
          jrc[id].closeConnection();
          jrc[id] = null;
        }


}
//END

RJCon is a JDBC connection class. i set the limit to 100. In RJCon include openConn and closeConn. The apps will call openConn once it is execute. If it is stop without calling closeConn. The connection is still count in the jrc Array.
0
 

Author Comment

by:chencc77
ID: 7031609
>I'd recomend closing it when not required.

how to determine the connection is not required if the app is closed abnormally?
0
 
LVL 92

Expert Comment

by:objects
ID: 7031613
> how to determine the connection is not required if the
> app is closed abnormally?

It will be timed out by the server.
0
 
LVL 92

Expert Comment

by:objects
ID: 7031626
BTW, you should check if you JDBC implementation provides connection pooling. Many these days do, so implementing connection pooling yourself is not necessary.
0
 

Author Comment

by:chencc77
ID: 7031630
Ok..thanks, i will try on it.
0
 
LVL 35

Expert Comment

by:girionis
ID: 8797730
No comment has been added lately, so it's time to clean up this TA.

I will leave a recommendation in the Cleanup topic area that this question is:

- points to objects@idg

Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

girionis
Cleanup Volunteer
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
The viewer will learn how to implement Singleton Design Pattern in Java.
This video teaches viewers about errors in exception handling.
Suggested Courses
Course of the Month13 days, 6 hours left to enroll

777 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