[webking]

hello,
i'm using JDBC and OCSF to make client-server connection including MySQL.
i'm now stuck at a very strange problem:
my server can receive either "select..." string, "insert..." string, "update..." string (those are treated as queries), or any other string, which is treated as a simple message to the server (i do nothing with in so far)

here is the code of the server:

public void handleMessageFromClient (Object msg1, ConnectionToClient client) {
            String msg=((String)msg1).toLowerCase();
            if ( !( msg.startsWith("select") || msg.startsWith("insert") ||
                    msg.startsWith("update") ) ) {
                System.out.println("Message received: " + msg + " from " + client);
                this.sendToAllClients(msg);
                return;
            }
            try {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
            }
            catch (Exception ex) {
                System.out.println("Couldn't load MySQL driver...");
            }
            try {
                  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","Braude");
                  System.out.println("Connected to the database");  // indication on server only
                  Statement st = conn.createStatement();
                  // user sent a "select" query
                  if (msg.startsWith("select")){
                      int isResultEmpty=1;  // for indicating if the query returned no results
                      ResultSet rs=st.executeQuery(msg);
                      ResultSetMetaData rsmd=rs.getMetaData();
                      int numOfCols=rsmd.getColumnCount();
                      Vector result=new Vector();
                      while (rs.next()){
                          String[] tuple=new String[numOfCols];
                          for (int i=1;i<=numOfCols;i++)
                              tuple[i-1]=rs.getString(i);
                          result.addElement(tuple);
                          isResultEmpty=0;  // indicating at least one tuple in the result
                      }
                      if (isResultEmpty==0)
                          this.sendToAllClients(result);
                      else
                          this.sendToAllClients("No appropriate results!");
                      rs.close();
                  }  // end of select case

                  // if user tries to only update the db (no resultset is returned)
                  if (msg.startsWith("insert")  ||  msg.startsWith("update") ){
                      st.executeUpdate(msg);
                      this.sendToAllClients("Action successful!!");
                  }  // end of update case
                  st.close();
                  conn.close();
            }  // end of try
            catch (SQLException ex) { // handle any errors
                    this.sendToAllClients("SQLException: " + ex.getMessage());
                    this.sendToAllClients("SQLState: " + ex.getSQLState());
                    this.sendToAllClients("VendorError: " + ex.getErrorCode());
            }

  } // end of function

the problem is as follows:
if i send any series of simple messages (one or more), everything works fine.
when i send my first query (any of them, select / update / insert) , it executes.
but, after i send the first query, trying to send any message (query / simple one) ,
i get an error message, thrown by this code:

public void handleMessageFromClientUI(String message)
  {
    try
    {
        sendToServer(message);
    }
    catch(IOException e)
    {
      clientUI.display
        ("Could not send message to server.  Terminating client.");
      quit();
    }
  }

the sendToServer() method is inherited from the OCSF, i didn't touch it.
For conclusion, the problem is:
after sending simple messages, all fine. after sending a query, nothing else can be sent.
any idea will be very appreciated, I don't have a clue about what's wrong here..

[masijade]

It might help to print the stack trace of the Exception so you can see what is actually going wrong.

Also, the problem probably lies in your "comm" code code and not in the db code, as a note though, make sure to close the connections, statements, and resultsets in finally blocks or you will end up with "hanging" connections and eventually will use up all available connections to the DB.

Edit: P.S. get a connection pooling software (if standalone or use the one from the app server/web container if using those) as building a new connection is an expensive operation.

This post has been edited by masijade: 25 April 2011 - 12:17 PM

[webking]

here is the stack trace of the exception:

java.net.SocketException: socket does not exist 
at ocsf.client.AbstractClient.sendToServer(AbstractClient.java:141) 
at client.ChatClient.handleMessageFromClientUI(ChatClient.java:85) 
at ClientConsole.accept(ClientConsole.java:77) 
at ClientConsole.main(ClientConsole.java:121)

[masijade]

It looks as though someone, either the client or the server, has already closed and released the socket, maybe. As I said, the problem lies in your "comm" code, not in the "db" code.