Solved

com.mysql.jdbc.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.

Posted on 2008-10-12
8
3,087 Views
Last Modified: 2013-11-23
HI
i am doing in program(java), which check the database if the record is already there...if the record is already there then program will update the table and if the record is not there it will update the table.

for checking the table whether the record is there or not i am using the following code
 public int chkRecordExist(String chk_mpn, String chk_region)
          {
              
              Connection con1 = null;
              Statement stmt1 = null;
              ResultSet rs;

              int rec_count = 0;
              try
              {
                    con1 =  connections();;
                      stmt1 = con1.createStatement();
              }
              catch(Exception e)
              {
                  System.out.println((new StringBuilder("error in connction to sql in check record exist")).append(e).toString());
              }
              try
              {  
                    
                    chk_mpn=chk_mpn.replace("\"", "");
                    chk_region=chk_region.replace("\"", "");
                    
                  String getCountQuery = "select count(*) from inbound_to_future where MPN = '"+chk_mpn+"'and Region = '"+chk_region+"'";
                  System.out.println("record count query"+getCountQuery);
                  for(rs = stmt1.executeQuery(getCountQuery); rs.next();)
                        rec_count = rs.getInt(1);

              
                  stmt1.close();
                  con1.close();
              }
              catch(Exception e)
              {
                  System.out.println("in record count");
              }
              return rec_count;
          }
      

after running some set of data i am getting error of
com.mysql.jdbc.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.

This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable.

For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.

For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271).
I understood that it is because i am opening and closing the connection many times in short interval of time...

can anybody give me suggestion to do this....that is to check the table whether the record is already there or not...
iattach my program with this


import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.StringTokenizer;
 
public class UloadData {
 
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		UloadData uploaddataObj = new UloadData();
		uploaddataObj.uploadToTable();
	}
 
	 public int chkRecordExist(String chk_mpn, String chk_region)
	    {
	        
	        Connection con1 = null;
	        Statement stmt1 = null;
	        ResultSet rs;
 
	        int rec_count = 0;
	        try
	        {
	        	con1 =  connections();;
			    stmt1 = con1.createStatement();
	        }
	        catch(Exception e)
	        {
	            System.out.println((new StringBuilder("error in connction to sql in check record exist")).append(e).toString());
	        }
	        try
	        {   
	        	
	        	chk_mpn=chk_mpn.replace("\"", "");
	        	chk_region=chk_region.replace("\"", "");
	        	
	            String getCountQuery = "select count(*) from inbound_to_future where MPN = '"+chk_mpn+"'and Region = '"+chk_region+"'";
	            System.out.println("record count query"+getCountQuery);
	            for(rs = stmt1.executeQuery(getCountQuery); rs.next();)
	            	rec_count = rs.getInt(1);
 
	         
	            stmt1.close();
	            con1.close();
	        }
	        catch(Exception e)
	        {
	            System.out.println("in record count");
	        }
	        return rec_count;
	    }
	
	public void uploadToTable() {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs;
		String chk_mpn="";String chk_region="";
		try
		{
			con =  connections();;
		    stmt = con.createStatement();
		
		} catch (Exception eee) {
			System.out.println("error in connection " + eee.toString());
		}
		
	
        int i=0;
String thisLine = "";
		try {
			FileInputStream fin = new FileInputStream(
					"D:\\futureElectronics\\temp\\future.csv");
			BufferedReader myInput = new BufferedReader(new InputStreamReader(
					fin));
			
			while((thisLine = myInput.readLine()) != null) 
            {
             int counter = 0;
             i=i+1;  
             StringTokenizer tk = new StringTokenizer(thisLine, ",");
             String data[] = new String[tk.countTokens()];
             while(tk.hasMoreElements()) 
             {
                data[counter] = (String)tk.nextElement();
               
               
               if (counter==2){
            	   String chk_mpn1=data[counter];
            	//   chk_mpn=chk_mpn.replaceAll("\\s+$", "");
            	   chk_mpn=chk_mpn1.replaceAll("\\s+$", "");
               }
               if (counter==3){
            	   chk_region=data[counter];
            	   chk_region=chk_region.trim();
               }
               
               counter++;
                }
             String PartnerName=data[counter-6];
             String ManufacturerName=data[counter-5];
             String MPN=data[counter-4];
             String Region=data[counter-3];
             String ATS=data[counter-2];
             String SOQ=data[counter-1];
             
             PartnerName=PartnerName.replaceAll("\\s+$", "");
             ManufacturerName=ManufacturerName.replaceAll("\\s+$", "");
             MPN=MPN.replaceAll("\\s+$", "");
             Region=Region.replaceAll("\\s+$", "");
             ATS=ATS.replaceAll("\\s+$", "");
             SOQ=SOQ.replaceAll("\\s+$", "");
             
                
             PartnerName=PartnerName.replace("\"", "");
             ManufacturerName=ManufacturerName.replace("\"", "");
             MPN=MPN.replace("\"", "");
             Region=Region.replace("\"", "");
             ATS=ATS.replace("\"", "");
             SOQ=SOQ.replace("\"", "");
                                      
             String Q="INSERT INTO inbound_to_future(PartnerName,ManufacturerName,MPN,Region,ATS,SOQ) VALUES ('" + PartnerName + "','" + ManufacturerName + "','" + MPN+ "','" + Region+ "','"+ATS+"','"+SOQ+"')";
             String QU="update inbound_to_future set PartnerName = '"+PartnerName+"',ManufacturerName ='"+ManufacturerName+"',MPN='"+MPN+"',Region='"+Region+"',ATS='"+ATS+"',SOQ='"+SOQ+"' where MPN='"+MPN+"' and Region='"+Region+"'"; 
            
             
             
             int tot=chkRecordExist(chk_mpn,chk_region);
             
             
             
             if (tot>0)
             {
            	 System.out.println("update qery"+QU);
            	 stmt.executeUpdate(QU);
            	 
            
             }
             else
             {
            	 System.out.println("Insert qery"+Q);
            	 stmt.executeUpdate(Q);
            	
     		 
             }
 
     		
     		
            } 
		} catch (Exception eee) {
			System.out.println("error in File Reading " + eee.toString());
		}
	}
 
	public Connection connections() {
		Connection con = null;
		try {
			
			//Class.forName("org.gjt.mm.mysql.Driver").newInstance();
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager
					.getConnection("");
			
 
		} catch (Exception eee) {
			System.out.println("error " + eee.toString());
		}
		return con;
	}
	public void  closeConnections(Connection con) {
		try
		{
		con.close();
		} catch (Exception eee) {
			System.out.println("error in closeing connection " + eee.toString());
		}
	}
}

Open in new window

0
Comment
Question by:priyapratheep
[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
  • 5
  • 3
8 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22699931
Where are you passing the location of you MySQL server and the proper credentials?  Hopefully you just omitted in this post to hide details, but just make sure in your real code you are actually connecting to a MySQL server using a username and password with enough rights to do what you need.
0
 

Author Comment

by:priyapratheep
ID: 22700068
Obviously i should remove the userid and pwd since i am goint to post in a fourm....
0
 

Author Comment

by:priyapratheep
ID: 22700070
this is a working code where i remove userid and pwd
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:priyapratheep
ID: 22700212
i solve my problem by giving sleep at chkRecordExist()

but it is not a proper way right ?....

my exception is becuase of frequent opening and closing of connection....

any suggetions regarding this?

before inserting or updating records should check record is already there or not...any other idea to do this

0
 

Author Comment

by:priyapratheep
ID: 22700338
any suggestions pls?

usually how people will check data is already in table or not while bulk updations?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22701055
Couple of things:
+For checking record exists, I would say this is up to you but probably unnecessary if you have a primary key defined.  Make the combination of those two values unique together so the database will reject duplication.
+If you still want to check, open one connection at the beginning of bulk update/insert so that calls to the this function don't keep openning new connection.
+If you prefer this way, ensure that you close connection in a finally block.  If your code gets an exception, it will not close the connection as it is in the try block with rest of code.  Move it to the finally block and you should be fine as you close code has a try on it so if connection already closed at that point or doesn't exist, it won't matter.
0
 

Author Comment

by:priyapratheep
ID: 22707916
first thing i cant use since if there is record exist then i should update the field..not to quit...ok if i put my update statement in exception block then if really a excetion came then it will update right?

here also i opened connection for updation and i am closing that at the end of bulkupdation/insertion.while on checking only i am opeining and closing the connection...pls check my code...

anyway thanks for the suggestions

pls advice me what to do? and have a look at my code....
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22707968
try
              {
                    con1 =  connections();; // MWVisa1: only need one ;
                      stmt1 = con1.createStatement();
              }
              catch(Exception e)
              {
                  System.out.println((new StringBuilder("error in connction to sql in check record exist")).append(e).toString());
              }
// MWVisa1: you could have error in connection here and thus no statement, but then moving on in code anyway
              try
              {  
                    
                    chk_mpn=chk_mpn.replace("\"", "");
                    chk_region=chk_region.replace("\"", "");
                    
                  String getCountQuery = "select count(*) from inbound_to_future where MPN = '"+chk_mpn+"'and Region = '"+chk_region+"'";
                  System.out.println("record count query"+getCountQuery);
                  for(rs = stmt1.executeQuery(getCountQuery); rs.next();)
                        rec_count = rs.getInt(1);
 
              
                  stmt1.close();
                  con1.close(); // MWVisa1: this statement may not be reached is an exception thrown earlier
              }
              catch(Exception e)
              {
                  System.out.println("in record count");
              }
// MWVisa1: can add a finally block to cure issue I just mentioned
finally
{
    closeConnections(con1);
}

My main suggestion was to make sure in your updateTable that you have appropriate creation of connection which I see and closing of connection in a finally block (I don't see but may be there) and then pass connection to your checkExists method instead of creating a new one.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Sub-Select Query Returning Duplicate Result 7 51
Import MySQL data into MS Access using VB.Net interface 5 47
junit example issue 2 23
Survey branching tutorial 11 38
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

735 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