dr.jekyllandme has asked for the wisdom of the Perl Monks concerning the following question:

I have a Perl script that connects to a sqlite3 databaase and does a query. Example: 
    #!/usr/software/bin/perl5.8.8

    use strict;
    use warnings;

    my $db = 'somedb';
    my $dbh = DBI->connect("dbi:SQLite:dbname=$db", "", "", {RaiseErro
+r => 1}) or die $DBI::errstr;
    # doing something here with $dbh...
    $dbh->disconnect;

[download]
The above code works, but I would like to add a condition when the DBI->connect fails. Currently the script dies, but instead I would like to have an if statement that does something alternative if the db I am connecting to is not available. Then exits. I was thinking of this: 
    #!/usr/software/bin/perl5.8.8

    use strict;
    use warnings;

    my $db = 'somedb';
    my $dbh = DBI->connect("dbi:SQLite:dbname=$db", "", "", {RaiseErro
+r => 1});
    
    # Is this the right way to check if DBI connected to the database?
    unless ($dbh) {
        print "Unable to connect to $db.\n";
        # doing something else here...
    }
    # doing something here with $dbh...
    $dbh->disconnect;

[download]
Is this right? Thank you for the help.

Replies are listed 'Best First'.
Re: What to do when DBI connect fails
by davido (Cardinal) on Oct 07, 2013 at 23:00 UTC

    If your database handle is invalid you cannot call disconnect on it. Also, RaiseError elevates a failure to connect to an exception. You'll have to trap that in an eval, or not use RaiseError.


    Dave

[reply]
[d/l]
[select]
Re: What to do when DBI connect fails
by Marshall (Canon) on Oct 07, 2013 at 23:14 UTC
    Your idea is correct in that $dbh will be defined if the connect() succeeds.

    As a fundamental question: What do you intend to do if the connect fails?

    I would probably loop back to the original connect() statement with an escape value of some max number of "try this other thing" statements or some other limiting factor.

    With SQLite, basically it comes down to whether you have permission to access the file or not. There are no user name or password issues like with a normal server and that is a cool thing.

    I would be thinking along the lines of checking if the file and path are valid and then do whatever you have to do if that is not right. What I don't know... After that let the program "blow up" if the connect fails (and that would mean corrupted .db file). Anyway what you intend to do if the connect fails is relevant.

[reply]
         #
   # -----------------------------------------------
   # open the database handle if possible, if not return proper error 
+msgs
   # ( $ret , $msg , $dbh ) = $self->doConnectToDb ( $db ) ;
   # -----------------------------------------------
   sub doConnectToDb {

      my $self = shift ;
      my $db   = shift || 'non_accessible_db'  ;

      my $ret  = 400 ;
      my $msg = 'cannot connect to the "' . $db . '" database: ';
      my $dbh  = undef ;

      $dbh = DBI->connect("dbi:Pg:dbname=$db", "", "" , {
                 'RaiseError'          => 0 # otherwise it dies !!!
               , 'ShowErrorStatement'  => 1
               , 'PrintError'          => 1
               , 'AutoCommit'          => 1
               , 'pg_utf8_strings'     => 1
      }) ;

      if ( defined $dbh  ) {
         $ret = 0 ; $msg = "" ;
      } else {
         $msg .= DBI->errstr ;
      }

      return ( $ret , $msg , $dbh ) ;
   }

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom