Here's a snippet of my code, this method was with the described problem
public void insertVDNInfo(VDNInfo vdns) throws SQLException {
Calendar c = Calendar.getInstance();
Iterator<VDNInfo.VDN> it = vdns.getVdns().iterator();
Connection c1 = null;
Statement s1 = null;
try {
while (it.hasNext()) {
c1 = getConnection();
s1 = c1.createStatement();
VDNInfo.VDN v = it.next();
String query = "INSERT INTO list.vdn VALUES ("
+ "'" + v.getName() + "'," + v.getExt() + "," +
v.isVdnovr() + "," + v.getCor() + "," + v.getTn() + "," + v.getVecNum() + ",'"
+ v.getMeas() + "',"
+ v.isOrigAnnc() + "," + v.isEvntNotiAdj() + ",'" +
sdfTimestamp.format(c.getTime()) + "')";
s1.execute(query);
}
} finally {
if (s1 != null) {
s1.close();
}
s1 = null;
if (c1 != null) {
c1.close();
}
c1 = null;
}
}
Objects are created like
private Connection getConnection() {
DataSource ds = null;
Connection c1 = null;
try {
ds = (DataSource) new
InitialContext().lookup("java:/comp/env/jdbc/pgsql");
c1 = ds.getConnection();
} catch (NamingException ex) {
log.error("", ex);
} catch (SQLException ex) {
log.error("", ex);
}
return c1;
}
what i can see different from the example on
http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html is
that the SQLException is now thrown but catched locally in the method.
and here's my resource pool config
<Context antiJARLocking="true" path="/xcall3">
<Resource
auth="Container"
driverClassName="org.postgresql.Driver"
maxActive="10"
maxIdle="3"
name="jdbc/pgsql"
password="xcall3"
type="javax.sql.DataSource"
url="jdbc:postgresql://localhost/xcall3"
username="xcall3"
validationQuery="select version();"
maxWait = "5000"
/>
here's what logs the database
2011-05-07 23:02:25 ARTLOG: execute <unnamed>: INSERT INTO list.vdn VALUES
('Atencion a Clientes',7022,false,1,1,33,'int',false,false,'2011-05-07
23:02:25')
2011-05-07 23:02:25 ARTLOG: unexpected EOF on client connection
until i lose all my connections.
here's what i got from the webapp log after that
2011-05-06 13:34:38,430 ERROR DAO:47 -
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection,
general error
at
org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:118)
at
org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at com.lesi.xcall3.core.DAO.getConnection(DAO.java:43)
at com.lesi.xcall3.core.DAO.getSchedulerTasks(DAO.java:317)
at com.lesi.xcall3.core.Scheduler.run(Scheduler.java:35)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.InterruptedException
at java.lang.Object.wait(Native Method)
at java.lang.Object.wait(Object.java:485)
at
org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1104)
at
org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
... 5 more
Exception in thread "XCALL3-Scheduler" java.lang.NullPointerException
at java.util.ArrayList.toArray(ArrayList.java:303)
at com.lesi.xcall3.core.DAO.getSchedulerTasks(DAO.java:384)
at com.lesi.xcall3.core.Scheduler.run(Scheduler.java:35)
at java.lang.Thread.run(Thread.java:662)
On May 8, 2011, at 12:19 PM, Felix Schumacher wrote:
> Am Sonntag, den 08.05.2011, 11:42 -0300 schrieb alexis:
>> Hello all, is there any way to recover lost connectios on a pool?
>>
>> Here's the issue, for an application, using postgresql jdbc (same happens
>> with mysql as i tried), having query errors syntax or duplicated keys,
>> actually no big deal, it throws an sqlexception upon que sql error, as this
>> call executes ~150 queries to insert, on each query i lost the connection,
>> so suddenly i have all my connections lost and my app useless.
>>
>> How can i do from the catch clause of the SQLException to ask the pool to
>> reconnect? Because today we are in debug stage but soon to be production and
>> when this happens (we've corrected a lot of queries and situations but for
>> sure some new one will arise) we need to restart the webapp.
> SQL Connections should not get lost when used properly. You have to
> close the connections and every resources you got out of that connection
> in case of a program error. Look for "jdbc try finally".
>
> As a helper to find connections, you did not close properly you can
> configure your jdbc pool to show your abandoned connections. Another
> safety net are the validation queries. For further information look at
> http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html
>
> Regards
> Felix
>>
>> thanks in advance.
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
