http://www.perlmonks.org?node_id=293083

nmerriweather has asked for the wisdom of the Perl Monks concerning the following question:

i can't figure out how its done i'm migrating a bunch of old procedural code into a new object oriented framework i'm kind of liking it too. except i can't figure out, for the life of my, how to have the new packages/objects/classes/whatever i create use a unified database connection. i'd rather not have a connection in each instance. i'd like to *somehow* address a global connection is that at all possible? or am i going to have to make a new package and do all my database stuff in there?

Replies are listed 'Best First'.
Re: using multiple packages w/1 database connection
by BUU (Prior) on Sep 22, 2003 at 05:34 UTC
    Package DBH; my $dbh=DBI->connect('DBD:foo:baz',bar,qux); sub get_dbh() { return $dbh; } package main; my $dbh = DBH->get_dbh(); $dbh->prepare();

      Pet peeve. If you don't religiously disconnect from the DB what CAN happen is that when the script completes $dbh gets undeffed BUT the underlying connection MAY potentially remain until it times out. Although the DBI DESTROY method should be called on script exit and this should call disconnect we have practical experince that this is not always the case. Also if you are using transactions the Commit/Rollback behaviour on disconnect is not defined so you might wish to add a commit to the end block as well to finalise everything.

      On MySQL you get a default limit of 100 max_connections and a timeout of 8 hours. Result: Run a script that fails to disconnect more than 100 times in 8 hours and you will potentially use up all your available connections. At this point the DB connect will error out every time until an old connection times out. I think is is failry sound practice to code the connect like this, with the disconnect in an END block immediately below. It makes sure it happens.

      Package DBH; my $dbh=DBI->connect('DBD:foo:baz',bar,qux); END{ $dbh->disconnect if $dbh };

      cheers

      tachyon

      s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

        When the program exits all file handles closed by the OS. That should close the socket for the database connection. The Perl code that cleans up might not get run. I guess some database servers could sit there waiting until they get an explicit close command and not notice that the client disappeared.

        Recent versions of DBI include an END block that does a disconnect_all and closes all database connections. Also, the DESTROY is supposed to call rollback before the disconnect. Doing a rollback in the END block is the safest thing otherwise some unknown, possibly failed, transaction could be committed.

Re: using multiple packages w/1 database connection
by rnahi (Curate) on Sep 22, 2003 at 07:34 UTC