Sql Connection busy...

try this:

synchronized (conn) {
  // operating with the connection
}

where conn is your database connection

where to add it?in run method for each thread?

enclose the usage of the database connection with this. For example:
synchronized (conn) {
 statement = conn.createStatemt();
 statement.executeUpdate("");
}

This code:
synchronized (conn) {}
means that the enclosed code need the 'monitor' of the 'conn' object. The monitor is released after the code is ececuted. If a thread cannot acquire the monitor - it will sleep until the monitor is released by another thread.

where to add it?in run method for each thread?

if you are using the database connections directly in the run() methods of the threads - then yes.

now...one of the thread is waiting, and another couldn't finish processing. Actually i have a main java program that generate N theard. I only declare a contant of sql(conn) in main.java . izit will course problems?

Both threads is continuous thread. It will wake up, perfome some task, and sleep again waiting for another wake up time.

It doesn't matter what exactly the threads are doing. The 'synchronized' word is used to lock the object (in this case - the connection) for the code enclosed in this section. You don't need to do any additional steps for sleeping if another thread has locked this object. When the 'synchronized' code must be ececuted - the thread will start sleeping and will wake up when the onject ('conn') is released.

if i haven't synchronize the thread, this error msg will occur : java.SQLException;[Microsoft][ODBC SQL Server Driver] Connection is busy with results for another hstmt.

In my program, main.java generate N threads(task.java) and a Update.java to update the Thread's sleeptime. ok, when i sychronize the run() method for task.java and Update.java. The Update.java will run script before i the start() method, and Update.java will stop here before start() is call. But the task.java work fine.
Hope u can help me with this, thank you very much.

Ok - can you post some code then

I am using RMI, the code quite long. so i post the sql connection program. In my main.java, i just create a constant, RemoteSQL sql = new RemoteSQL().

import java.rmi.*;
import java.sql.*;
import java.util.Vector;
import java.io.*;
import java.net.*;

import java.text.SimpleDateFormat;
import java.text.MessageFormat;
import java.text.DecimalFormat;
import java.util.SimpleTimeZone;
import java.text.ParsePosition;
import java.util.Calendar;

import java.util.GregorianCalendar;

public class RemoteSQL {
 
        public static int connectionId;
        RmiJdbc servlet;
        String result[];
        public static String strStatus;
        public static String strQueryResult[][];
 
        public RemoteSQL() {
            try {
                    servlet = (RmiJdbc)Naming.lookup("rmi://172.18.18.74/AccessDatabase");
                    connectionId = servlet.openConnection();
            } catch (RemoteException re) {
                    System.out.println( "A Remote Exception was thrown when requesting the HelloService" );
                    System.out.println( "" + re );
            } catch (MalformedURLException mURLe) {
                    System.out.println( "There is a problem with the rmi: URL you are using" );
                    System.out.println( "" + mURLe );
            } catch (NotBoundException nbe) {
                    System.out.println( "" + nbe );
            } catch (SQLException se) {
                    System.out.println( "" + se );
            } catch (IOException ioe) {
                    System.out.println( "" + ioe );
            } catch (ClassNotFoundException ce) {
                    System.out.println( "" + ce );
            }
        }

 
        public Vector performQuery(String str) {
               
                String searchString = str;
                Vector v = new Vector(0,1);
                String result;
               
                try {
                        servlet.performQuery(connectionId, searchString);
                        // Get and display the result set.
                      result = servlet.getNextRow( connectionId );
                        int index = 0;
                        if ( result == null ) {
                                System.out.println("No rows found using specified search criteria");
                                v.clear();
                        } else {        
                          while ( result != null ) {
                                      v.add(index, result);
                                      index++;
                                      result = servlet.getNextRow( connectionId );
                              }
                      }
                } catch (RemoteException re) {
                        System.out.println(re);
                        v.clear();
                } catch (SQLException se) {
                        System.out.println(se);
                        v.clear();
                } finally {
                        return v;
                }
        }
       
        public String[][] loadQuery(String strQuery, int intNumCol) {
        //public int loadQuery(String strQuery, int intNumCol) {

                Vector v = new Vector(0,1);
                String strTemp[][];
                int length;
              /*  try {
                        v = performQuery(strQuery);  
                } catch (Exception e) {
                        e.printStackTrace();
                } */
               
                v = performQuery(strQuery);
               
                if (v.isEmpty()) {
                        return strTemp = null;
                } else {
                        length = v.capacity();
                }
                if (length == 0) {
                        return strTemp = null;
                } else {
                        strTemp = new String[length][intNumCol];
 
                        for (int i = 0; i < length; i++) {
                                v.toString();
                                Object obj = v.elementAt(i);
                                String strRecord = obj.toString();                      
                                int intRecordLen = strRecord.length();
                                int j = 0;
                                int index = 0;

                                while (j < (intRecordLen-1)) {
                                        int intSeparator = strRecord.indexOf("|", j);
                                        String str = strRecord.substring(j, intSeparator);
                                        strTemp[i][index] = str;
                                        index = index + 1;
                                        j = intSeparator + 1;
                                }
                        }
                        return strTemp;
                }                                                    
        }
       
        public boolean insertRecord(String strStatement) {
               
                boolean status = false;
                try {
                        status = servlet.insertRecord(connectionId, strStatement);
                } catch (RemoteException re) {
                        System.out.println( "" + re);
                        status = false;
                } finally {
                        return status;
                }
               
         /*       try {
                        servlet.insertRecord(connectionId, strStatement);
                } catch (RemoteException re) {
                        System.out.println( "" + re);
                } catch (SQLException se) {
                        System.out.println( "" + se);
                } */
        }
       
        public void closeConnection() {
                try {
                        servlet.closeConnection(connectionId);
                } catch (RemoteException re) {
                        System.out.println( "" + re);
                } catch (SQLException se) {
                        System.out.println( "" + se);
                }
        }
       
        public String getDateTime() {
                try {
                        String strDateTime = servlet.getServerDateTime();
                        return strDateTime;
                } catch (RemoteException re) {
                        System.out.println(re);
                        return null;
                }
        }

        public String getDate() {
                try {
                        String strDate = servlet.getServerDate();
                        return strDate;
                } catch (RemoteException re) {
                        System.out.println(re);
                        return null;
                }
        }

        public String getTime() {
                try {
                        String strTime = servlet.getServerTime();
                        return strTime;
                } catch (RemoteException re) {
                        System.out.println(re);
                        return null;
                }
        }

        public int getWeekDay() {

              try
              {
                    return servlet.getServerWeekDay();
              }
              catch (RemoteException re)
              {
                          System.out.println(re);
                        return -99;
              }
        }
}

//this is the main.java

main() {
RemoteSQL sql = new RemoteSQL();
for (i=0, i< N; i++) {
     new task(sql,...);
}

new update(sql,...);

}
   

I found that the only place where you are using the database connection is your performQuery. In this case - you should synchronize the usage of this connection
       public Vector performQuery(String str) {
               
               String searchString = str;
               Vector v = new Vector(0,1);
               String result;
               
               try {
synchronized (connectionId) {
                     servlet.performQuery(connectionId, searchString);
                       // Get and display the result set.
                     result = servlet.getNextRow( connectionId );
                       int index = 0;
                       if ( result == null ) {
                               System.out.println("No rows found using specified search criteria");
                               v.clear();
                       } else {        
                        while ( result != null ) {
                                     v.add(index, result);
                                     index++;
                                     result = servlet.getNextRow( connectionId );
                             }
                     }
}
               } catch (RemoteException re) {
                       System.out.println(re);
                       v.clear();
               } catch (SQLException se) {
                       System.out.println(se);
                       v.clear();
               } finally {
                       return v;
               }
       }
       
       

Hi, do i still need to synchronized others threads?

connectioId is an int, java.lang.Object is required.

are the other threads calling the performQuery method of the RemoteSQL  class

I see - how many servlet objects do uou have?

may be you should use the
servlet
object for synchronization:

synchronized (servlet) {
....

instead of
synchronized (connectionid) {

-->>are the other threads calling the performQuery method of the RemoteSQL  class

Yes.

> how many servlet objects do uou have?

there are two : insertRecord and loadQuery

Do i need to synchronized those methods in update.java and task.java ?? both will get the remoteSql from main.java.

no - only the methods in the RemoteSql class

Venci, just a additional question bout the RemoteSQL class, if i stop the program without using closeConnection method, that mean the connection is still alive. In server, i set only allow 50 connections, after few times, connection will over 50 and error will occur. How do i stop the connection even i din use closeConnection method?

actually - I don't know any good solution to this. You should fina a way to close this connection - for example - create a
public void finalize() {
  closeConnection();
}

method of the RemoteSql class. This method will be called by the carbage collector when it is possible

ok, I din see any connection busy in the program already. I hope it will work fine. Thanks again. ^-^