in reply to multiple db connects

You can do this in many ways. One obvious way is to just wrap this in a function: 
    sub database_handle {
        my $dbh = DBI->connect("DBI:Sybase:CRAP", "sa", "");
        die "connect failed: $DBI::errstr" unless $dbh;
        $dbh;
    }

[download]
Then get a new database handle using 
    my $dbh = database_handle();

[download]
Is this what you wanted? Or did you want to save the handle somehow, and return the already-open connection if it's open already? You could do that like this: 
    {
        my $dbh;
        sub database_handle {
            return $dbh if defined $dbh;
            $dbh = DBI->connect("DBI:Sybase:CRAP", "sa", "");
            die "connect failed: $DBI::errstr" unless $dbh;
            $dbh;
        }
    }

[download]
And you'd get access to the handle the same way, by calling database_handle. This will return an already-open connection.

Or you could get trickier and wrap this in an object wrapper around DBI. This is something I like to do, because I like to write code that works on different databases, so I provide one main class that wraps around DBI, then subclass that to provide database-specific functionality (stuff that differs between DBD drivers, etc.).

Here's a simple class: 

    package My::DB;
    use strict;

    sub new {
        my $class = shift;
        $dbh = DBI->connect("DBI:Sybase:CRAP", "sa", "");
        die "connect failed: $DBI::errstr" unless $dbh;
        bless { handle => $dbh }, $class;
    }

    sub handle { $_[0]->{handle} }

[download]
You could use this like this: 
    my $dbh = My::DB->new->handle;

[download]
This may seem relatively useless (why I am returning an object containing only an open database handle? why not just return the handle itself?), but the way I use this is, I don't actually use any of the DBI methods directly in my main code. I have wrappers around DBI methods that do a bit more, like do some automatic exception handling, bind variable processing, etc.

For example, I have an execute method that does this: 

    sub _execute {
        my $self = shift;
        my($sql, $place, $finish) = @_;

        my($sth, $rv);
        eval {
            $sth = $self->prepare($sql);
            $rv = $sth->execute(ref $place eq "ARRAY" ? @$place : ());
            $sth->finish if $finish;
        };
        if ($@) {
            My::Exception::DB->throw(sql => $sql, error => $@);
        }
        return $finish ? $rv : $sth;
    }

[download]
And my prepare method uses prepare_cached, instead of just plain prepare. And so on.

Replies are listed 'Best First'.
Re: Re: multiple db connects
by ichimunki (Priest) on Dec 21, 2000 at 19:01 UTC
    My first thought is that unless you are connecting to more than one DB, you don't need a routine. You leave the connection open until you are done. So any routine should handle some sort of argument for which DB to connect to.

    I have a question of style/usage regarding the die clause in the subs... Would we still want to call such a function thusly: 
    my $dbh->db_handle($db_name) || die "SNAFU getting to $db_name: $!";
or
my $dbh->db_handle($db_name) || &explain_to_user_that_db_unavailable( 
+$db_name );

    [download]
    Or something like that? Especially if this is a CGI wouldn't we want to use a function to tell the user to quit hitting refresh until the server is fixed? The die clause still returns a false value for the sub call, right? But this could cause errors if not handled in the main clause, right?
[reply]
[d/l]
      Sure, you could do whatever you wanted, really. Personally I like to die when something seems like an obviously fatal error. That doesn't mean that your *program* will die, because you can catch the die in an eval block. Which is what I do.

      You also said: 

          > The die clause still returns
    > a false value for the sub call, right?

      [download]
      Well, no; the die just *dies*. Your sub won't return at all. Your program will die, unless you have an eval block, in which case the die will shoot you out to the closest eval block, and will populate $@ with the error message. And then you said: 
          > But this could cause
    > errors if not handled in the main clause, right?

      [download]
      Certainly. That's why the errors should be handled. :)

      Instead of dying, then, you could have your sub return a false value. That would work. Then you could do your checking out in the caller. Some people find that preferable, I'm sure, and that's fine; my preference is to throw exceptions as soon as something bad happens. Then I catch them.

[reply]
[d/l]
[select]
        So that's the behavior I'm trying to understand about die. If it is going to cause the program to bomb, then it seems like (especially for CGI) a much better idea to me (both in terms of user-friendly solutions and in terms of code re-usability) to build the function in such a way that it returns a false value if it fails. That way the function doesn't kill the whole process (i.e. HTTP result code 500) and it can be used in statements like 
        $foo = biff($bap) || 'Bar'; 

or

if ($dbh = handle($db)) {
    do_something($dbh);
} else {
    dont();
}

        [download]
        (for instance).
[reply]
[d/l]

In Section Seekers of Perl Wisdom