RE: GenericDataSource Reconnection

2001-05-03 Thread Alan Inser 

Andre,

Though PoolMan is now version 2.0.1, your code could be interesting. If you
find it again, thanks to put it on this list or send it directly to me at
[EMAIL PROTECTED]

On the other hand, I do not understand why you had to stuck the poolman
instance in the init method, maybe it was related to version 4.1. In the 2.0
Poolman user guide, there is a little section on Struts that tells how to
get the DataSource instance from a static method of PoolMan:
javax.sql.DataSource ds = PoolMan.findDataSource(dbname);

Thanks.
Adriano Labate

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 5:49 PM
To: [EMAIL PROTECTED]
Subject: RE: GenericDataSource Reconnection


i was using PoolMan at first.  i subclassed ActionServlet and just stuck my
poolman instance in one of the init methods.  it worked fine.  i'm just
using
the built in struts pool now because i'm lazy.

if i remember correctly, poolman didn't check each connection before handing
it
out either.  but what it did do was refresh the connections at a
configurable
interval.  i really wanted a check in there to make sure the connection
wasn't
stagnant, so i overrode the validate method in JDBCPool to call a class that
implemented a simple interface called ConnectionTester.   you would could
associate a ConnectionTester class with each db connection in the config
file,
and by default you would just use a dummy ConnectionTester that always
returned
true.  My OracleConnectionTester class just did a select sysdate from dual
and
returned false if a SQLException was thrown.

i believe i still have this code lying around if someone wants to take a
look.
it was in PoolMan1.4.1.

ab

  From:  [EMAIL PROTECTED]
 Date:   05/02/2001 09:44 AM
Please respond to struts-user


Hi,

The difficulty is to find a pretty efficient SQL statement which you want
to
execute each time before returning a connection.
 --- Matthias

As it was already suggested in this forum, the query select dummy from
dual could work well for a Oracle database. For other databases there
would
be specific query strings like the one for Oracle. It could be a property
of
the application.

Is anyone working with PoolMan connection pool instead of the one available
in Struts? Thanks.

Adriano Labate


-Original Message-
From: Matthias Bauer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 3:59 PM
To: [EMAIL PROTECTED]
Subject: Re: GenericDataSource Reconnection

Re: GenericDataSource Reconnection

2001-05-02 Thread Matthias Bauer 

I also had to solve this problem for one of our applications. I changed
GenericDataSource.java, so I am checking the isClosed method of a connection
before I am returning it to the caller of getConnection().

I know, that there is a statement in the Java Specification which says that the
isClosed() method does not need to return the current server state. It just
needs to report whether the close() method has been called before. This is
really unfortunate. But at least the MySQL driver and the PostgesSQL driver
(with a little patch) I am using return the proper server state. So I get along
with my struts patch for these drivers. You will just have to try out if it also
works in your case.

I have included the modified GenericDataSource.java. Try it out and please let
me know whether it works for you.

--- Matthias



Vivek Bhaskaran wrote:
 
 Hi People
 I was looking through the implementation of the javax.sql.DataSource
 (inside the org.apache.struts.util) package. One think I am facing with that
 mysql disconnects after a specified period of timeout. So the DB Pool gets
 corrupted.
 
 I was about to go ahead and add some code to the GenericDataSource to check
 for the conneciton before giving it to a consumer.
 
 Now my question is has this already been done? I did take a look at the
 WebCVS and could not find anything to that repect Is this something that
 needs to be done ?
 
 -vivek
 GenericDataSource.java
 S/MIME Cryptographic Signature

Re: GenericDataSource Reconnection

2001-05-02 Thread Donnie_Hall 



I headed down that path too, but then I noticed the GenericConnection class.

The GenericConnection class works with GenericDataSource to override the close
method.  When you call close, it cleans up the connection, set the close flag
to true and returns the connection to the free connection stack.  Therefore,
when you get an available connection from the stack, the IsClosed() method will
alwas return true.  My guess is that your code returns a new connection
everytime you make a database request.  Doing so you quickly head toward your
max connections.  At least that was my experience.

I'm using Oracle and the only way I know how to validate a connection is to
actually make a database call.  So what I was thinking in my own naive way was
to something like this in my code:

boolean sqlSuccess = false;
int sqlAttempts = 0;
while ((!sqlSuccess)  (sqlAttempts  2)) {
   try {
  conn = dataSource.getConnection();
  itemList = ItemLoad.Load(itemId,conn);
  sqlSuccess = true;
}
catch (SQLException e) {
   if (getErrorCode == 28) {// Oracle session killed error code
sqlAttempts++;
conn.remove() // Not a real method, but  would close and remove
from connection pool
   }
   else {
   throw SQLException;
}
 }
}


What do you think?  Has anyone written or planning to write such a method?

Thanks

Donnie Hall



|+-
||  Matthias.Bauer@livi|
||  nglogic.de |
|| |
||  05/02/2001 02:23 AM|
||  Please respond to  |
||  struts-user; Please|
||  respond to |
||  Matthias.Bauer |
|| |
|+-
  |
  ||
  |   To: [EMAIL PROTECTED]   |
  |   cc: (bcc: Donnie Hall/Enron Communications)  |
  |   Subject: Re: GenericDataSource Reconnection  |
  |



I also had to solve this problem for one of our applications. I changed
GenericDataSource.java, so I am checking the isClosed method of a connection
before I am returning it to the caller of getConnection().

I know, that there is a statement in the Java Specification which says that the
isClosed() method does not need to return the current server state. It just
needs to report whether the close() method has been called before. This is
really unfortunate. But at least the MySQL driver and the PostgesSQL driver
(with a little patch) I am using return the proper server state. So I get along
with my struts patch for these drivers. You will just have to try out if it also
works in your case.

I have included the modified GenericDataSource.java. Try it out and please let
me know whether it works for you.

--- Matthias



Vivek Bhaskaran wrote:

 Hi People
 I was looking through the implementation of the javax.sql.DataSource
 (inside the org.apache.struts.util) package. One think I am facing with that
 mysql disconnects after a specified period of timeout. So the DB Pool gets
 corrupted.

 I was about to go ahead and add some code to the GenericDataSource to check
 for the conneciton before giving it to a consumer.

 Now my question is has this already been done? I did take a look at the
 WebCVS and could not find anything to that repect Is this something that
 needs to be done ?

 -vivek(See attached file: GenericDataSource.java)


 GenericDataSource.java

Re: GenericDataSource Reconnection

2001-05-02 Thread Matthias Bauer 

 I headed down that path too, but then I noticed the GenericConnection class.
 
 The GenericConnection class works with GenericDataSource to override the close
 method.  When you call close, it cleans up the connection, set the close flag
 to true and returns the connection to the free connection stack.  Therefore,
 when you get an available connection from the stack, the IsClosed() method will
 alwas return true.  My guess is that your code returns a new connection
 everytime you make a database request.  Doing so you quickly head toward your
 max connections.  At least that was my experience.

No! Definitely not! I agree you have to be pretty careful what you do. You
certainly must not call the GenericConnection's isClosed() method. This would
result in what you described - a new connection would always be returned.

You have to call the isClosed() method of the GenericConnection's member conn,
which is of type Connection. Just look at my code, it should be pretty easy to
understand.

 
 I'm using Oracle and the only way I know how to validate a connection is to
 actually make a database call.  So what I was thinking in my own naive way was
 to something like this in my code:
 
 boolean sqlSuccess = false;
 int sqlAttempts = 0;
 while ((!sqlSuccess)  (sqlAttempts  2)) {
try {
   conn = dataSource.getConnection();
   itemList = ItemLoad.Load(itemId,conn);
   sqlSuccess = true;
 }
 catch (SQLException e) {
if (getErrorCode == 28) {// Oracle session killed error code
 sqlAttempts++;
 conn.remove() // Not a real method, but  would close and remove
 from connection pool
}
else {
throw SQLException;
 }
  }
 }
 
 What do you think?  Has anyone written or planning to write such a method?

The difficulty is to find a pretty efficient SQL statement which you want to
execute each time before returning a connection. The clean approach is to let
the driver find out (with any proprietary means that can be used for the
specific database) whether the connection is valid or not. But as I pointed out
earlier, the Java Specification does not request the driver to do that...
unfortunate, isn't it!

 
 Thanks
 
 Donnie Hall

--- Matthias
 S/MIME Cryptographic Signature

RE: GenericDataSource Reconnection

2001-05-02 Thread Alan Inser 

Hi,

The difficulty is to find a pretty efficient SQL statement which you want
to
execute each time before returning a connection. 
 --- Matthias

As it was already suggested in this forum, the query select dummy from
dual could work well for a Oracle database. For other databases there would
be specific query strings like the one for Oracle. It could be a property of
the application.

Is anyone working with PoolMan connection pool instead of the one available
in Struts? Thanks.

Adriano Labate


-Original Message-
From: Matthias Bauer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 3:59 PM
To: [EMAIL PROTECTED]
Subject: Re: GenericDataSource Reconnection

RE: GenericDataSource Reconnection

2001-05-02 Thread Andre_Beskrowni 



i was using PoolMan at first.  i subclassed ActionServlet and just stuck my
poolman instance in one of the init methods.  it worked fine.  i'm just using
the built in struts pool now because i'm lazy.

if i remember correctly, poolman didn't check each connection before handing it
out either.  but what it did do was refresh the connections at a configurable
interval.  i really wanted a check in there to make sure the connection wasn't
stagnant, so i overrode the validate method in JDBCPool to call a class that
implemented a simple interface called ConnectionTester.   you would could
associate a ConnectionTester class with each db connection in the config file,
and by default you would just use a dummy ConnectionTester that always returned
true.  My OracleConnectionTester class just did a select sysdate from dual and
returned false if a SQLException was thrown.

i believe i still have this code lying around if someone wants to take a look.
it was in PoolMan1.4.1.

ab

  From:  [EMAIL PROTECTED]
 Date:   05/02/2001 09:44 AM
Please respond to struts-user


Hi,

The difficulty is to find a pretty efficient SQL statement which you want
to
execute each time before returning a connection.
 --- Matthias

As it was already suggested in this forum, the query select dummy from
dual could work well for a Oracle database. For other databases there would
be specific query strings like the one for Oracle. It could be a property of
the application.

Is anyone working with PoolMan connection pool instead of the one available
in Struts? Thanks.

Adriano Labate


-Original Message-
From: Matthias Bauer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 3:59 PM
To: [EMAIL PROTECTED]
Subject: Re: GenericDataSource Reconnection

RE: GenericDataSource Reconnection

2001-05-02 Thread Anthony Martin 

Both methods seem to only check the current connection and move on to the
next one.  What if all of the connections are closed?


Anthony

-Original Message-
From: Alan Inser [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 7:44 AM
To: '[EMAIL PROTECTED]'
Subject: RE: GenericDataSource Reconnection


Hi,

The difficulty is to find a pretty efficient SQL statement which you want
to
execute each time before returning a connection. 
 --- Matthias

As it was already suggested in this forum, the query select dummy from
dual could work well for a Oracle database. For other databases there would
be specific query strings like the one for Oracle. It could be a property of
the application.

Is anyone working with PoolMan connection pool instead of the one available
in Struts? Thanks.

Adriano Labate


-Original Message-
From: Matthias Bauer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 3:59 PM
To: [EMAIL PROTECTED]
Subject: Re: GenericDataSource Reconnection

Re: GenericDataSource Reconnection

2001-05-02 Thread Donnie_Hall 



Sorry, I missed the member call. Unfortunately, I tried it and oracle does not
seem to check the connection status.  It always returns true.

The question becomes:  Do I, (A) test every connection when I get it.  Or, (B)
wait until an exception occurs and then either destory the connection or reset
it.  I lean toward (B), only to avoid an extra loop everytime I call the
database.

Anyone have suggestions or solutions?

Thanks,
Donnie Hall



|+-
||  Matthias.Bauer@livi|
||  nglogic.de |
|| |
||  05/02/2001 08:58 AM|
||  Please respond to  |
||  struts-user; Please|
||  respond to |
||  Matthias.Bauer |
|| |
|+-
  |
  ||
  |   To: [EMAIL PROTECTED]   |
  |   cc: (bcc: Donnie Hall/Enron Communications)  |
  |   Subject: Re: GenericDataSource Reconnection  |
  |



 I headed down that path too, but then I noticed the GenericConnection class.

 The GenericConnection class works with GenericDataSource to override the close
 method.  When you call close, it cleans up the connection, set the close
flag
 to true and returns the connection to the free connection stack.  Therefore,
 when you get an available connection from the stack, the IsClosed() method
will
 alwas return true.  My guess is that your code returns a new connection
 everytime you make a database request.  Doing so you quickly head toward your
 max connections.  At least that was my experience.

No! Definitely not! I agree you have to be pretty careful what you do. You
certainly must not call the GenericConnection's isClosed() method. This would
result in what you described - a new connection would always be returned.

You have to call the isClosed() method of the GenericConnection's member conn,
which is of type Connection. Just look at my code, it should be pretty easy to
understand.


 I'm using Oracle and the only way I know how to validate a connection is to
 actually make a database call.  So what I was thinking in my own naive way was
 to something like this in my code:

 boolean sqlSuccess = false;
 int sqlAttempts = 0;
 while ((!sqlSuccess)  (sqlAttempts  2)) {
try {
   conn = dataSource.getConnection();
   itemList = ItemLoad.Load(itemId,conn);
   sqlSuccess = true;
 }
 catch (SQLException e) {
if (getErrorCode == 28) {// Oracle session killed error code
 sqlAttempts++;
 conn.remove() // Not a real method, but  would close and
remove
 from connection pool
}
else {
throw SQLException;
 }
  }
 }

 What do you think?  Has anyone written or planning to write such a method?

The difficulty is to find a pretty efficient SQL statement which you want to
execute each time before returning a connection. The clean approach is to let
the driver find out (with any proprietary means that can be used for the
specific database) whether the connection is valid or not. But as I pointed out
earlier, the Java Specification does not request the driver to do that...
unfortunate, isn't it!


 Thanks

 Donnie Hall

--- Matthias

RE: GenericDataSource Reconnection

2001-05-01 Thread Anthony Martin 

I am facing the same problem.  I use Interbase, and the connection goes down
from time to time for maintenance.  I was trying to build a test case to
present because I was worried I was the only one seeing this problem, in
case it was just my JDBC Driver not reporting it.

Restarting Tomcat is the only workaround I have right now.  This is not my
favorite way to handle it, obviously.  I too would be interested in any
other methods.


Anthony

-Original Message-
From: Vivek Bhaskaran [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 4:18 PM
To: [EMAIL PROTECTED]
Subject: GenericDataSource Reconnection


Hi People
I was looking through the implementation of the javax.sql.DataSource
(inside the org.apache.struts.util) package. One think I am facing with that
mysql disconnects after a specified period of timeout. So the DB Pool gets
corrupted.

I was about to go ahead and add some code to the GenericDataSource to check
for the conneciton before giving it to a consumer.

Now my question is has this already been done? I did take a look at the
WebCVS and could not find anything to that repect Is this something that
needs to be done ?

-vivek