Solved

abnormal closing sql connection

Posted on 2002-05-24
17
177 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
17 Comments
 
LVL 92

Accepted Solution

by:
objects earned 50 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
wordmultiple challenge 12 116
Systems talking to each other 5 123
Unexpected HttpURLConnection connection behavior 2 84
GUI builder for Eclipse? 8 27
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…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
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…

932 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now