Re: DBI / Mysql connections

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(...);
}
[download]

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).

Comment on Re: DBI / Mysql connections Download Code

Replies are listed 'Best First'.
Re^2: DBI / Mysql connections by radiantmatrix (Parson) on Dec 03, 2004 at 15:48 UTC
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(...); }` [download] 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)}`	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: DBI / Mysql connections
by radiantmatrix (Parson) on Dec 03, 2004 at 15:48 UTC

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(...);
}
[download]

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)}

[reply]
[d/l]