Error connect to db after several transaction

[Koon Jiun]

Hi all,

I've encountered error connecting to db while inserting records into postgresql using java.

i have 4k records to be inserted to db from a text file,
first i read the text file and put into List<String>
and from List, i do a for loop for each string in the list to insert to db.
the program run without any problem until around 1960+ records, it throws :-
org.postgresql.util.PSQLException: The connection attempt failed.

i did close rs,ps,con for each newly created connection.

is it a postgres problem or java problem?

Thanks

[Muhammad Saifuddin]

Originally posted by Koon Jiun:


is it a postgres problem or java problem?

I can say that there must be something is incorrect in your code not in postgres/java. Exception is occur d when something is incorrect or may be in your db connection code..

could your share you code or stack trace to help you to find out this problem.

[Koon Jiun]

        List<String> studentNames = readFile("fileName.txt");
        int i = 1;
        for(String name : studentNames) {
            String sql = "INSERT INTO student VALUES(?,?)";
            int count = 0;
            try{
                Connection con = DriverManager.getConnection(POSTGRES_URL, POSTGRES_USER, POSTGRES_PASSWORD);
                PreparedStatement pstmt = con.prepareStatement(sql);
                pstmt.setInt(1,i);
                pstmt.setString(2,name);
                count = pstmt.executeUpdate();
                pstmt.close();
                con.close();
                i++;
            } catch(SQLException e) {
                throw new RuntimeException(e);
            }
            System.out.println("Records inserted so far - " + count);
        }
    

Exception in thread "main" java.lang.RuntimeException: org.postgresql.util.PSQLException: The connection attempt failed.

it throws this error after 1960+- records inserted

what could be the problem?
[ December 18, 2006: Message edited by: Koon Jiun ]

[Muhammad Saifuddin]

Originally posted by Koon Jiun:

      
for(String name : studentNames) {
String sql = "INSERT INTO student VALUES(?,?)";
int count = 0;
try {
 [B]Connection con = DriverManager.getConnection(POSTGRES_URL,POSTGRES_USER, POSTGRES_PASSWORD);[/B] // paste it before the loop
    PreparedStatement pstmt = con.prepareStatement(sql);
    pstmt.setInt(1,i);
    pstmt.setString(2,name);
    count = pstmt.executeUpdate();
 [B]pstmt.close();[/B] // Line 1
 [B]con.close(); [/B]  // Line 2
    i++;
    }//try
    catch(SQLException e)
    {
        throw new RuntimeException(e);
    }
    System.out.println("Records inserted so far - " + count);
}// for loop
 
    

Do some changes to paste Line 1 and 2 after the for Loop bracket "}". Actually in your For Loop connection and prepared statement object were created and close on every record insertion. like what you said its insert 1960+ record in the database hope you will understand. please do separate this code to reduce the time of extra process and increase the performance.

hope it helps..
[ December 19, 2006: Message edited by: Saif uddin ]