I do this one a little differently than most people: 
{
  my $dbh;
  sub get_db_prod {
    unless (defined ($dbh)) {
      $dbh =  DBI->connect(
        ('DBI:Sybase:' . prod_server),
        prod_user, prod_pass,
        {PrintError => 0, AutoCommit => 0}
      ) or die $DBI::errstr;
    }
    return $dbh;
  }
}

[download]
Previously I had already done a "use constant" to create constants for prod_server, prod_user and prod_pass. I won't paste that code here. (VBG) But the key trick is that you can ask for the connection as many times as you want, from wherever you want. The first time you need it it will spring into existence and from then on you get the memoized instance. Bye bye global variable. :-)

Note that I use a wrapper for errors, so I set PrintError to 0, you may want to change that default. Look at the DBI docs to decide what defaults make sense to you.

A big win with the above code. This is really, really nice in programs which may or may not need a database connection, but if they do will need it in several places. Just place calls to the above function wherever you need it and don't worry about any custom logic for whether or not to initiate a connection. I have some scripts that this idea has really simplified. Depending on what you are trying to do, YMMV.

And to answer your final question, building and destroying connections is very expensive, maintaining them is pretty cheap. So you want to open a connection and not close it again if you can at all help it.

In reply to RE: passing DBI database handles to subroutines by tilly
in thread passing DBI database handles to subroutines by moo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.