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

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

Hi,
I have a question regarding how DB connections work within a single process perl script. Hope someone doesn't mind giving me a few pointers.

If I was to have a sub routine that setups a connection and returns a connection handler variable (or perhaps a reference), such as the code shown below. If I then call this sub everytime I need to make a query (my $dbh = &dbConnect), am I right in thinking that a new connection would NOT be openend every time but that DBI would use an existing connection if one existed and was not already in use; assuming that I hadn't yet called disconnect()

My second question is: Is the connection opened on the call to connect or is it delayed until the first query is prepared?

Regards to all,
Cyril

sub one { my $dbh = &dbConnect; # query continues here } sub two { my $dbh = &dbConnect; # query continues here } sub dbConnect { my $dsn = "DBI:mysql:database:localhost"; my $db_user_name = 'username'; my $db_password = 'password'; my $dbh; $dbh = DBI->connect ($dsn, $db_user_name, $db_password, { RaiseError => 1 } ) or die("cannot connect to DB: ".DBI::errstr. +"\n",$dbh); return $dbh; }

Replies are listed 'Best First'.
Re: DBI / Mysql connections
by Mutant (Priest) on Dec 03, 2004 at 11:12 UTC
    Each call to dbConnect() will open a new connection to the database. One way of connecting only once is to declare the $dbh var as global to your package, and call dbConnect only once:
    my $dbh = dbConnect(); sub dbConnect { my $dsn = "DBI:mysql:database:localhost"; my $db_user_name = 'username'; my $db_password = 'password'; $dbh = DBI->connect ($dsn, $db_user_name, $db_password, {RaiseError => 1}) || die("cannot connect to DB: ".DBI::errstr."\n",$dbh); } sub foo { # Can still see $dbh in here my $sth = $dbh->prepare(...); }

    Of course, this means you're connecting to the DB every time the script's called (which might not be necessary), and you won't be able to access $dbh from outside of this package (probably a good thing though, I prefer to confine all DB related stuff to one package, or at least one hierarchy of classes. The next step after this is Class::DBI).

      If you want to avoid connecting until you need a connection, you can do this:

      my $dbh; sub dbConnect { return if defined $dbh; # exit if we are already connected. my $dsn = "DBI:mysql:database:localhost"; my $db_user_name = 'username'; my $db_password = 'password'; $dbh = DBI->connect ($dsn, $db_user_name, $db_password, {RaiseError => 1}) || die("cannot connect to DB: ".DBI::errstr."\n",$dbh); } sub foo { # Can still see $dbh in here dbConnect(); my $sth = $dbh->prepare(...); }

      Since dbConnect() is called each time we want a connection (and not until then), and since it returns if we are already connected, $dbh becomes a persistent connection that is made the first time it is needed.

      This method also opens the door for disconnecting from the DB and reconnecting later, if that should ever become useful. (Admittedly, I can't think of when that might be useful, but you never know. ;-P)

      Then again, you might just use connect_cached. Though that does something ever-so-slightly different.

      radiantmatrix
      require General::Disclaimer;
      s//2fde04abe76c036c9074586c1/; while(m/(.)/g){print substr(' ,JPacehklnorstu',hex($1),1)}

Re: DBI / Mysql connections
by perrin (Chancellor) on Dec 03, 2004 at 11:54 UTC
    If you want to reuse connections, you should be calling DBI->connect_cached() instead of DBI->connect().
Re: DBI / Mysql connections
by monsieur_champs (Curate) on Dec 03, 2004 at 11:24 UTC

    Dear cyril:
    Sorry, but I think that you're wrong in assuming that the same connection will be reused every time you call DBI::Connect() with the same parameters. My experience says that this varies from DBD driver to DBD driver. Guess you shall use a cacheable implementation. Maybe Ima::DBI would be more appropriated.

    About your second question, I guess the first contact is at the time you call DBI::Connect(): your password needs verification so the DBI/DBD engine makes sure you have authorization to connect to the specified database or if there is an error condition to be reported. Of course, this behavior depends on what DBD driver you're using.

Re: DBI / Mysql connections
by pingo (Hermit) on Dec 03, 2004 at 11:05 UTC
    I would assume you would get a new connection every time, and that the connection is opened immediately, but if I am wrong, I think a certain saint may correct me. :-)
Re: DBI / Mysql connections
by cyril (Acolyte) on Dec 03, 2004 at 14:07 UTC

    Thanks for all the help.

    Most of my experience is with PHP, and the default behaviour with Mysql under these circumstances is to reuse an existing process whenever possible. Perhaps the mysql driver with Perl behaves similarly. I guess the best thing for me to do is experiment further and work out what is going on behind the scenes. I'll look into the options mentioned and see what what suits.

    regards

    Cyril

      You can't really compare PHP with a Perl script.

      PHP is usually run via a webserver and more precise via a module of the webserver, which will re-use certains processes/threads.
      And only when something is re-used it can use an already made connection (so a script that is run via CGI can't re-use a connection made the previous time the page was loaded), using two connections from inside the same script would be possible but that isn't the default behaviour (or atleast not for a normal Perl script).

      If you would run perl via the apache module mod_perl and you have loaded Apache::DBI then the default would be to use the connect method of Apache::DBI which will try to use a previous created connection

      If you are using Perl in a web application under mod_perl, then the answer is different: in that case, due to the beauty of a module called Apache::DBI, the connection will be cached automatically for you, even if you do not explicitly call connect_cached. If you look under the documentation for Apache::DBI you'll see the full explanation.