reusing DBI statement handle

[Manish Sapariya]

In following db functions I am using returned statement handle 
and afterword just ignoring it and overwrite it with the newly
returned value.

Does this cause memory leak/ or any kind of resouce leak?
Can I reuse statement handle in such a way? If not, what is
cleaner way to do it?

-
sub getTotalDBTags (){
my $rTableListRef;
my $query;
my $sth;
my $count=0;
my $rRowCount=0;

db_init();
$query = select TableName from hourly_tables;
$sth = perform_query($query);
$rTableListRef = $sth-fetchall_arrayref;

$query=;
foreach $table (@$rTableListRef) {
$query = select count(*) from $table;
$sth = perform_query($query);
$rRowCount = $sth-fetchall_arrayref;
$count = $count + $rRowCount-[0][0];
print count = $count\n;
}

$db{handle}-disconnect();
return $count;
}

sub perform_query($$) {
my ($query) = @_;
my $sth = $db{handle}-prepare($query);
unless ($sth) {
ERROR (Failed to prepare sql query: $query\n);
return undef;
}

unless ($sth-execute()) {
ERROR (Failed to execute sql query: $query\n);
return undef;
}
return $sth;
}
sub db_init () {
my $query = ;
if ($db{handle} = DBI-connect($db{name}, $db{user}, $db{pass}))
{
return(1);
}
else
{
ERROR (Unable to connect to database $db{name}\n);
return(0);
}
}

-
Thanks,
Manish

--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response

Re: reusing DBI statement handle

[Jeff 'japhy' Pinyan]

On Sep 23, Manish Sapariya said:


In following db functions I am using returned statement handle 
and afterword just ignoring it and overwrite it with the newly
returned value.

Does this cause memory leak/ or any kind of resouce leak?
Can I reuse statement handle in such a way? If not, what is
cleaner way to do it?


You're doing it in an acceptable manner.  One suggestion would be to move 
the scope of $sth to JUST the foreach loop, though:



   my $sth;
   ...
   foreach $table (@$rTableListRef) {
$query = select count(*) from $table;
$sth = perform_query($query);
  ...
   }


might be better written as


   foreach $table (@$rTableListRef) {
$query = select count(*) from $table;
my $sth = perform_query($query);
  ...
   }


The other thing I've noticed is your use of function prototypes.  Don't 
use them, because they are rarely needed, and your program isn't even 
actually using them at all.  Not to mention this one is written wrong:



sub perform_query($$) {
   my ($query) = @_;
   ...
}


Your prototype SAYS it's getting TWO scalars, but you've only passed one. 
But this is never noticed because you've called the function *before* Perl 
has seen its prototype.


Long story short:  don't use prototypes.

--
Jeff japhy Pinyan%  How can we ever be the sold short or
RPI Acacia Brother #734%  the cheated, we who for every service
http://www.perlmonks.org/  %  have long ago been overpaid?
http://princeton.pm.org/   %-- Meister Eckhart

--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response