Solved

abnormal closing sql connection

Posted on 2002-05-24
17
176 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
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.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

705 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

18 Experts available now in Live!

Get 1:1 Help Now