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
Estemensaje 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 00y 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).
|