tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From alexis <alz...@gmail.com>
Subject Re: recovering connections on jdbc connection pool
Date Sun, 08 May 2011 15:29:52 GMT
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: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>> 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message