simple minded DBI timeout

[Discipulus]

Hello monks and nuns!

I've finally build up my Dancer2 web application, where among other tasks I query a mssql db. The query to the db is in a long chain of ajax requests and, unfortunately, it happens sometimes that such damned db slows down all the page because other ajax requests are queued and wait for the DBI connection to fail.

Infact it happens that such db is offline or it's so busy to not return data in a decent time. Smashing the dba is not a legal option in this case..

Even if i prefer to have something to show from such db I dont want to wait so long to draw my page that is, thanks to Perl, very responsive in each part, so if db is not fast I'd prefer to skip and going on.

This is called timeout. DBI has not a timeout option and unluckily on the unfortunate platform I work on, SIG{ALRM} is known to not work (in DBI docs is explained how to setup a timeout using it).

So I worked it around with a preliminar connection check using IO::Socket::INET with a very short timeout:

use strict;
use warnings;

use IO::Socket::INET;
use DBI;

my $db_ip = '10.10.10.10'; 
my $db_port = 1433;
my $db_timeout = 2;
my $db_conn_error;
my $sock = IO::Socket::INET->new(
                       PeerPort  => $db_port,
                       PeerAddr  => $db_ip,
                       Proto     => 'tcp',    
                       Timeout   => $db_timeout) 
                   or $db_conn_error = "impossible to comunicate with 
+the DB($@)";
                   
if ($sock and $sock->connected){
    shutdown($sock, 2);
    my $connect_string = "driver={SQL Server};Server=10.10.10.10;Datab
+ase=NAME;UID=USR;PWD=PWD";
    my $db_error;
    my $dbh;
    if ( $dbh = DBI ->connect ( "DBI:ODBC:host=localhost;$connect_stri
+ng" )){
            my $sth = $dbh->prepare('SELECT * FROM TABLE'); # not the 
+real query..
            my $results = $dbh->selectall_arrayref($sth);
                        # OK code here...            
    }
    else { print "error connecting to the db NAME"; }
}
else{ print  $db_conn_error,"\n"; }

Anything better?

L*

[erix]

That 'DBI:ODBC:' in the connectstring means you're using DBD::ODBC. I see there is a odbc_query_timeout in DBD::ODBC. Does it not work in your case?

http://search.cpan.org/~mjevans/DBD-ODBC-1.56/ODBC.pm#odbc_query_timeout

(and as it says there, /t/20SqlServer.t has an example)

I don't know if it's better than what you have but it sure looks simpler :)