Solved

abnormal closing sql connection

Posted on 2002-05-24
17
181 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 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

691 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