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
>
>
> public void insertVDNInfo(VDNInfo vdns) throws SQLException {
Why do you want to throw a SQLException?
>
> Calendar c = Calendar.getInstance();
>
> Iterator<VDNInfo.VDN> it = vdns.getVdns().iterator();
Seems like you can use
for (VDNInfo.VND v: vdns.getVdns()) {
...
}
instead of x=y.iterator() combined with while(x.hasNext())
{ v=x.next(); }
> Connection c1 = null;
> Statement s1 = null;
>
> try {
> while (it.hasNext()) {
> c1 = getConnection();
Your getConnection method can return null, so you should guard against
it.
> 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()) + "')";
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.
>
> s1.execute(query);
> }
> } finally {
> if (s1 != null) {
> s1.close();
> }
if s1.close() throws an error you will not close c1, so you have to
catch that exception:
if (s1!=null) { try { s1.close } catch (SQLException e) { // Ignore
or event better log
};
> s1 = null;
> if (c1 != null) {
> c1.close();
catch the possible Exception
if (c1!=null) { try {c1.close();} catch (SQLException e) { // Ignore
or log
};
> }
> 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);
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?
> }
>
>
>
> 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.
That difference can be essential, as explained above.
>
>
> 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();"
I think you should not place the ";" at the end of the statement.
> 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)
>
Do you get any abandoned log entries, if you add the configuration
...
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
...
to your resource?
Bye
Felix
>
>
>
> 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]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
