proper use of database connections, when to finish, when to disconnect

[spewn]

i have some code in a script i wrote, an example is located below.

never mind the names of tables and fields. they all work in my script, i've just changed them for easy reading.

$sth = $dbh->prepare("select field1 from mytable1 where field5 = 'donuts'");
$sth->execute;
use constant SN1 => 0;
while (@row = $sth->fetchrow_array()){
$snackitem = $row[SN1];
}

if ($snackitem ne '1') {
$sth2 = $dbh->prepare("UPDATE members SET field4 = 'no' where field5 = 'donut'");
$sth2->execute;
}

$sth3 = $dbh->prepare("INSERT INTO mytable2 VALUES(0,'info','moreinfo')");
$sth3->execute;
$newID=$dbh->{q{mysql_insertid}};

$sth4 = $dbh->prepare("select f1 from mytable6 where f4 = '2/2/2009'");
$sth4->execute;
$count = $sth4->rows;
use constant AID => 0;
 while (@row = $sth2->fetchrow_array() ){
 $aidx = $row[AID];
 }

 if ($count eq 1) {
 $sth5 = $dbh->prepare("UPDATE mtable SET clicks = '$newclicks' where f9 = '$afid'");
 $sth3->execute;
 }
 else{
 $sth5 = $dbh->prepare("INSERT INTO mtable VALUES(0,'')");
 $sth5->execute;
 }

my question is where do i need to put $dbh->disconnect(); and do i need to put $dbh->finish();?

i'm trying to set this up properly and not have open connections.

thanks!

- g

 

[PaulTEG]

when script finishes, so too do connections.

are you seeing something else ...?

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[TrojanWarBlade]

Generally it is bad practice to leave handles open longer than necessary because it can cause memory (and other resources) to be tied up.
If you've finished with a statement handle then it is probably wise to close it.
I suspect you might well need the DB connection itself available for the entire duration of your script execution but personally I'd still prefer to see things closed manually rather than leaving it to perl to clean up after you.

Managing the life of anything in your script is something you should really consider and on that basis I would suggest that choosing to deliberately close handles when no longer needed is a principle of good practice that will help you to manage all your resources efficiently.


Trojan.

 

[MrCBofBCinTX]

while (@row = $sth->fetchrow_array()){
$snackitem = $row[SN1];
}

If you are really only fetching this one item and not a list of items, there is a better way to do this. And it doesn't have to be closed separately.

my $statement = "SELECT field1 FROM mytable1 WHERE field5 = 'donuts'";
my $snackitem;
unless (($snackitem) = $dbh->selectrow_array($statement)) {die "can't execute the query: $dbh->errstr";}

Since this method fetches arrays, you may also get more than one item, such as select field1, field2
($snackitem1, snackitem2) = $dbh etc.

This method only is useful however for one fetch, not getting all of many values of column field1

I really like this method when appropriate, very short, yet very clear code.