Solved

abnormal closing sql connection

Posted on 2002-05-24
17
178 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
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 scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

776 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