Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (athena.apache.org: domain of alzrck@gmail.com designates
 209.85.213.173 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=content-type:mime-version:subject:from:in-reply-to:date
         :content-transfer-encoding:message-id:references:to:x-mailer;
        b=IWB/6IvsasoTP1J2BNDMwGswL3dmxj7isqQpnpcacpQg50ADPP5uciH00yvIkHjzME
         wcC1GGvtGCZqMh8TNl7SAmGKa3jvs4JTpUUXPXSg8hIvcM1TbbSObqkOPKeYjSBRTZoH
         ZfrSCN6IthnjjK8A7GuEvuuzeHe+2OUHJnv3U=
Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Apple Message framework v1084)
Subject: Re: recovering connections on jdbc connection pool
From: alexis <alzrck@gmail.com>
In-Reply-To: <1304871728.3954.52.camel@cat>
Date: Sun, 8 May 2011 14:04:26 -0300
Content-Transfer-Encoding: quoted-printable
Message-Id: <AC4F2B0D-3E79-4991-9325-932982BDC36A@gmail.com>
References: <6D018CA5-446F-489E-B21B-0F6251DA9A3C@gmail.com>
 <1304867956.3954.37.camel@cat>
 <290BE762-998B-4999-BEDC-A85AA74CBF8F@gmail.com>
 <1304871728.3954.52.camel@cat>
To: "Tomcat Users List" <users@tomcat.apache.org>


On May 8, 2011, at 1:22 PM, Felix Schumacher wrote:

> Am Sonntag, den 08.05.2011, 12:29 -0300 schrieb alexis:
>> Here's a snippet of my code, this method was with the described =
problem
>>=20
>>=20
>> public void insertVDNInfo(VDNInfo vdns) throws SQLException {
> Why do you want to throw a SQLException?
>=20

Because all methods regarding database access are called from the same =
class, im throwing the sqlexception and then catch that exception on the =
class that call all those methods.=20


>>=20
>>        Calendar c =3D Calendar.getInstance();
>>=20
>>        Iterator<VDNInfo.VDN> it =3D vdns.getVdns().iterator();
> Seems like you can use
> for (VDNInfo.VND v: vdns.getVdns()) {
> ...
> }
> instead of x=3Dy.iterator() combined with while(x.hasNext())
> { v=3Dx.next(); }
>=20

yes, you're right

>>        Connection c1 =3D null;
>>        Statement s1 =3D null;
>>=20
>>        try {
>>            while (it.hasNext()) {
>>                c1 =3D getConnection();
> Your getConnection method can return null, so you should guard against
> it.
>=20


>>                s1 =3D c1.createStatement();
>>                VDNInfo.VDN v =3D it.next();
>>=20
>>                String query =3D "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()) + "')";
> You should read about SQL-injections and Prepared Statements. You can
> declare the prepared statement outside of your loop and set the values
> inside the loop.

Yes, im aware about injection, but there's no chance in the way this app =
works that injection can be done, all public access has no way to reach =
database or methods that uses the database at all. But, im aware that =
using preparedstatements is more effective and easy to code.


>>=20
>>                s1.execute(query);
>>            }
>>        } finally {
>>            if (s1 !=3D null) {
>>                s1.close();
>>            }
> if s1.close() throws an error you will not close c1, so you have to
> catch that exception:
>    if (s1!=3Dnull) { try { s1.close } catch (SQLException e) { // =
Ignore
> or event better log
>    };
>>            s1 =3D null;
>>            if (c1 !=3D null) {
>>                c1.close();
> catch the possible Exception
>  if (c1!=3Dnull) { try {c1.close();} catch (SQLException e) { // =
Ignore
> or log
> };
>=20

yes, you're right. i will

>>            }
>>            c1 =3D null;
>>        }
>>    }
>>=20
>>=20
>>=20
>> Objects are created like
>>=20
>> private Connection getConnection() {
>>        DataSource ds =3D null;
>>        Connection c1 =3D null;
>>        try {
>>            ds =3D (DataSource) new =
InitialContext().lookup("java:/comp/env/jdbc/pgsql");
>>            c1 =3D ds.getConnection();
>>        } catch (NamingException ex) {
>>            log.error("", ex);
> Error messages should be a bit more meaningful than "".
>>        } catch (SQLException ex) {
>>            log.error("", ex);
>>        }
>>        return c1;
> You will return null, if you have catched an Exception. Is that what =
you
> want?


no problem with that only if i handle the null after that (ill correct =
the call to handle the null)

>>    }
>>=20
>>=20
>>=20
>> what i can see different from the example on =
http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.htm=
l is that the SQLException is now thrown but catched locally in the =
method.
> That difference can be essential, as explained above.
>=20

good to know, thanks again. changing now

>>=20
>>=20
>> and here's my resource pool config
>>=20
>>=20
>> <Context antiJARLocking=3D"true" path=3D"/xcall3">
>>    <Resource=20
>>    auth=3D"Container"=20
>>    driverClassName=3D"org.postgresql.Driver"=20
>>    maxActive=3D"10"=20
>>    maxIdle=3D"3"=20
>>    name=3D"jdbc/pgsql"=20
>>    password=3D"xcall3"=20
>>    type=3D"javax.sql.DataSource"=20
>>    url=3D"jdbc:postgresql://localhost/xcall3"=20
>>    username=3D"xcall3"
>>    validationQuery=3D"select version();"=20
> I think you should not place the ";" at the end of the statement.
>=20
>>    maxWait =3D "5000"
>>    />
>>=20
>>=20
>> here's what logs the database=20
>>=20
>> 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
>>=20
>> until i lose all my connections.
>>=20
>> here's what i got from the webapp log after that
>>=20
>> 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(PoolingDataSou=
rce.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(GenericObj=
ectPool.java:1104)
>>        at =
org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSou=
rce.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)
>>=20
> Do you get any abandoned log entries, if you add the configuration
>  ...
>  removeAbandoned=3D"true"
>  removeAbandonedTimeout=3D"60"
>=20
>  logAbandoned=3D"true"
>  ...
> to your resource?
>=20

adding now.

> Bye
> Felix
>>=20
>>=20
>>=20
>> On May 8, 2011, at 12:19 PM, Felix Schumacher wrote:
>>=20
>>> Am Sonntag, den 08.05.2011, 11:42 -0300 schrieb alexis:
>>>> Hello all, is there any way to recover lost connectios on a pool?
>>>>=20
>>>> 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.
>>>>=20
>>>> 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".
>>>=20
>>> 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.htm=
l
>>>=20
>>> Regards
>>> Felix
>>>>=20
>>>> thanks in advance.=20
>>>> =
---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>>>> For additional commands, e-mail: users-help@tomcat.apache.org
>>>>=20
>>>=20
>>>=20
>>>=20
>>> =
---------------------------------------------------------------------
>>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>>> For additional commands, e-mail: users-help@tomcat.apache.org
>>>=20
>>=20
>>=20
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>=20
>=20
>=20
>=20
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>=20


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