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

Using DBI and DBD::MySQL i bump into a little problem when i try to create a subroutine that handles all the database connections.
A normal piece of database code could look like this: 
$dbh = DBI->connect("DBI:mysql:database=db;host=host", $user, $pass);
$dbquery = 'select * from table';
$sth = $dbh->prepare($dbquery);
$sth->execute();
while (my $ref = $sth->fetchrow_hashref())
{ print $ref->{'id'};
}
$sth->finish();
$dbh->disconnect();

[download]
But if i place this in a subroutine, the sub must return the object $sth or a copy of it. I am afraid that returning $sth after the finish and disconnect will not work. Making a reference to $sth won't do much good either since it's contents will probably be gone after the finish().

How can i make this work in a subroutine? </code>

Replies are listed 'Best First'.
Re: Copy an object
by perlplexer (Hermit) on Apr 25, 2002 at 20:52 UTC
    Unless this is something you do only once in your program, you shouldn't reconnect to the database every time you need to do a select.
    Instead, you should connect once and then use the database handle throughout your program; e.g., you would pass it to all subroutines that work with the DB. 
    # example
my $dbh = DBI->connect( ... );

#...
my @row = doSelect($dbh, 'foobar');
#...

sub doSelect{
  my ($dbh, $table) = @_;
  my $sth = $dbh->prepare("SELECT * FROM $table LIMIT 1");
  $sth->execute();
  return $sth->fetchrow_array();
}

    [download]
    As VSarkiss already mentioned, it makes little sense to pass the statement handle ($sth) back from the subroutine.
    Pass the result set instead. Of course, you need to make sure that your query won't return too many rows...

    --perlplexer
[reply]
[d/l]
      Quick note: 
      # connect
my $dbh = DBI->connect_cached(...)

# then add
sub DESTROY {
  if (defined $dbh) {
    $dbh->disconnect();
  }
}

      [download]
      This works for me and lets me forget about the disconnect.

      The connect_cached only reconnects if cached connection doesn't exist. So you can safely put a connect_cached before a query if you're unsure whether you're connected without the performance hit of connecting/disconnecting all the time. docs here

      Putting the disconnect in the DESTROY sub cleanly disconnects when the script exits - assuming, of course, that it will still be in scope :)

      AFAIK! No-one has told me otherwise, anyway :)

      .02

      cLive ;-)

[reply]
[d/l]
Re: Copy an object
by VSarkiss (Monsignor) on Apr 25, 2002 at 20:23 UTC

    You're right, returning $sth after you've called finish will not do anything. But if all you need is the data, you could return the hashref that comes back from the query.

[reply]
[d/l]
[select]
Re: Copy an object
by Super Monkey (Beadle) on Apr 25, 2002 at 22:05 UTC
    It sounds like you should create a couple of subroutines. One for the db connection and one for disconnect (or not). You can return the db handle from the connect sub and use it the same way you would any other. Then call the disconnect when you are done. If you wanted to get really slick, keep the db handle inside your module (ie. don't return it from the connect sub) and provide other subroutines to insert, select, delete, etc... Of course, it depends on how much power you want to allow the users of your module.
[reply]
Re: Copy an object
by cLive ;-) (Prior) on Apr 26, 2002 at 07:25 UTC
    A bit more of an expansion on my comment above... 
    # untested! just off top of head

my $db   = 'DBI:mysql:database=db;host=host';
my $user = '...';
my $pass = '...';

my $result = db_query('select * from table');

my $dbh;
sub db_query {
  my $sql = $_[0];
  $dbh = DBI->connect_cached($db, $user, $pass);
  my $sth = $dbh->prepare($sql);
  $sth->execute();
  my $result_hashref = {};
  while (my $ref = $sth->fetchrow_hashref()) {
    $result_hashref->{ $ref->{'id'} } = $ref;
  }
  $sth->finish();

  return $result_hashref;
}

sub DESTROY {
  if (defined $dbh) {
    $dbh->disconnect();
  }
}

    [download]
    Is this the sort of thing you're looking for?

    .02

    cLive ;-)

[reply]
[d/l]
Re: Copy an object
by Jaap (Curate) on Apr 26, 2002 at 08:09 UTC
    Wow. I am impressed with so many wisdom in such a short time! Thank you very much for all your submissions.
    Apart from placing the $db handler only once in the script (thus outside the sub) and the destroyer, cLive ;-) recommends making my own copy of the hash data structure and return that.

    Perlplexer thinks about returning an array but i really like the hash way of working with this data.

    Supermonkey mey mean that since i do not disconnect or destroy my $sth, i can still get the data from it so i won't have to copy it? That could be a great solution. I am going to try it all out. Thank you very much.
[reply]

Back to Seekers of Perl Wisdom