Welcome to the world of wan programming. It *can* be done, but you have to do it carefully. I think the problem you are running into, is that the ODBC driver might not have a 'timeout' setting enabled. I know that when I wrote a comm layer using a database and a jdbc driver, executing a 'select' would not timeout, even if the tcp connection was broken. I had to manually specify (in jdbc's dsn string) two timeout values, one for the connect, and one for the time that it took for the connection to return results. Additionally, even when the client timed out right and disconnected the connection, the *server* still thought it had a connection. This was a problem because we quickly reached a 'connection limit' for the server. So, I set the timeout for the jdbc connection && the database connection to the same value, and that (seemed) to work ok....

If you want to test this in the future, consider setting up an openbsd / freebsd box with ipfw on it. You can throttle the network connection, introduce random packet drops, etc. You can then automate your testing by using Net::Telnet or Net::SSH to send commands to ipfw, and Cluster::Run (not yet released, working on it at home) or your favourite Distributed:: module to stress test the server.

Good luck! Must get java filth off my hands... unclean! unclean!

Using MySQL we never use ODBC to connect - even on our LAN, of course using an MS database we don't have much choice, so we don't use MS.

We got around it in some cases by actually wrtiing a SOAP server in Perl on the DB machine and accessing that from the client side - look up SOAP::Lite

As someone else said - 'Good Luck'

jdtoronto

my ($rc) = $sql_obj->sql($query);
return ($sql_obj, undef) unless $rc;
return ($sql_obj, "".Win32::ODBC::Error())  unless (Win32::ODBC::Error
+() =~ /Communication link failure/);
my($sql_obj) = $self->connect($server, $user, $password);
return (undef, "Cannot reconnect") unless ($new_sql_obj);
$rc=$sql_obj->sql($query);
    
return ($sql_obj, "".$rc) if ($rc);
return ($sql_obj, undef);
[download]

Liz

Followup

I've been doing some homework, as a result of the replies, thank you all.

However first I would like to make a couple of clarifications.

1. Point 10 of the scenario should actually read "Works again"
2. I think I need to emphasize that the major problem is not the broken link, but the fact that program invocation # > 1 hangs in the connect method ( see below for (too much) detail.

What I have done is to enable tracing in DBI, settings for greatest detail.

zakzebrowski
Yes I've checked for timeouts and it's set for 15 secs. Which seem to be what I see for the first program invocation.

jdtoronto
Interesting idea, I would like it alot if you could elaborate on the pros of the SOAP solution compared to ODBC in this regard.

The advantage of SOAP is that the SOAP server is on the database server. The ODBC connection is direct and does not have to worry about network issues. The disadvantage is that you would need to rewrite your application to use SOAP and move the database calls to the application server.

Another option is DBI::Proxy. It is a pure Perl DBI proxy to remote server with its own DBI connection to the database. The big advantage is over SOAP is that the interface is DBI so you wouldn't have to rewrit your application.

Well, if you know it *should* exit, but doesn't... grab the pid, use win32::Process and win32::process::info , when you launch the process, grab a pid and start a 'watcher' process. If timeout > run time, do a Win32::Process::KillProcess on that process... As you suggest, it may be an odbc bug...
Cheers & Good luck.

----
Zak