Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Trap DB Connect

by bitman (Beadle)
on Jul 30, 2007 at 11:09 UTC ( #629525=perlquestion: print w/ replies, xml ) Need Help??
bitman has asked for the wisdom of the Perl Monks concerning the following question:

I am having trouble connecting to our Oracle DB. The command $dbh = DBI->connect("DBI:Oracle:".... ) or die ..... is never returning, I suspect a network issue, I would like to timeout this statement after about 15 secs. Is this possible?

Replies are listed 'Best First'.
Re: Trap DB Connect
by rpanman (Scribe) on Jul 30, 2007 at 11:25 UTC
    I had a quick look at the DBI documentation on CPAN here and searched on timeout, finding the following text:
    With Oracle for instance (DBD::Oracle), if the system which hosts the +database is down the DBI->connect() call will hang for several minute +s before returning an error.
    I guess this is happening in your case.

    There is a suggested solution in the documentation.
Re: Trap DB Connect
by grinder (Bishop) on Jul 30, 2007 at 13:00 UTC
    I would like to timeout this statement after about 15 secs

    You want to set an alarm for 15 seconds, and attempt the connection in an eval block.

    my $db; eval { local $SIG{ALRM} = sub { die "connect timeout\n" }; alarm 15; $db = DBI->connect("DBI:Oracle:".... ) or die $DBI::errstr; alarm 0; }; if ($@) { # timed out die unless $@ eq "connect timeout\n"; } elsif (!$db) { # didn't connect die "no database connection\n"; } # $db is ok here

    If you're running on Win32, I believe you're out of luck, because alarm isn't implemented. Or it could be that it is now implemented in 5.8.8 and/or there's another technique that taps Win32::API to achieve the same functionality.

    update: it looks like alarm is implemented in Active Perl build 819 (version 5.8.8) at least (don't know which exact version introduced this).

    H:\>perl -wle "eval {$SIG{ALRM}=sub{die qq{ook\n}}; alarm(3); 1 while +1; alarm(0)}; print $@" ook

    • another intruder with the mooring in the heart of the Perl

Re: Trap DB Connect
by mjscott2702 (Pilgrim) on Jul 30, 2007 at 13:19 UTC
    What type of system is your script running on - *nix, Win32, Win32/Cygwin etc? At the sacrifice of portability, that would tell you if the alarm approach will work on your system.

    One thing you could do is try to telnet to the port on the remote machine - you won't get a sensible response, but if you get a connection refused or other error, that means network connectivity or other more fundamental issues are your problem.
    e.g. telnet oracle_host 3306
    would try to telnet to machine oracle_host on port 3306 (which happens to be the default port for MySql - use the Oracle port number that you have configured!)
      It's Solaris, 8 I think. I just need the darn thing to fail again so I can verify the the ALRM signal will work for me.

      Thank you very much for all your help.

        Couldn't you just change the URL or unplug the network temporarily?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://629525]
Approved by jettero
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (6)
As of 2016-06-25 10:57 GMT
Find Nodes?
    Voting Booth?
    My preferred method of making French fries (chips) is in a ...

    Results (325 votes). Check out past polls.