Thanks for your answers.

I've found the solution Myself.

1) You have 2 different blocks in a jsp file:
   
    The one into <%! %> tags is about definition
    The one into <% %> tags is about coding

2) A jsp file (script.jsp) is transfomed by tomcat into a single class file into the work directory including the source code of the
sceipt (with all the includes)

3) 'definition block' goes into the definition part of the class in the generated class and 'code block' goes into one method of the class:

     public void _jspService(final javax.servlet.http.HttpServletRequest request, final javax.servlet.http.HttpServletResponse response)

4) I assume that the Tomcat Container, instances the class depending of the retrieved requests. This is WHY Tomcat is a Container.

5) Tomcat, creates ONE (or maybe SOME) Class object and call to the 
_jspService on every script request

What happens if you handle Pool Coonections with a 'java.sql.Connection conn' variable declared into the definitions block "<%! %>"?

Happens than if you are donig multitheading and executing the same sctipt in parallel you will mix up connections because evey thead is executing the same method in parallel and putting a different connection into the
java.sql.Connection conn class variable.

I don't know if this is a tomcat bug.

   


FIRMA


Este  mensaje se dirige exclusivamente a su destinatario. Contiene información CONFIDENCIAL sometida a secreto profesional o cuya divulgación está prohibida por Ley.
Si ha recibido este mensaje por error, debe saber que su lectura, copia y uso están prohibidos.
Le rogamos nos lo comunique inmediatamente por esta misma vía o por teléfono 93 600 36 00  y proceda a su destrucción.
El correo electrónico vía Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepción.

TEMPEL S.A. no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilización del correo electrónico vía Internet y la grabación de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.
En cumplimiento de la Ley Orgánica 15/1999, de 13 de diciembre, de Protección de Datos de Carácter Personal le informamos de que sus datos personales y de empresa pasarán a formar parte de un fichero registrado ante la Agencia Española de Protección de Datos.
Los datos personales que existen en nuestro poder están protegidos por nuestra Política de Seguridad, y no serán compartidos con ninguna otra empresa.
Usted puede ejercitar sus derechos de acceso, rectificación, cancelación y oposición dirigiéndose por escrito a c/ Cobalto, 4 08907 L'Hospitalet de LLobregat (Barcelona).


El 14/12/2011 17:21, Aitor Garcia | Tempel.es escribió:
Hi,

After three days of research I've found notthing that helps me to solve this problem, it's why I'm trying to get help from the list.

I'm getting connections from dbcp pool in this way:
/*********************************************************/
/* Open a connection to the database                     */
/*********************************************************/


// AITOR 13/12/2011
// Seems that connections stay active
java.sql.Connection conn = null;

// PreparedStatement prestmt = null; 
Statement stmt = null; 
ResultSet res = null;

/*********************************************************/
/* Open a connection to the database                     */
/*********************************************************/

public boolean web_dbConnect()  throws SQLException
{
   
    try
    {
        javax.naming.Context initContext = new javax.naming.InitialContext();
         javax.naming.Context envContext  = (javax.naming.Context) initContext.lookup("java:/comp/env");
        javax.sql.DataSource datasource = (javax.sql.DataSource) envContext.lookup("jdbc/localpool");
       
        // get connection from datasource
        conn = datasource.getConnection();

        // wrap the connection with log4jdbc
        conn = new net.sf.log4jdbc.ConnectionSpy(conn);

        // now use Connection as normal (but it will be audited by log4jdbc)
        web_dbLog("Get DB Connection '" + conn + "'");
       
        /*
        DatabaseMetaData meta = conn.getMetaData();
      
        web_dbLog("Driver Name: " + meta.getDriverName());
        web_dbLog("Driver Version: " + meta.getDriverVersion());       
        web_dbLog("Max Connections: " + meta.getMaxConnections());       
        */
       
        /*
        ResultSet res = meta.getTables(null, null, "%", null);
       
        while (res.next())
        {
            web_dbLog(res.getString(3));
        }
        */       
    }
    catch( javax.naming.NamingException ne )
    {
        // throw new RuntimeException( "Unable to aquire data source", ne );
        web_dbLog("ERROR: Unable to aquire DB Connection from Datasource");

        return (false);
       

    }


   
    return (true);
   
}

Closing connection here:


/*********************************************************/
/* Close the connection to the database                     */
/*********************************************************/

public void web_dbClose() throws SQLException
{
    // Never close the database connection, because
    // it may interfere with other scripts which
    // share the same connection.
    web_dbLog("Return DB Conection to pool");
   
   


    if(res == null)
    {
        web_dbLog("Sorry, ResultSet in null");
    }
    else
    {
        res.close();
        res = null;

        web_dbLog("ResultSet closed!");
    }


    if(stmt == null)
    {
        web_dbLog("Sorry, Statement in null");
    }
    else
    {
        stmt.close();
        stmt = null;

        web_dbLog("Statement closed!");
    }

    if(conn == null)
    {
        web_dbLog("Sorry, Connection in null");
    }
    else
    {
        conn.close();
        conn = null;

        web_dbLog("Connection closed!");
    }
}

All was working UNTIL AJAX comes. I have an AJAX script that launches different JSP scripts at the same time, and seems that comcat mix the connections:

The .jsp just

1) Opens connections
2) Make a SELECT statement
3) Make an UPDATE
2) Close a connection

See the code:


    String SQL = "";


   web_dbConnect();
   
    HashMap section = new HashMap();

    // Que valor tiene actualmente el menú
    SQL = "SELECT ... ";
   
   
  
    section = web_getElem(SQL);

   



    SQL = "UPDATE "...;



    web_dbUpdate(SQL);
 


    web_dbClose();



Code works well but connections mix, I have installed log4jdbc to see whan happens & the probler arise with the micure of connections into different threads:

15 [http-bio-8080-exec-4] INFO jdbc.connection - 1. Connection opened 15 [http-bio-8080-exec-4] INFO jdbc.audit - 1. Connection.new Connection returned 16 [http-bio-8080-exec-6] INFO jdbc.connection - 2. Connection opened 17 [http-bio-8080-exec-7] INFO jdbc.connection - 3. Connection opened 17 [http-bio-8080-exec-7] INFO jdbc.audit - 3. Connection.new Connection returned 17 [http-bio-8080-exec-6] INFO jdbc.audit - 2. Connection.new Connection returned 17 [http-bio-8080-exec-2] INFO jdbc.connection - 4. Connection opened 17 [http-bio-8080-exec-2] INFO jdbc.audit - 4. Connection.new Connection returned 22 [http-bio-8080-exec-7] INFO jdbc.audit - 4. Statement.new Statement returned 22 [http-bio-8080-exec-7] INFO jdbc.audit - 4. Connection.createStatement(1004, 1007) returned net.sf.log4jdbc.StatementSpy@3a0589 22 [http-bio-8080-exec-6] INFO jdbc.audit - 4. Statement.new Statement returned 22 [http-bio-8080-exec-7] INFO jdbc.sqlonly - SELECT ... 22 [http-bio-8080-exec-4] INFO jdbc.audit - 4. Statement.new Statement returned 22 [http-bio-8080-exec-4] INFO jdbc.audit - 4. Connection.createStatement(1004, 1007) returned net.sf.log4jdbc.StatementSpy@12a2259 23 [http-bio-8080-exec-4] INFO jdbc.sqlonly - SELECT... 22 [http-bio-8080-exec-6] INFO jdbc.audit - 4. Connection.createStatement(1004, 1007) returned net.sf.log4jdbc.StatementSpy@14177f3 23 [http-bio-8080-exec-6] INFO jdbc.sqlonly - SELECT ... 36 [http-bio-8080-exec-7] INFO jdbc.sqltiming - SELECT.... {executed in 13 msec} 42 [http-bio-8080-exec-7] INFO jdbc.resultset - 4. ResultSet.new ResultSet returned 42 [http-bio-8080-exec-7] INFO jdbc.audit - 4. Statement.executeQuery(SELECT ... ) returned net.sf.log4jdbc.ResultSetSpy@2a2ae9 42 [http-bio-8080-exec-7] INFO jdbc.resultset - 4. ResultSet.getRow() returned 0 42 [http-bio-8080-exec-7] INFO jdbc.resultset - 4. ResultSet.last() returned true 42 [http-bio-8080-exec-7] INFO jdbc.resultset - 4. ResultSet.getRow() returned 0 42 [http-bio-8080-exec-7] INFO jdbc.resultset - 4. ResultSet.beforeFirst() returned 57 [http-bio-8080-exec-2] INFO jdbc.audit - 4. Statement.new Statement returned 57 [http-bio-8080-exec-2] INFO jdbc.audit - 4. Connection.createStatement(1004, 1007) returned net.sf.log4jdbc.StatementSpy@1b56848 57 [http-bio-8080-exec-2] INFO jdbc.sqlonly - SELECT.... 60 [http-bio-8080-exec-4] INFO jdbc.sqltiming - SELECT... {executed in 37 msec} 60 [http-bio-8080-exec-4] INFO jdbc.resultset - 4. ResultSet.new ResultSet returned 60 [http-bio-8080-exec-4] INFO jdbc.audit - 4. Statement.executeQuery(SELECT ... ) returned net.sf.log4jdbc.ResultSetSpy@1c26db4 60 [http-bio-8080-exec-4] INFO jdbc.resultset - 4. ResultSet.getRow() returned 0 60 [http-bio-8080-exec-4] INFO jdbc.resultset - 4. ResultSet.last() returned true 60 [http-bio-8080-exec-4] INFO jdbc.resultset - 4. ResultSet.getRow() returned 0 60 [http-bio-8080-exec-4] INFO jdbc.resultset - 4. ResultSet.beforeFirst() returned 96 [http-bio-8080-exec-6] INFO jdbc.sqltiming - SELECT ... {executed in 73 msec} 96 [http-bio-8080-exec-6] INFO jdbc.resultset - 4. ResultSet.new ResultSet returned 96 [http-bio-8080-exec-6] INFO jdbc.audit - 4. Statement.executeQuery(SELECT ...) returned net.sf.log4jdbc.ResultSetSpy@1b0620c 96 [http-bio-8080-exec-6] INFO jdbc.resultset - 4. ResultSet.getRow() returned 0 96 [http-bio-8080-exec-6] INFO jdbc.resultset - 4. ResultSet.last() returned true 96 [http-bio-8080-exec-6] INFO jdbc.resultset - 4. ResultSet.getRow() returned 0 96 [http-bio-8080-exec-6] INFO jdbc.resultset - 4. ResultSet.beforeFirst() returned 102 [http-bio-8080-exec-4] INFO jdbc.audit - 4. PreparedStatement.new PreparedStatement returned 102 [http-bio-8080-exec-7] INFO jdbc.audit - 4. PreparedStatement.new PreparedStatement returned 102 [http-bio-8080-exec-4] INFO jdbc.audit - 4. Connection.prepareStatement(UPDATE ...) returned net.sf.log4jdbc.PreparedStatementSpy@d4a1d3 102 [http-bio-8080-exec-7] INFO jdbc.audit - 4. Connection.prepareStatement(UPDATE ...) returned net.sf.log4jdbc.PreparedStatementSpy@1cc3baa 103 [http-bio-8080-exec-4] INFO jdbc.sqlonly - UPDATE ... 103 [http-bio-8080-exec-7] INFO jdbc.sqlonly - UPDATE .... 133 [http-bio-8080-exec-2] INFO jdbc.sqltiming - SELECT ... {executed in 76 msec} 133 [http-bio-8080-exec-2] INFO jdbc.resultset - 4. ResultSet.new ResultSet returned 133 [http-bio-8080-exec-2] INFO jdbc.audit - 4. Statement.executeQuery(SELECT ... ) returned net.sf.log4jdbc.ResultSetSpy@1611aec 133 [http-bio-8080-exec-2] INFO jdbc.resultset - 4. ResultSet.getRow() returned 0 133 [http-bio-8080-exec-2] INFO jdbc.resultset - 4. ResultSet.last() returned true 133 [http-bio-8080-exec-2] INFO jdbc.resultset - 4. ResultSet.getRow() returned 0 133 [http-bio-8080-exec-2] INFO jdbc.resultset - 4. ResultSet.beforeFirst() returned 134 [http-bio-8080-exec-6] INFO jdbc.audit - 4. PreparedStatement.new PreparedStatement returned 134 [http-bio-8080-exec-6] INFO jdbc.audit - 4. Connection.prepareStatement(UPDATE ...) returned net.sf.log4jdbc.PreparedStatementSpy@2e9c76 135 [http-bio-8080-exec-6] INFO jdbc.sqlonly - UPDATE ... 197 [http-bio-8080-exec-7] INFO jdbc.sqltiming - UPDATE ... {executed in 93 msec} 197 [http-bio-8080-exec-7] INFO jdbc.audit - 4. PreparedStatement.executeUpdate() returned 1 225 [http-bio-8080-exec-8] INFO jdbc.connection - 5. Connection opened 225 [http-bio-8080-exec-8] INFO jdbc.audit - 5. Connection.new Connection returned 227 [http-bio-8080-exec-8] INFO jdbc.audit - 5. Statement.new Statement returned 227 [http-bio-8080-exec-8] INFO jdbc.audit - 5. Connection.createStatement(1004, 1007) returned net.sf.log4jdbc.StatementSpy@11f2041 227 [http-bio-8080-exec-8] INFO jdbc.sqlonly - SELECT ... 249 [http-bio-8080-exec-4] INFO jdbc.sqltiming - UPDATE .... {executed in 146 msec} 249 [http-bio-8080-exec-4] INFO jdbc.audit - 4. PreparedStatement.executeUpdate() returned 1 250 [http-bio-8080-exec-8] INFO jdbc.sqltiming - SELECT ... {executed in 23 msec} 250 [http-bio-8080-exec-8] INFO jdbc.resultset - 5. ResultSet.new ResultSet returned 250 [http-bio-8080-exec-8] INFO jdbc.audit - 5. Statement.executeQuery(SELECT ... ) returned net.sf.log4jdbc.ResultSetSpy@7e942f 250 [http-bio-8080-exec-8] INFO jdbc.resultset - 5. ResultSet.getRow() returned 0 250 [http-bio-8080-exec-8] INFO jdbc.resultset - 5. ResultSet.last() returned true 250 [http-bio-8080-exec-8] INFO jdbc.resultset - 5. ResultSet.getRow() returned 0 250 [http-bio-8080-exec-8] INFO jdbc.resultset - 5. ResultSet.beforeFirst() returned 250 [http-bio-8080-exec-7] INFO jdbc.audit - 4. PreparedStatement.close() returned 251 [http-bio-8080-exec-10] INFO jdbc.connection - 6. Connection opened 251 [http-bio-8080-exec-10] INFO jdbc.audit - 6. Connection.new Connection returned 252 [http-bio-8080-exec-10] INFO jdbc.audit - 6. Statement.new Statement returned 252 [http-bio-8080-exec-10] INFO jdbc.audit - 6. Connection.createStatement(1004, 1007) returned net.sf.log4jdbc.StatementSpy@74ece8 252 [http-bio-8080-exec-10] INFO jdbc.sqlonly - SELECT... 261 [http-bio-8080-exec-10] INFO jdbc.sqltiming - SELECT ... {executed in 9 msec} 261 [http-bio-8080-exec-10] INFO jdbc.resultset - 6. ResultSet.new ResultSet returned 261 [http-bio-8080-exec-10] INFO jdbc.audit - 6. Statement.executeQuery(SELECT ...) returned net.sf.log4jdbc.ResultSetSpy@165d118 261 [http-bio-8080-exec-10] INFO jdbc.resultset - 6. ResultSet.getRow() returned 0 261 [http-bio-8080-exec-10] INFO jdbc.resultset - 6. ResultSet.last() returned true 261 [http-bio-8080-exec-10] INFO jdbc.resultset - 6. ResultSet.getRow() returned 0 261 [http-bio-8080-exec-10] INFO jdbc.resultset - 6. ResultSet.beforeFirst() returned 264 [http-bio-8080-exec-10] INFO jdbc.audit - 6. PreparedStatement.new PreparedStatement returned 264 [http-bio-8080-exec-10] INFO jdbc.audit - 6. Connection.prepareStatement(UPDATE ...) returned net.sf.log4jdbc.PreparedStatementSpy@18297fe 264 [http-bio-8080-exec-8] INFO jdbc.audit - 5. PreparedStatement.new PreparedStatement returned 264 [http-bio-8080-exec-8] INFO jdbc.audit - 5. Connection.prepareStatement(UPDATE....) returned net.sf.log4jdbc.PreparedStatementSpy@c8aeb3 269 [http-bio-8080-exec-7] INFO jdbc.resultset - 5. ResultSet.close() returned 269 [http-bio-8080-exec-10] INFO jdbc.sqlonly - UPDATE .... 278 [http-bio-8080-exec-8] INFO jdbc.sqlonly - UPDATE... 340 [http-bio-8080-exec-6] INFO jdbc.sqltiming - UPDATE .. {executed in 205 msec} 340 [http-bio-8080-exec-6] INFO jdbc.audit - 4. PreparedStatement.executeUpdate() returned 1 341 [http-bio-8080-exec-2] INFO jdbc.audit - 4. PreparedStatement.new PreparedStatement returned 341 [http-bio-8080-exec-2] INFO jdbc.audit - 4. Connection.prepareStatement(UPDATE ...) returned net.sf.log4jdbc.PreparedStatementSpy@5f634c 342 [http-bio-8080-exec-6] INFO jdbc.audit - 4. PreparedStatement.close() returned 343 [http-bio-8080-exec-4] INFO jdbc.audit - 4. PreparedStatement.close() returned 344 [http-bio-8080-exec-2] INFO jdbc.sqlonly - UPDATE ... 365 [http-bio-8080-exec-2] INFO jdbc.sqltiming - UPDATE ... {executed in 21 msec} 365 [http-bio-8080-exec-2] INFO jdbc.audit - 4. PreparedStatement.executeUpdate() returned 1 367 [http-bio-8080-exec-2] INFO jdbc.audit - 4. PreparedStatement.close() returned 428 [http-bio-8080-exec-10] INFO jdbc.sqltiming - UPDATE ...' {executed in 159 msec} 428 [http-bio-8080-exec-10] INFO jdbc.audit - 6. PreparedStatement.executeUpdate() returned 1 431 [http-bio-8080-exec-7] INFO jdbc.audit - 6. Statement.close() returned 433 [http-bio-8080-exec-7] INFO jdbc.connection - 6. Connection closed 433 [http-bio-8080-exec-7] INFO jdbc.audit - 6. Connection.close() returned 436 [http-bio-8080-exec-10] INFO jdbc.audit - 6. PreparedStatement.close() returned 436 [http-bio-8080-exec-8] INFO jdbc.sqltiming - UPDATE ... {executed in 158 msec} 436 [http-bio-8080-exec-8] INFO jdbc.audit - 5. PreparedStatement.executeUpdate() returned 1 439 [http-bio-8080-exec-6] INFO jdbc.audit - 6. Statement.close() returned 439 [http-bio-8080-exec-4] INFO jdbc.audit - 6. Statement.close() returned 439 [http-bio-8080-exec-2] INFO jdbc.audit - 6. Statement.close() returned 453 [http-bio-8080-exec-8] INFO jdbc.audit - 5. PreparedStatement.close() returned


I've tested this into : Centos / Windows Vista
Tomcat : 7.0 (diferent )
Java: Jre 1.6.0_16, 1.7.0 & 1.7.2

And happens the same.

Thnks. Any help would be appreciated!


--

FIRMA


Este  mensaje se dirige exclusivamente a su destinatario. Contiene información CONFIDENCIAL sometida a secreto profesional o cuya divulgación está prohibida por Ley.
Si ha recibido este mensaje por error, debe saber que su lectura, copia y uso están prohibidos.
Le rogamos nos lo comunique inmediatamente por esta misma vía o por teléfono 93 600 36 00  y proceda a su destrucción.
El correo electrónico vía Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepción.

TEMPEL S.A. no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilización del correo electrónico vía Internet y la grabación de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.
En cumplimiento de la Ley Orgánica 15/1999, de 13 de diciembre, de Protección de Datos de Carácter Personal le informamos de que sus datos personales y de empresa pasarán a formar parte de un fichero registrado ante la Agencia Española de Protección de Datos.
Los datos personales que existen en nuestro poder están protegidos por nuestra Política de Seguridad, y no serán compartidos con ninguna otra empresa.
Usted puede ejercitar sus derechos de acceso, rectificación, cancelación y oposición dirigiéndose por escrito a c/ Cobalto, 4 08907 L'Hospitalet de LLobregat (Barcelona).