java.sql.SQLException: Closed Connection

[saikrishna cinux]

hi,
i am running online exam application developed using jsp & servlets
it is running correclt with the single user
but it fails to run when 2 or more users run this application cuncurrently

it shows different types of exceptions on each execution
like this
java.sql.SQLException: Closed Connection
java.sql.SQLException: Fail to convert to internal representation

and i 've debugged the application but no chance sombody help me out please for the god sake

[Martin Simons]

I have the feeling that you have connection as an instance variable
in one your jsp's or servlets. This connection would then be shared
by two users with one user stepping on the toes of the other, either by
closing the connection or redefining the variable.

[Sri Gnana]

If you closed connection in your program it will happen. block that and try.

[saikrishna cinux]

i haven't closed my connection any where except in finnaly block

[Charles Lyons]

Now might be a good time to share an outline of the source code you think might be generating the problem (please replace any non-essential parts with a comment to make it more readable)...

[dema rogatkin]

More likely that you use class variable for connection or some other db related data. Make sure that you have only method variables.

[saikrishna cinux]

ya i am using class variable for creating the connection and i am creating the connection in service method

and never seen such type of exceptions
please some body help me

[dema rogatkin]

Declare any variables used for connection in service method. Servlet container uses only one copy of your servlet class to service concurrent requests.

[saikrishna cinux]

sir , can i create multiple connections and preparedstatements and resultsets for my servlet
is there any fault in doing this???
or shall i create only one connection and one stmt and one rs for all the queries i am using my servlet???

[Martin Simons]

Yes you can create multiple connections, but as has been stated, the
variable used to store/create/use the connection should be declared inside
of a method (and passed toi any other that might need it) not as a class
variable, or you get the problems you are getting now. Declaring the
variable for the connection inb the class means you have only one, as the
JSP is compiled and started only once. All connections are handled by the
same instance. So, when you declare it a class variable, then create the
connection in the method you get the following:

user one: creates connection thereby overwriting the class variable
user two: creates connection thereby overwriting the class variable
and destroying everything uer one was trying to do
user three: as user two above but for both user one and two, etc, etc, etc

[saikrishna cinux]

hey the problem is solved
just by keeping synchronized modified to the doGet method()
now it handles multiple request at a time
is there any wrong in keeping synchronized for the method doGet()

can i follow this method??? :roll: :roll:

[Ben Souther]

You have a much slower app now.
All requests are going to have to queue up and wait for a single database connection.

In other words, you've just coded out the multithreaded ability of a J2EE app.

You may want to look into connection pooling.
Most containers ship with this capability.

[Martin Simons]

Originally posted by saikrishna cinux:
hey the problem is solved
just by keeping synchronized modified to the doGet method()
now it handles multiple request at a time
is there any wrong in keeping synchronized for the method doGet()

can i follow this method??? :roll: :roll:

No it does not handle multiple requests, now it only handles one request
at a time not multiple. It can handle multiple users, but only because it
handles them one after the other, and not simultaneously. It is usually a
very bad idea to use class/instance variables in a servlet/jsp unless it is
something that is not going to change during the life of the servlet/jsp
(or at least will only be changed very sparingly and then probably only
through synchronized methods), but synchronizing your doGet/Post methods is
a very poor (design- and performance- wise) solution (at least when done
without a very good reason).
[ May 19, 2006: Message edited by: Martin Simons ]

[saikrishna cinux]

Originally posted by Martin Simons:


but synchronizing your doGet/Post methods is
a very poor (design- and performance- wise) solution (at least when done
without a very good reason).

[ May 19, 2006: Message edited by: Martin Simons ]

Yes Mr Martin, i am synchronizing the doget method because of strong reason i will explain u every thing now.

here i am developing online examination for GRE
so, 10 students will be taking the exams cuncurently
so for this only one servlet will take entire data(questions) from the database so i think this causes verymuch burden to the server so when 10 members access the same servlet at a time then it sudenly FAILS
i dont knwo why tomcat is incapable to handle multiple user requests while fetching huge data from the database
It just throws the exception on 10 members who r taking the exam at same point of time
the exceptions are like this
for member 1-----java.sql.SQLException: Invalid column index
for member 2-----java.sql.SQLException: Closed Connection
for member 3-----java.sql.SQLException: Failed to represent internal data
so comming to my coding in tht servlet i am using lotz of Vectors for storing the data from DB and lots of inner loops and inner loops contains lots of queries

if a single user runs this application it runs perfectly...
now tell me, while developing this webapplication every one follows the same rule tht is "if the application runs well in one system then it must run on multiple systems superbly!!!" but here it fails
it even gives these type of exceptions "Failed to represent internal data"

so now what is the neccesity of Tomcat ???

what should i do now ???
that is the STRONG reason why i am using SYNCHRONIZED doGet method
so now justify....

:roll:
thanking u sir

regards
cinux

[David O'Meara]

But there is an important point we've been trying to explain to you and you haven't picked it up yet. It has nothing to do with the load on Tomcat, Tomcat is fine. Your code is not thread safe. This means multiple threads accessing the code will cause it to fail in strange ways - like you've been seeing. A rough but inefficient way of fixing it is to restrict the flow to a single thread at a time by synchronizing the method - which is what you found.

Everything you're saying leads me to believe your code is not thread safe. There is little we can do to really help unless you post a sample. It needs fixing because your code is not working properly.

Dave

[Bear Bibeault]

Originally posted by saikrishna cinux:

so i think this causes verymuch burden to the server

Ten simultaneous connections is nothing! Servers are designed to handle a much greater load than that.

i dont knwo why tomcat is incapable to handle multiple user requests while fetching huge data from the database

Tomcat is perfectly capable of handling such situations. It's your code that is causing the problems.

As pointed out, you have written a non-thread-safe applciation. So as soon as more than one thread is kicked off, your application fails. Blaming Tomcat is not the answer. Fixing your code is.

And synchronizing the connection so that only one can execute at a time is a not the answer.

First, re-read the replies, there's a lot of good suggestions there. The most important of which is to adopt the use of a connection pool. Tomcat provides and excellent connection pooling mechanism.

[Ben Souther]

Originally posted by saikrishna cinux:


i dont knwo why tomcat is incapable to handle multiple user requests while
...
so now what is the neccesity of Tomcat ???
...
what should i do now ???

Tomcat is very capable of handling multiple user requests and does so for thousands of commercial apps.

The problem isn't with the container you're using.
You would have the exact same issue with any other container.
The problem is in your code.
You're declaring your database connection as an instance variable and then initializing it in your service methods. This is not a thread safe approach.

Several people have suggested alternatives.
You might want to look into them before bashing Tomcat (or whatever other conatiner you're using).



[scary klunk]
[ May 19, 2006: Message edited by: Ben Souther ]

[Bear Bibeault]

David <-> bear <-> Ben BONK!
[ May 19, 2006: Message edited by: Bear Bibeault ]

[dema rogatkin]

Originally posted by saikrishna cinux:
hey the problem is solved
just by keeping synchronized modified to the doGet method()
now it handles multiple request at a time
is there any wrong in keeping synchronized for the method doGet()

can i follow this method??? :roll: :roll:

Cool. I should suggest it first. Certainly it's a smart solution and you can use it for your app without fear.

[Bear Bibeault]

Originally posted by dema rogatkin:
Cool. I should suggest it first. Certainly it's a smart solution and you can use it for your app without fear.

Only if you are expecting one visitor at a time. Otherwise you've tied a tourniquet around the throat of your application.

Bad idea. Very bad idea. Not at all a smart solution in my opinion.

[Ben Souther]

Originally posted by dema rogatkin:
Cool. I should suggest it first. Certainly it's a smart solution and you can use it for your app without fear.

If there are to be only 10 or so users on the system at once, and..
If the database calls are relatively quick, synchronizing the doGet method may be tolerable to the original poster.

In case the last half dozen responses weren't clear enough, let me re-iterate.
Sharing a single database connection by declaring it as an instance variable and then instanciating it in the service methods is a poor idea (a bug actually).
The result is a non-thread-safe architecture.

Synchronizing the service method, is a simple hack that will make the app work but at the expense of scalability. Any long database calls will force all other users on the system to have to wait. This will never be a scalable solution and may (if the database calls take any length of time to execute) result in a sluggish app with as little as two concurrent users.

Since the main goal of Javaranch is to be a "Friendly place for Java Greenhorns" (a place to learn about Java coding), I don't think it's a good idea to encourage people to patch poor code with less than optimal solutions such as this.
[ May 19, 2006: Message edited by: Ben Souther ]

[dema rogatkin]

For some reason people design software considering thousand concurrent requests, and managing million transaction a day. This is not a requirement for many systems, so what's the reason all time go to the same way? If average time of servicing a request around 50ms and a peek of usage doesn't exceed 10 requests per second, then I can tell that dispatching threads servicing all these request concurrently can take more time than service them sequentially. Why people can't design software accordingly requirements? I do not know.

Edit: not always processing concurrently works faster than sequentially. But it's another topic.
[ May 19, 2006: Message edited by: dema rogatkin ]

[Ben Souther]

Dema,

Experienced developers make judgment calls all the time.
There are always compromises and trade-offs to be considered.
Expedience often trumps best practice design, scalability, CPU/Memory
efficiency, etc... And it should. It would make no sense for a company
to pay for large n tiered application if they know the
requirements could easily be met with a quick and dirty script written
in a high level language.
That's common sense and I don't think anyone here is disputing that.

In this case someone has written to a forum, trying to understand why
his application isn't working with multiple users. There are obvious
signs that a misunderstanding about the way threading in a servlet app
works has led him to write code that is not thread safe. He has also
demonstrated that he is having trouble understanding the suggestions
made to him by other people on the forum and, in his frustration,
has gone as far as to blame his servlet container for the concurrency
issues.

While working on the problem he has discovered that synchronizing the
service method seems to have fixed his app. (Load testing, even with 10
users may reveal otherwise). He has even specifically asked if there is
any harm in synchronizing the doGet method. A really good question, in
my opinion; one that deserves an honest and thorough answer.

There is a huge difference between an experienced developer making a
'time to market' vs scalability decision and a beginner working to
understand (and asking) how the technology works.

Do you really think you're helping the original poster by congratulating
him and telling him that he's written a 'smart solution that can be used
without fear'? I don't. I would much rather see him walk away
from the experience with an understanding of how threading works in a
servlet app.

This, as I understand it, is the point of JavaRanch.
[ May 20, 2006: Message edited by: Ben Souther ]

[dema rogatkin]

Ben,
Just after reading of few responses of OP you should get clear idea that he/she has no any experience with multi threading programming. I undertand your desire to help but you should understand that you can't replace a good learning course by few suggestions even from super smart guy. Software engineering requires time for learning and a lot of practice. Some fast knowledge can play bad in future issuing low quality software everyone should deal with. So, giving a simple solution is better than trying to jump over head.

[David O'Meara]

Originally posted by Bear Bibeault:
David <-> bear <-> Ben BONK!

Hopefully the same view from three staff members will carry some weight.

[David O'Meara]

dema,
while I agree that is in the case in many cases, I don't believe teaching simple solutions is a good idea when the practice is unsafe or something the user will later have to un-learn.

If it was a case of teaching a simpler implementation where the full solution could be integrated later I would support it, but in this case it applies to the whole architecture of the solution and provides the illusion of thread safety without any real guarantee.

I see it as more 'compensating errors' which provide the correct output in the current circumstance and not a real fix to the problem.

Just my thoughts though

Dave

[Bear Bibeault]

Originally posted by David O'Meara:

Hopefully the same view from three staff members will carry some weight.

I come in at 235 lbs.

So, giving a simple solution is better than trying to jump over head.

There is a big difference between a simple solution and a poor practice.

[saikrishna cinux]

hi to all
ok Mr ben i agree that u r all experienced guy's and u all know how to develop thread safe applications
now the point is i just help me out in this reg i have explained u all abt my application what i am using there and what i am trying to do.

but none of the member is giving me a good suggestion.

ok for now by7 placing synchronized method the application is running well no exception at run time but the problem is no 2 ppl are getting correct answers
one member is getting 26 out of 28 questions while the other is getting 30 out of 28 questions
whatz happenning here ?
i think the sessions are exchaning the questions haaa


some body help me

thanking u all for valubale suggestions

[dema rogatkin]

Dave,
I do not know who you are by occupation, but believe me I worked as a universtity professor for long time. So teaching is something different than you meet in your profession as a software engineer. You need to concentrate attention a student on something relevant to current topic and keep in shade other details which will be learned after. So, here is a perfect example, if he/she learns servlet, then thread safety can be other topic. On this stage of education adding synchronized is just perfect solution. Later in other courses students can learn how to get thread safety without possible compromisimg performance. Again, without sufficient information about requirements we can't recommend any thread safety solution. BTW If you are work near Oracle we can get lunch together and do more discussion.

[dema rogatkin]

Originally posted by Bear Bibeault:


There is a big difference between a simple solution and a poor practice.

Bear,
Excellent answer. A simple solution doesn't mean a poor practice at all. A poor practice is without knowledge any requirements are guessing what they could be and try to propose some common practice as a remedy for all cases.

[dema rogatkin]

A little more food for thinking. Just imagine that system is backended by a database which can't proceed concurrent requests.

[Bear Bibeault]

Originally posted by dema rogatkin:

A simple solution doesn't mean a poor practice at all.

No one said a simple solution was poor practice. Masking a problem instead of fixing it is what I consider a poor practice.

[David O'Meara]

Can I say I appreciate that you are prepared to discuss the issues without getting personal. This is rare on the internet.

I will admit that some of my statements are opinion, but I still believe that it is the correct way to proceed. While threading is critical to the understanding of servets, if you weren't going to teach threads explicitly I would still introduce a 'pattern' for producing stable code.

eg

// BAD!!
public class MyServlet extends HttpServlet
{
    // NEVER put variables here!
    private Connection conn;
 
    public void doGet( ... )
    {
        conn = DriverManager.getConnection( ... );
    }
}

// GOOD!!
public class MyServlet extends HttpServlet
{
    public void doGet( ... )
    {
        // ALWAYS put variables here!
        private Connection conn;
        conn = DriverManager.getConnection( ... );
    }
}

This is just a quick sample of how I would approach the issue.

[saikrishna cinux]

Originally posted by David O'Meara:

// GOOD!!
public class MyServlet extends HttpServlet
{
    public void doGet( ... )
    {
        // ALWAYS put variables here!
        private Connection conn;
        conn = DriverManager.getConnection( ... );
    }
}

hey i 've used the same patter for creating the connection the problem still exists

:roll:
i dont know why it creates a problem???

[David O'Meara]

We've been hoping you can post some sample code, because otherwise we're just guessing at what your problem could be.

[saikrishna cinux]

ok boss atlast here i am copying my code
this servlet is for fetching the questions,choices,answer from the database
and i apologize to all for the inconvinence (that the code is very long)

thanking u all
now u must definitely help me
coz i am now displaying my code here so pick out the wrong code(BUGS)
once again i am telling u all that this application runs perfectly with the single user but it fails to run with 2 or more users

package com.vlms.vts.grandtest.gre.blueprint;
import com.vlms.misc.MainServlet;
import com.vlms.vts.grandtest.gre.masters.bean.GreIndQnBnkChoiceBean;
import com.vlms.vts.grandtest.gre.masters.bean.GreIndQnBnkMasterBean;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.*;
import java.util.Hashtable;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import com.vlms.login.grandtest.gre.testuser.bean.TestUserBean;
import com.connectionpool.ConnectionPool;
// Referenced classes of package com.vlms.vts.grandtest.gre.blueprint:
//            GreIndQnBnkBluePrintBean
 
public class GreIndQnBnkBluePrintServlet extends MainServlet
{
 
    public GreIndQnBnkBluePrintServlet()
    {
        isDependent = "";
        totalGroupQns = 0;
    }
 
    public void init(ServletConfig servletconfig)
        throws ServletException
    {
        super.init(servletconfig);
        context = getServletContext();
 
 
    }
//here i h've synchronized for accepting the multiple users at same point of time
    public synchronized void doGet(HttpServletRequest httpservletrequest, HttpServletResponse httpservletresponse)
        throws ServletException, IOException
    {
 
      session = httpservletrequest.getSession(true);
Connectioncon =getConnection();
Connectioncon1=getConnection();
Connectioncon2=getConnection();
Connectioncon3=getConnection();
Connectioncon4=getConnection();
Connectioncon5=getConnection();
Connectioncon6=getConnection();
Connectioncon7=getConnection();
Connectioncon8=getConnection();
Connectioncon9=getConnection();
 
        boolean flag = false;
        boolean flag1 = false;
        boolean flag2 = false;
        boolean flag3 = false;
        try
        {
System.out.println ( "Connection"+con);
        query = "select distinct BLUEPRID from GRETESTBLUEPR WHERE SECTIONID=1";
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            ArrayList1 = new ArrayList();
            while(rs.next())
            {
ArrayList1.add(new Integer(rs.getInt(1)));
}
 
 
            int i = (int)(Math.random() * (double)ArrayList1.size());
            ////System.out.println("blue Print ID index: " + i);
            int j2 = ((Integer)ArrayList1.get(i)).intValue();
 
            System.out.println("blue Print SIZE " + ArrayList1.size() +"BluePrint ID:"+j2);
 
 
           int section = 1;//sectionID
 
           ArrayList bluePrintArrayList= new ArrayList();
 
query = "select ISDEPENDENT, TOTALQNSINGRP, CATEGORYID, CHAPTERID from GreTestBluePr where  BLUEPRID =  " + j2 + "  and  SECTIONID = " + section + " order by APPEARENCEORD";
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
 
            while( rs.next())
            {
                bpBean = new GreIndQnBnkBluePrintBean();
                isDependent = rs.getString(1);
                totalGroupQns = rs.getInt(2);
                int l = rs.getInt(3);
                int k1 = rs.getInt(4);
                bpBean.setIsDependent(checkForNull(isDependent));
                bpBean.setTotalGroupQns(totalGroupQns);
                bpBean.setCategoryId(l);
                bpBean.setChapterId(k1);
                bluePrintArrayList.add(bpBean);
            }
 
ArrayList levelArrayList =new ArrayList();
            query = "select LEVELID  from LEVELMASTER";
            pstmt1 = con1.prepareStatement(query);
            rs1 = pstmt1.executeQuery();
            while(rs1.next())
            {
levelArrayList.add(new Integer(rs1.getInt(1)));
}
            ////System.out.println("levelIdArrayList Size: " + ArrayList4.size());
             hashtable = new Hashtable();
            //level
 
for(int level = 1; level <= levelArrayList.size(); level++)
            {
StringBuffer stringbuffer=new StringBuffer();
questionArrayList=new ArrayList();
 
                for(int k5 = 0; k5 < bluePrintArrayList.size();  k5++)
                {
 
bpBean = (GreIndQnBnkBluePrintBean)bluePrintArrayList.get(k5);
                    isDependent = bpBean.getIsDependent();
                    totalGroupQns = bpBean.getTotalGroupQns();
                    int category = bpBean.getCategoryId();
                    int chapter = bpBean.getChapterId();
 
                    if((bpBean.getIsDependent()).equals("Yes"))
                    {
     if(stringbuffer.length() == 0)
 
  query = "select QNO from QUESTIONBNKMASTER where SECTIONID = " + section  + " and CATEGORYID = " + category + " and CHAPTERID = " + chapter+ " and DEPENDENTQUESTION LIKE '" + isDependent + "' and LEVELID = 1" ;
 
 else
 
  query = "select QNO from QUESTIONBNKMASTER where SECTIONID = " + section  + " and CATEGORYID = " + category + " and CHAPTERID = " + chapter+ " and DEPENDENTQUESTION LIKE '" + isDependent + "' and QNO not in (" + stringbuffer.toString() + ") and LEVELID = 1" ;
 
     }
else
{
    if(stringbuffer.length() == 0)
 
      query = "select QNO from QUESTIONBNKMASTER where SECTIONID = " + section  + " and CATEGORYID = " + category + " and CHAPTERID = " + chapter+ " and DEPENDENTQUESTION LIKE '" + isDependent + "' and LEVELID ="+level  ;
 
     else
 
      query = "select QNO from QUESTIONBNKMASTER where SECTIONID = " + section + " and CATEGORYID = " + category + " and CHAPTERID = " + chapter + " and DEPENDENTQUESTION LIKE   '" + isDependent + "' and QNO not in (" + stringbuffer.toString() + ") and LEVELID = " + level;
 
     }
 
pstmt2 = con2.prepareStatement(query);
                    rs2 = pstmt2.executeQuery();
                    ArrayList numberArrayList= new ArrayList();
                    while( rs2.next() )
                    {
numberArrayList.add(new Integer(rs2.getInt(1)));
}
 
 
                int j = (int)(Math.random() * (double)numberArrayList.size());
                int qno = ((Integer)numberArrayList.get(j)).intValue();
                    if(stringbuffer.length() > 0)
                        stringbuffer.append("," + String.valueOf(qno));
                    else
                        stringbuffer.append(String.valueOf(qno));
 
 
                    questionArrayList.add(new Integer(qno));
 
 
}//inner for
System.out.println(session.getId()+"qnNumArrayList Size: " + questionArrayList.size());
 
                hashtable .put(new Integer(level), questionArrayList);
 
            }//outer for loop
 
 
            qnht = new Hashtable();
            for(int k4 = 0; k4 < hashtable.size(); k4++)
            {
                qnArrayList = new ArrayList();
 
                ArrayList qArrayList= (ArrayList)hashtable.get(levelArrayList.get(k4));
                System.out.println(session.getId()+"^numArrayList: " + qArrayList);
 
                for(int i5 = 0; i5 < qArrayList.size(); i5++)
                {
                    int qno = ((Integer)qArrayList.get(i5)).intValue();
 
                    query = "select ISPICTEXT, ISCHTXTPIC, QTEXTPATH,  DEPENDENTQUESTION, CATEGORYID from QUESTIONBNKMASTER where QNO = " + qno;
 
                    pstmt3 = con3.prepareStatement(query);
                    //here questionBean qnArrayList
                    rs3 = pstmt3.executeQuery();
                    while(rs3.next())
                    {
                        qnBean = new GreIndQnBnkMasterBean();
                        qnBean.setQnId(qno);
 
                        qnBean.setQnFmt(checkForNull(rs3.getString(1)));
                        qnBean.setChoiceFmt(checkForNull(rs3.getString(2)));
                        qnBean.setQnContent(checkForNull(getClobString(rs3.getClob(3))));
                        isDependent = checkForNull(rs3.getString(4));
                        qnBean.setIsDependent(isDependent);
 
                        query = "select CATEGORYDESC from categorymaster where CATEGORYID = " + rs3.getInt(5);
                        pstmt8 = con8.prepareStatement(query);rs8 = pstmt8.executeQuery();
                        while( rs8.next()){ qnBean.setCategoryName(checkForNull(rs8.getString(1)));}
 
                        if(isDependent.equalsIgnoreCase("No"))
                        {
                            ArrayList choiceArrayList= new ArrayList();
                            query = "select CHOICENO, CHOICETEXTPATH from CHOICEMASTER where QNO = " + qno;
                            pstmt9 = con9.prepareStatement(query);
                            rs9 = pstmt9.executeQuery();
                            while(rs9.next())
                            {
                                choicesBean = new GreIndQnBnkChoiceBean();
                                choicesBean.setChoiceId(rs9.getInt(1));
                                choicesBean.setChoiceContent(checkForNull(getClobString(rs9.getClob(2))));
                                choiceArrayList.add(choicesBean);
                            }
 
                            qnBean.setChoiceArrayList(choiceArrayList);
                            query = "select  CHOICENO, ISPICTEXT,  ANSEXPLANATION from  ANSWERMASTER where QNO = " + qno;
 
 
                            pstmt4 = con4.prepareStatement(query);
                            rs4 = pstmt4.executeQuery();
                            while(rs4.next())
                            {
                                qnBean.setAnswerId(rs4.getInt(1));
                                qnBean.setExpFmt(checkForNull(rs4.getString(2)));
                                qnBean.setExpContent(checkForNull(getClobString(rs4.getClob(3))));
 
                            }
 
 
                        }//If NO
 
if(isDependent.equalsIgnoreCase("Yes"))
{
 
 
ArrayList attchQuestionArrayList =new ArrayList();
 
GreIndQnBnkMasterBean greindqnbnkmasterbean;
 
//warning:hard coded value must be removed
StringBuffer stringbuffer1 =new StringBuffer();
for(int lev=1; lev<=levelArrayList.size();lev++)
{
if(stringbuffer1.length()==0)
query = "select ATTACHMENTQNO, ISPICTEXT, ISATCHTXTPIC, TEXTPATH, LEVELID from  ATTACHMENTQUESTIONS where QNO = "+ qno +" and LEVELID ="+lev;
else
query = "select ATTACHMENTQNO, ISPICTEXT, ISATCHTXTPIC, TEXTPATH, LEVELID from  ATTACHMENTQUESTIONS where QNO not in (" + stringbuffer1.toString() + ") and LEVELID ="+lev;
 
//System.out.println("ATTACHMENTQUESTIONS query: "+lev + query);
 
pstmt5 = con5.prepareStatement(query);
rs5 = pstmt5.executeQuery();
while( rs5.next())
{
greindqnbnkmasterbean = new GreIndQnBnkMasterBean();
                                int attQno = rs5.getInt(1);
                                greindqnbnkmasterbean.setQnId(attQno);
                                greindqnbnkmasterbean.setQnFmt(checkForNull(rs5.getString(2)));
                                greindqnbnkmasterbean.setChoiceFmt(checkForNull(rs5.getString(3)));
                                greindqnbnkmasterbean.setQnContent(checkForNull(getClobString(rs5.getClob(4))));
                                greindqnbnkmasterbean.setDifficultyLevelId(rs5.getInt(5));
 
                                ArrayList attchChoiceArrayList = new ArrayList();
                                query = "select CHOICENO, TEXTPIC from ATTACHMENTCHOICEMASTER where ATTACHMENTQNO = " + attQno;
GreIndQnBnkChoiceBean greindqnbnkchoicebean;
                                pstmt6 = con6.prepareStatement(query);
                                rs6 = pstmt6.executeQuery();
                                while(rs6.next())
                                {
                                    greindqnbnkchoicebean = new GreIndQnBnkChoiceBean();
                                    greindqnbnkchoicebean.setChoiceId(rs6.getInt(1));
                                    greindqnbnkchoicebean.setChoiceContent(checkForNull(getClobString(rs6.getClob(2))));
                                    attchChoiceArrayList.add(greindqnbnkchoicebean);
                                }
                                greindqnbnkmasterbean.setChoiceArrayList(attchChoiceArrayList);
                                query = "select CHOICENO, ISPICTEXT, ANSEXPLANATION from ATTACHMENTANSWERMASTER where ATTACHMENTQNO = " + attQno;
                                //System.out.println("ATTACHMENTANSWERMASTER Query"+query);
 
                                pstmt7 = con7.prepareStatement(query);
                                rs7 = pstmt7.executeQuery();
                                while(rs7.next())
                                {
                                    greindqnbnkmasterbean.setAnswerId(rs7.getInt(1));
                                    greindqnbnkmasterbean.setExpFmt(checkForNull(rs7.getString(2)));
                                    greindqnbnkmasterbean.setExpContent(checkForNull(getClobString(rs7.getClob(3))));
 
                                }
 
    attchQuestionArrayList.add(greindqnbnkmasterbean);
 
                            }//while close
                            qnBean.setQuestionArrayList(attchQuestionArrayList);
 
}//for close
 
 
}//if Yes
 
 
                 }//main while
 
qnArrayList.add(qnBean);
//System.out.print(session.getId()+" qnArrayList.size(), "+qnArrayList.size());
              }//for qnArrayList
 
 
                    qnht.put(new Integer(k4), qnArrayList);
                    //System.out.println(session.getId()+" qnht.size() "+qnht.size());
 
         }//for hashtable
 
 
            session.removeAttribute("qnht");
            session.setAttribute("qnht", qnht);
 
            int l4 = qnht.size();
            int j5 = 1;
            int i6 = 0;
            session.removeAttribute("qnIndex");
            session.setAttribute("qnIndex", new Integer(i6));
 
            session.removeAttribute("minLevelNum");
            session.setAttribute("minLevelNum", new Integer(j5));
 
            session.removeAttribute("maxLevelNum");
            session.setAttribute("maxLevelNum", new Integer(l4));
 
            int j6 = (int)Math.ceil((j5 + l4) / 2);
            ////System.out.println("levelNum: " + j6);
 
            session.removeAttribute("levelNum");
            session.setAttribute("levelNum", new Integer(j6));
            //
            qnht = (Hashtable)session.getAttribute("qnht");
            qnArrayList = (ArrayList)qnht.get(new Integer(j6 - 1));
            ////System.out.println("|||"+qnArrayList.size());
 
            session.removeAttribute("qnArrayList");
            session.setAttribute("qnArrayList", qnArrayList);
 
            session.removeAttribute("activeArrayList");
            session.setAttribute("activeArrayList", bluePrintArrayList);
            //X
int j3=28;
session.removeAttribute("maxNumQns");
session.setAttribute("maxNumQns", new Integer(j3));
 
 
            urlStr = "/VLMS/VTS/GrandTest/GRE/Quantitative/Instructions/quantFirstScreen.jsp";
            session.removeAttribute("whereIWas");
            session.setAttribute("whereIWas", "/VisuTestingServices" + urlStr);
            rd = context.getRequestDispatcher(urlStr);
            rd.forward(httpservletrequest, httpservletresponse);
        }
        catch(SQLException sqlexception)
        {
            sqlexception.printStackTrace();
            ////System.out.println(sqlexception);
            ////System.out.println(sqlexception.toString());
            int k2 = sqlexception.getErrorCode();
            ////System.out.println("ErrorCode: " + k2);
            context.log("GreQnBnkBluePrintServlet:  SQLException.", sqlexception);
            sendErrorRedirect(httpservletrequest, httpservletresponse, "/inc/error.jsp", "GreQnBnkBluePrintServlet: SQLException.", sqlexception);
            return;
        }
        catch(NullPointerException nullpointerexception)
        {
            nullpointerexception.printStackTrace();
            ////System.out.println(nullpointerexception);
            ////System.out.println(nullpointerexception.toString());
            context.log("GreQnBnkBluePrintServlet:  NullPointerException.", nullpointerexception);
            sendErrorRedirect(httpservletrequest, httpservletresponse, "/inc/error.jsp", "GreQnBnkBluePrintServlet: NullPointerException.", nullpointerexception);
            return;
        }
        catch(Exception exception)
        {
            exception.printStackTrace();
            ////System.out.println(exception);
            ////System.out.println(exception.toString());
            context.log("GreQnBnkBluePrintServlet : An error has occurred.", exception);
            sendErrorRedirect(httpservletrequest, httpservletresponse, "/inc/error.jsp", "GreQnBnkBluePrintServlet: An error has occurred.", exception);
            return;
        }
    finally{
try{
con.close();
con1.close();
con2.close();
con3.close();
con4.close();
con5.close();
con6.close();
con7.close();
con8.close();
 
stmt.close();
stmt1.close();
 
pstmt1.close();
pstmt2.close();
pstmt3.close();
pstmt4.close();
pstmt5.close();
pstmt6.close();
pstmt7.close();
pstmt8.close();
 
 
 
rs.close();
rs1.close();
rs2.close();
rs3.close();
rs4.close();
rs5.close();
rs6.close();
rs7.close();
rs8.close();
qnArrayList=null;
activeArrayList=null;
qnht=null;
 
ArrayList1=null;
 
 
}catch(Exception e){}
 
}
 
    }//doget
 
    public void doPost(HttpServletRequest httpservletrequest, HttpServletResponse httpservletresponse)
        throws ServletException, IOException
    {
        session = httpservletrequest.getSession(true);
    }
 
    private ServletContext context;
    private RequestDispatcher rd;
    private HttpSession session;
    private String query;
    private String urlStr;
 
 
    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet rs;
    private Statement stmt1;
 
    private Statement stmt2;
    private PreparedStatement pstmt1;
    private PreparedStatement pstmt2;
    private PreparedStatement pstmt3;
    private PreparedStatement pstmt4;
    private PreparedStatement pstmt5;
    private PreparedStatement pstmt6;
    private PreparedStatement pstmt7;
    private PreparedStatement pstmt8;
    private PreparedStatement pstmt9;
 
    private ResultSet rs1;
    private ResultSet rs2;
    private ResultSet rs3;
    private ResultSet rs4;
    private ResultSet rs5;
    private ResultSet rs6;
    private ResultSet rs7;
    private ResultSet rs8;
    private ResultSet rs9;
 
 
    private GreIndQnBnkBluePrintBean bpBean;
    private GreIndQnBnkMasterBean qnBean;
    private GreIndQnBnkChoiceBean choicesBean;
    private ArrayList qnArrayList;
    private Hashtable qnht;
    private String categoryName;
    private ArrayList activeArrayList;
    String isDependent;
    int totalGroupQns,atansw,answ;
    private ArrayListArrayList1;
 
private ArrayListquestionArrayList;
Hashtable hashtable ;
 
}

[BPSouther: Added UBB Code tags]
[ May 20, 2006: Message edited by: Ben Souther ]

[Ben Souther]

While you're at it, can you post the code the superclass "MainServlet.java" so we can see how you've implemented the getConnection method?
Use the UBB code tags as I've done so your indenting is preserved.

[Charles Lyons]

ok boss atlast here i am copying my code
this servlet is for fetching the questions,choices,answer from the database
and i apologize to all for the inconvinence (that the code is very long)

Well long it is... it's no wonder debugging is a nightmare.

I haven't read it all, but I've glossed over it. My main observation is that you use multiple Connection objects, when (I think) all you ever need do here is get a PreparedStatement from each one. You could do this easily from only one Connection without problem. The only time you would want to use multiple Connections is when you're connecting to a different database.

I'm also not sure why you've got as many instance variables as you have. Many (if not most/all) of these would be better as method-locals. Why do you need a RequestDispatcher to be an instance variable? It's far better as a method-local (and indeed every code I've ever seen does it this way).

The reason you are still experiencing problems is that all your ResultSets and Statements (which depend on the Connection) are also instance variables. Making the Connections method-local isn't sufficient.

Might I now suggest an improvement like this:

public void doGet(...) {
    Connection con = getConnection();
    ResultSet rs1 = query(con, "select distinct BLUEPRID from GRETESTBLUEPR WHERE SECTIONID=1");
    ArrayList<Integer> list1 = new ArrayList<Integer>();
    while(rs != null && rs.next()) {
        list1.add(new Integer(rs.getInt(1)));
    }
 
    // Later on:
    PreparedStatement ps1 = getPStatement(con, "select LEVELID from LEVELMASTER");
    // etc...
}
 
/** Does the leg-work of executing a query */
private ResultSet query(Connection c, String query) {
    try {
        Statement s = c.createStatement();
        return s.executeQuery(query);
    } catch(SQLException sqle) {
        return null;
    }
}
 
/** Gets a new PreparedStatement */
private PreparedStatement getPStatement(Connection c, String query) {
    return c.prepareStatement(query);
}

This tidies the code up somewhat - make the query variable method-local and only use it for conditional constructions. The fact I've wrapped several lines in the query() method means I've saved lots of lines in the implementation (one method call several times is much tidier than several lots of identical code just copied/pasted). This method now catches the exception and returns null if something fails. Of course if you use the ArrayList construction several times, that can be wrapped in a method as well.

I also don't understand why you've executed a Statement in some places and used PreparedStatements in others. PSs are supposed to be re-used over and over again, replacing ? parameters with new values on each execution. Since you do everything once, you're only incurring a performance hit using PSs. Do you understand the difference between the two?

Note that only one Connection is ever required for one client; we create it in the doGet() method and use it in other convenience methods. This is now thread-safe.

If all you're doing is setting up the exam here, you might also be better doing all the configuration logic in a set of separate JavaBeans and not in a servlet (I have an exam simulator which uses a JavaBean model to manage the exams). I notice that you're using some beans anyway. Remember that servlets are invoked for every request, so all this loading needs to be done every time a request is made - highly inefficient if your exam format rarely changes. My JavaBean model is kept in memory (it doesn't use much) and only refreshes itself if some configuration data in the exam changes... this is analogous to the way in which containers treat JSPs - they only translate and compile them once, not on every request, but will retranslate/compile if a change occurs.

[Ben Souther]

Four things:
1.)

Originally posted by saikrishna cinux:
...
ok Mr ben i agree that u r all experienced guy's and u all know how to develop thread safe applications ...

You've been around long enough to know that non-words such as "u r" are
strongly discouraged here. They confound language translation software
and make it very difficult for non-English speaking members to read your posts.

If you have't already, please see:
http://faq.javaranch.com/view?UseRealWords

2.)

Originally posted by saikrishna cinux: ...
but none of the member is giving me a good suggestion.

Quite the opposite..
By making this thread controversial, another ranch hand has caused this
thread to receive a lot more attention than it probably would have
otherwise. You've got two bartenders, a sheriff, an author, and a handful of ranchhands all looking at your situation and offering good suggestions.

Instead of complaining, I would suggest that you take full advantage of the
attention being spent here and use this opportunity to learn about servlet
threading. You might not ever get this level of free help again.


3.)

Originally posted by saikrishna cinux:
ok for now by7 placing synchronized method the application is running well
no exception at run time but the problem is no 2 ppl are getting correct
answers one member is getting 26 out of 28 questions while the other is
getting 30 out of 28 questions

whatz happenning here ?

i think the sessions are exchaning the questions haaa

In addition to the servlet threading issues, you may indeed have other
flaws in your database schema and/or business logic. You may need to
work these out yourself.

My suggestion is that you first, take advantage of the suggestions
made so far and get your database connection handling straightened
out so that you have a proper servlet that is thread safe and that
won't cause memory leaks and/or JDBC lock ups. Once that's done,
it will be easier for you to debug any other issues in your
code.

4.)

Originally posted by saikrishna cinux:
thanking u all for valubale suggestions

You are welcome.

[ May 20, 2006: Message edited by: Ben Souther ]

[saikrishna cinux]

/**
**Copyright 2003 Visu Cybertech Ltd. All Rights Reserved.
*FileName:MainServlet.java
*CreatedBy:D.Suresh Babu
*CreatedOn:12/08/2003
*Discription:class used for providing the database connection and for providing the *methodsfor checking Null sessions etc.
*/
 
package com.vlms.misc;
 
import javax.servlet.*;
import javax.servlet.http.*;
 
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
 
public class MainServlet extends HttpServlet {
 
private ServletContext context;
private RequestDispatcher rd;
 
private StringBuffer sb;
 

/**
*init()Method is used for intialising the servlet
* configinstance of ServletConfig interface used for creating the ServletContext *interface
*@returnvoid
*/
 
public void init ( ServletConfig config ) throws ServletException {
super.init ( config );
context = getServletContext ();
}// End of init Method
 

 
/**
*getConnection ()Method is used for creating the Database Connection
*
*@returnConnection
*/
 
public Connection getConnection () {
Connection con = null;
try {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
con = DriverManager.getConnection("jdbc :-o	 racle :-o	 ci8:@visuit","vtsqbadmin","gretoefl");
//con = DriverManager.getConnection("jdbc :-o	 racle:thin:@visuoracle:1521:vtsqbadmin","vtsqbadmin","gretoefl");
//System.out.println ( "Connectio"+con);
con.setAutoCommit ( true );
 
}
catch ( SQLException sqle ){
context.log ("MainServlet: Could Not get the Database Connection.", null);
sqle.printStackTrace ();
System.out.println ( "MainServlet: Could Not get the Database Connection: " + sqle );
}
return con;
 

}
 
/*protected Connection getConnection () {
Connection con = null;
try {
con = DriverManager.getConnection("jdbc :-o	 dbc :mrgreen: river={SQL Server};Server=DBSERVER;Database=TOEFL","sa","");
System.out.println ( "Connection is created: " + con );
}
catch ( SQLException sqle ){
context.log ("MainServlet: Could Not get the Database Connection.", null);
sqle.printStackTrace ();
System.out.println ( "MainServlet: Could Not get the Database Connection: " + sqle );
}
return con;
}*/
 
/**
*getConnection ()Method is used for creating the Database Connection
*
*@returnConnection
*/
 
public void freeConnection ( HttpServletRequest request, HttpServletResponse response, Connection con, Statement stmt, ResultSet rs ) throws ServletException, IOException {
try {
if ( rs != null ) {
rs.close();
}
if ( stmt != null ) {
stmt.close();
}
if ( con != null ) {
con.close();
}
}
catch (SQLException sqle){
context.log ("MainServlet: Error While disconnecting from The Database.", sqle);
 
this.sendErrorRedirect (request, response, "/Error/error.jsp", "MainServlet: Error While disconnecting from The Database.", sqle);
return;
}
}
 
/**
* dispatch()Method is used for dispatching to the requested page
*
* urlStrholds the path of the requested page
*
* @requestinstance of HttpServletRequest interface
*
* @responseinstance of HttpServletResponse interface
*
* @returnvoid
*/
 
public void dispatch ( String urlStr, HttpServletRequest request, HttpServletResponse response ) throws ServletException, IOException {
context = this.getServletContext ();
rd = context.getRequestDispatcher ( urlStr );
rd.forward ( request, response );
}
 
/**
* Redirects to an error page with the error information.
* The error page displays the information and also the exception stack trace
*
* @param request the servlet request
* @param response the servlet response
* @param errorPageURLthe URL of the errorPage
* @param statusMsga String to be displayed in the error page
* @param ethe exception which caused this error redirect
*/
 
    public void sendErrorRedirect (HttpServletRequest request, HttpServletResponse response, String errorPageUrl,String statusMsg, Throwable e) throws ServletException, IOException {
 
        HttpSession session = request.getSession (true);
 
        session.putValue ("statusMsg", statusMsg);
        session.putValue ("exception", e);
 
this.dispatch ( errorPageUrl, request, response );
 
    }
 
/**
* checkForNullMethod is used for checking null strings
*
* checkStrholds the String Value
*
* @returnString
*/
 
public String checkForNull ( String checkStr ) {
        if ( checkStr != null && checkStr.length () > 0 ) {
            return checkStr.trim ();
        }
        else {
            return "";
        }
    }
 
public int checkForNullNumber ( String checkStr ) {
        if ( checkStr != null && checkStr.length () > 0 ) {
            return Integer.parseInt ( checkStr.trim () );
        }
        else {
            return 0;
        }
    }
 
public String checkForLineBreaks ( String str ) {
char ch;
    StringBuffer sb = new StringBuffer(str);
for ( int i = 0; i < sb.length(); i++ ){
ch = sb.charAt(i);
if(ch == '\r'){
sb.insert(i,"<br>");
}
}
return sb.toString();
}
 

 
// method for inserting escape character for SingleQuote
 public String checkForSingleQuote(String str){
char ch;
    sb = new StringBuffer(str);
    for (int i = 0; i<sb.length();i++ )
         {ch = sb.charAt(i);
     if(ch == '\''){
      sb.insert(i,'\'');
      i++;
     }
       }
      return sb.toString();
     }
 

 // method for inserting escape character for SingleQuote
 public String checkForAmpersand(String str){
char ch;
    sb = new StringBuffer(str);
    for (int i = 0; i<sb.length();i++ )
         {ch = sb.charAt(i);
     if(ch == '&'){
      sb.insert(i,'\\');
      i++;
     }
       }
      return sb.toString();
     }
 
 // method for converting Clob to String
public String getClobString ( Clob clob ){
StringBuffer contentsb = new StringBuffer ();
try{
if ( clob != null ){
Reader reader = clob.getCharacterStream();
int charValue = 0;
while ( (charValue = reader.read()) != -1 ){
contentsb.append ( (char)charValue );
}
}
}catch ( IOException ioe ){
System.out.println ( "IOException: " + ioe );
}
catch ( SQLException sqe ){
System.out.println ( "SQLException: " + sqe );
}
catch ( Exception e ){
System.out.println ( "Exception: " + e );
}
return checkForNull ( contentsb.toString () );
}
 
}

Here is my MainServlet
for simplification i need to use mainservlet


regards
cinux