Help please!!! Is there a method for closing sessions java-side?

[Barbara Norway]

Hi,

What follows is a Java question and not an Oracle one. I haven't gotten any response so I thought I'd edit this original post to say it's Java help I'm looking for not Oracle

I'm an Oracle DBA so am just learning a little about Java (and trying to learn more). I have two big Java apps I'm responsible for : one largely JSP but it's releasing its Oracle sessions properly now and two, a Java graphical application (In two jars). I'm hoping for a high-level response first to help me head in the right direction so I'm not including any code (yet). My problem is this:

The application allows anyone on the net to create a graph using data from our database. Each time they create a new graph, it opens a new connection and Oracle session. Though the session becaomes INACTIVE (select status from V$session), it remains after the enduser is done with the Java app on the web.


After all that background, here is the question: What type of statements should be used in Java to do the housekeeping of closing the session after some period of time? I imagine there are various ways to do this but I want to have a general idea on how to proceed (though specifics are welcome too

I realize there are various remedies I can take and have been taking on the Oracle side but I believe the elegant solution is to fix it at the Java level, no?

Thanks very much, in advance, for your help.
Barbara

[ September 02, 2004: Message edited by: Barbara Norway ]

[ September 02, 2004: Message edited by: Barbara Norway ]
[ September 03, 2004: Message edited by: Barbara Norway ]

[JulianInactive KennedyInactive]

Hi Barbara,

OK, I've decided to start this again as I think I've just realised what your problem is. Your Java app is creating a DB connection at the client end. When the user goes away leaving the app running or closes the app down without going through the polite procedure it fails to close the connection. The quick solution is therefore to ensure that the client connection is closed down every time the app exits or the connection times out. Runtime.getRuntime().addShutdownHook() can be used as a catch-all, but it's better to do it in a controlled manner too. A better (and safer) solution might be to modify the client app to connect to a server app which itself connects the database, perhaps maintaining a connection pool. Of course you can't guarantee that connections will always be closed down cleanly client-side, or even server-side, so you'll still have to set connection timeouts and/or manage connections on the DB server.

My original thinking follows, in case that helps you at all.

Do you know if your errant Java app is using connection pooling? Judging by what you're saying ("each time they create a new graph, it opens a new connection and Oracle session") it's not. One approach to limit the number of Oracle sessions used would be to implement connection pooling (open a pool of connections to begin with an just use them as they're required). However, that doesn't help to solve your problem of the Java app failing to close connections when the client logs off or their session times out.

It's not clear from your posting if it's an Internet application (the net?) or if the "graphical application" runs as an applet or a full-blown client/server desktop app that connects to your server. Regardless of the architecture however, your Java app needs to detect when a user logs off or a session times out and close the connection or return it to the pool accordingly.

How you actually do this depends on your architecture, but it should be pretty simple stuff. Chances are that the code is already attempting to do that but is just failing due to some faulty logic.

Hope this helps.

Jules

[Barbara Norway]

Thanks Jules! I'm sorry I didn't say that the app is a servlet that allows anyone with a specified handful of browsers (Safari on Mac, IE on windows with our site specifying the versions we've tested, etc.) to use the Java interface to the data. It's not a Java Web Service. It is a signed .jar as the client must make a conscious decision whether to allow us to write to their disk to save a few files so they can reload their graph again, or a jpg file so they can get a static image of their graph. Fortunately there is a Java programmer who can help me with this as I am out of my depths and still too far down on the learning curve. But I would like to be able to be involved in resolving this problem (and not just on the Oracle side). I appreciate help so much and hope that as time goes by I become much more self sufficient in Java.

THanks again.
Barbara

[Barbara Norway]

Hi,

I'm still not sure how to go about resolving this on the Java side. Should I be asking these things in the Java beginner forum? I don't really understand enough Java to parse the reply I received. I don't want to handle it with Oracle as this should be dealt with by the Java web app that initiates the session I think. Please let me know if I should move this to another forum to get the help I need. I'm sorry that I don't know enough Java yet to tackle this one.

Cheers,
Barbara

[JulianInactive KennedyInactive]

Barbara,

If you have a Java programmer on hand, who is probably going to have to make the required modifications, why don't you ask him/her what the solution should be? You don't appear to be very confident in this person's abilities.

Also cross-posting in other forums is generally frowned upon. On the face of it this is a JDBC issue, though it may have an applet or servlet dimension. It has no place in Java In General (beginner).

At the moment it's still unclear to me whether the JDBC connection is established in a signed applet, a stand-alone app deployed in a signed JAR file or in a servlet.

You say signed JAR but then you mention servlets and browsers, which makes me think that you mean signed applet. Is the signed applet served as part a web page after a request to the servlet? If so, I would expect that the applet probably connects to the database directly, bypassing the servlet (this is not a Good Thing). In this case you would need code in the applet to close the JDBC connection when the applet goes out of use. I can't really help here. [Edit: just had a quick look at the java.applet.Applet class; it has stop() and destroy() methods which may be of use but I can't advise on best practice]

If the JDBC connection is made server-side then the solution is a whole lot simpler. As you can see, there are still too many unknowns to give a useful answer, other than just "close the JDBC connection (con.close()) when you've finished with it".

Jules
[ September 03, 2004: Message edited by: Julian Kennedy ]

[Barbara Norway]

Hi Julian, and thanks so much for all your time and help.

I'm afraid my situation when posting at Javaranch is uncomfortable for me because I have such a relatively modest experience level with Java (my background is Oracle DBA and Unix Sys Admin) but yet in the job I have I need to perform these advanced Java and JSP programmers functions in addition to the things I know how to do. I didn't misrepresent on an interview , it's just that I have a boss who thinks it's all trivial compared to the high-powered science he does (not an IT person). So, I do try to answer the few questions I can try to help with in areas such as Tomcat and Oracle on Javaranch but I'm usually trying to get some general direction when I ask a question.

I know not to ask someone to do my work for me but sometimes I'm hoping one of you experts can give me a push in the right direction. But, then again, when I do get an answer sometimes I cannot figure it out. I am trying hard (and studying hard) to catch up so I won't be buggin' y'all so much. So I cross-posted because I thought maybe I'd get an answer I could understand better in the beginner forum. I'm sorry. The java programmer is someone who is in another department at the University, is quite competent, but must be paid and will someday leave us altogether, and more importantly, if I don't have some clue as to what's going on, will code in a vacuum and give me the compiled .class files.

So this all was a way for me to open the dialogue with the person in another department and be involved at some level so we could work together a bit to arrive at a solution. I felt I could help the programmer, and have in the past, with the Oracle and, to some extent, the JDBC.

So, in summary, getting a high-level clue would help me work in tandem rather than just having the other person do it all alone. They originally did the application thinking it was going to be ok as is. But I discovered that Oracle had lots and lots of inactive sessions in the course of my routine DBA monitoring and so fortunately end users haven't seen the problem yet.

Again, thanks!!! and sorry for the bad X-posting ettiquette.

Barbara
[ September 03, 2004: Message edited by: Barbara Norway ]

[Barbara Norway]

P.S. In googling the web I saw some references to

 connection.close() 

and

 finally {
releaseConnection();
} 

as well as

  finally {
if(connection != null) {
   try {
     connection.close();
        } catch (JMSException e) {}
    }
}

but then JMS is not JDBC ...

[JulianInactive KennedyInactive]

The basic principle is the same for JMS connections as for JDBC connections, i.e. you should explicity close them in a finally clause. The key difference is that you're using a java.sql.Connection rather than a javax.jms.Connection.

I'd be surprised if the code doesn't contain a call to con.close() somewhere. The problem is more likely to be that it's not being executed under certain circumstances, e.g. if a user just closes the browser or opens another page in the same window.

If I were in your position I'd probably get the Java programmer to walk me through the code (or perhaps just the process) explaining when the JDBC connection is closed and pose a few "what if" questions. What if the user just closes the browser? What if the user leaves the page open in their browser overnight? What if IE has one of its turns and just craps out? It would also be useful if you could get some idea of how the different layers or components of the application hang together (the architecture).

If your programmer doesn't see any holes in the implementation that way you might find it useful to post the relevant code snippets here. Then people here can really help you.

Jules

[Barbara Norway]

Thanks again Julian. This last post from you was the most helpful of all for me. I will take your advice and thanks for your patience!

Cheers!

Barbara