troubleshooting Question

getting error java.sql.SQLException No available resource. Wait-time expired. when trying to save into db.

Avatar of mwari
mwari asked on
Java
7 Comments1 Solution595 ViewsLast Modified:
I am getting the following exception when I try to save info into a db.
Basically what happens is I save information from a csv file into a csvfile table, if the operation is succesfull then I retrieve the information from csvfile table and save it to daceaccount table, thereafter to get the total I read the daceaccount table and load the Reference number and the total, It takes about a long time to execute about 4mins and when it does, it does not save anything. When I look in the logs I see the exception:

 java.sql.SQLException No available resource. Wait-time expired

This is done inside a servlet here is the code snippet of the code that does this:

 protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        CsvDateUtil csvdate= new CsvDateUtil();
        out.println("<input type=hidden name=proc value=proc>");
        /* checking if file saved file exists, if so read the
         file using CSVFileReader process method, which will
         read the file and write it to the database.*/
       
       
        today= Calendar.getInstance();
        if(request.getParameter("files")!=null){
            if(request.getParameter("files").endsWith(".csv")){  
                try{
                    con= c.getJndiConnection();
                    pstmt = con.createStatement();
                   
                    csv.setCSVDirectory(request.getParameter("pathtocsv"));
                    csv.setSkippedRows(3);
                    List two= csv.getResults(2, request.getParameter("csvfilename").replaceAll(".csv", ""));
                    List four= csv.getResults(4, request.getParameter("csvfilename").replaceAll(".csv", ""));
                    List five= csv.getResults(5, request.getParameter("csvfilename").replaceAll(".csv", ""));
                    List six= csv.getResults(6, request.getParameter("csvfilename").replaceAll(".csv", ""));
                    PropertyReader reader =new PropertyReader();
                   
                    for (int i=0; i < two.size(); i++){
                        String insvalues="insert into csvfile values ('"+new CsvDateUtil().date(two, i, "")+"', '"+four.get(i)+"', '"+five.get(i)+"', '"+six.get(i).toString().trim()+"')";
                        chk= pstmt.executeUpdate(insvalues);
                        System.out.println("---status of insert csvfile table "+chk);
                    }
                   
                    if(chk > 0){
                        m = pstmt.executeUpdate("insert into daceaccount select Dates, Amount, Transaction, Reference from csvfile WHERE NOT EXISTS (SELECT daceaccount.Dates, daceaccount.Amount, daceaccount.Transaction, daceaccount.Reference from daceaccount where daceaccount.Reference = csvfile.Reference and daceaccount.Dates = csvfile.Dates and csvfile.Amount = daceaccount.Amount)");
                        if(m > 0 ){
                            try{
                                k= pstmt.executeUpdate("delete from total");
                                k= pstmt.executeUpdate(" insert into total select Reference, sum(Amount) from daceaccount group by Reference");
                                if(k > 0){
                                    System.out.println("---Successfully inserted "+k+" rows in total");
                                }
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                            System.out.println("--Successfully modified " + m + " rows.\n");
                           
                        }else{
                            System.out.println("---(Class Test), No updates required");
                        }
                    }else{
                        System.out.println("unable to save info to csvfile table");
                    }
                } catch (SQLException se) {
                    System.out.println("We got an exception while executing our query:" +
                    "that probably means our SQL is invalid");
                    se.printStackTrace();
                    System.exit(1);
                } finally {
                    try { pstmt.close(); } catch(SQLException e) { e.printStackTrace(); }
                    try { con.close(); } catch(SQLException e) { e.printStackTrace(); }
                }
               
            }else if(!request.getParameter("files").endsWith(".csv")){                
                out.println("<center><font color=red size=2 face=Tahoma>Wrong file type (must be csv file)</font></center>");                
            }
        }
        if(request.getAttribute("filename")!=null){
            out.println("<html>");
            out.println("<body>");
            out.println("<center><P>&nbsp;</P>");
            out.println("<P><FONT face=Tahoma size=2>The following file has been saved to directory<FONT color=#0000ff> /tmp</FONT> click continue to proceed.</FONT></P>");
            out.println("<FORM METHOD=POST ACTION=/accounting/servlet/utilities.InterceptorServlet>");
            out.println("<input type=hidden name=uploads value=uploads>");
            out.println("<P><FONT face=Tahoma size=2></FONT><INPUT TYPE=text NAME=files value='"+request.getAttribute("filepath")+"/"+request.getAttribute("filename")+"'><INPUT type=submit value=continue name=continue></P>");
            out.println("<input type=hidden name=pathtocsv value='"+request.getAttribute("filepath")+"'>");
            out.println("<input type=hidden name=csvfilename value='"+request.getAttribute("filename")+"'>");
            out.println("</FORM>");
            out.println("<P>&nbsp;</P></center>");
            out.println("</body>");
            out.println("</html>");
           
        }        
        out.close();
    }
   


Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros