How do I handle MySQL finish and disconnect statements in perl?

I'm looking for an educated comment on how I'm accessing MySQL from perl subroutines.  Specifically, we're looking at how I'm using "finish" and "disconnect" statements when an error occurs, or if there is no error.

In general, here's what I do:

sub DoSomethingWithMySQL {
use DBI;

$dbh = DBI->connect (
"DBI:mysql:database=$Database;mysql_read_default_file=HomePath/misc/MySQL_Connect_File.dat", undef, undef, {PrintError => 1});

# If there is an error connecting to the database, I just exit out of the subroutine, assuming that there is no $dbh that needs to be disconnected:
if (defined($DBI::errstr)) {
      # Do various things here
      return (0);
}

$sth = $dbh->prepare ("SELECT * FROM my_table");
$sth -> execute();

# Here, though, if there is an error, I'm running "finish" and "disconnect" before I exit:
if (defined($dbh->errstr)) {
      # Do various things here
      $sth->finish();
      $dbh->disconnect();
      return (0);
}

# Then, if we get here, there have been no errors, run "finish" and "disconnect" before ending the sub:
$sth->finish();
$dbh->disconnect();
return (some_useful_information);

Is that a rational way to handle things?  Is there an obviously better way?

Also, if I use a "do" statement to run the query instead of "prepare" and "execute" ...
$numUpdated = $dbh->do ($query_or_update_or_whatever_here);

... then I just leave the $sth->finish() statements out, because I never defined $sth.

This seems to *work* fine, but I wanted an opinion from someone more experienced, because I don't know if I'll eventually get in trouble with this.

Thanks for any advice.

--Steve


StevenMilesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marchentCommented:
It is always good to finish and disconnect after mysql operation is done from coding.
If you need to return from function, or exit from code, its better to close the mysql connections manually using disconnect function.
It is a good practice to check whether a variable is defined or not before access it. Specially when you have doubt that the variable may not be initialized (hash/array etc.)
0
Adam314Commented:

#This:
$dbh = DBI->connect (
"DBI:mysql:database=$Database;mysql_read_default_file=HomePath/misc/MySQL_Connect_File.dat", undef, undef, {PrintError => 1});
 
# If there is an error connecting to the database, I just exit out of the subroutine, assuming that there is no $dbh that needs to be disconnected:
if (defined($DBI::errstr)) {
      # Do various things here
      return (0);
}
 
#Could also be written as:
unless($dbh = DBI->connect (
"DBI:mysql:database=$Database;mysql_read_default_file=HomePath/misc/MySQL_Connect_File.dat", undef, undef, {PrintError => 1})) {
    # Do various things here
    return(0);
}
 
###############################
#this:
$sth = $dbh->prepare ("SELECT * FROM my_table");
$sth -> execute();
 
# Here, though, if there is an error, I'm running "finish" and "disconnect" before I exit:
if (defined($dbh->errstr)) {
      # Do various things here
      $sth->finish();
      $dbh->disconnect();
      return (0);
}
#Doesn't check if the prepare was successful or not.  To check both the prepare
#and the execute, you could do:
unless($sth = $dbh->prepare ("SELECT * FROM my_table") {
    #Do various things here
    $dbh->disconnect;
    return(0);
}
 
unless($sth->execute) {
    # DO various things here
    $sth->finish;
    $dbh->disconnect;
    return(0);
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StevenMilesAuthor Commented:
Hi, Adam,

I like that.  Clearly, I wasn't checking for success of the prepare statement, and I was testing for success based on the presence or absence of the error string.  Quick follow-up question:  since we're checking every step for errors, does it make more sense to decrease the number of steps by skipping the prepare statement, and write:

$result= $dbh->do (some_MySQL_task_here);

so there is one fewer step?  And save this construction:

$sth = $dbh->prepare (some_MySQL_task_here);
$result= $sth->execute (data_for_the_task_here);

for those times when we want to run the execute statement several times, with different data?  Is there any advantage to using prepare/execute rather than 'do' if we have just one task to accomplish?

0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Adam314Commented:
If you are only executing once, and it is a non-select statement, there is no advantage to prepare/execute over do.  If you look at the documentation for DBI, the do function is esentially a call to prepare then execute.
    http://search.cpan.org/~timb/DBI-1.607/DBI.pm#do
Because you don't have a statement handle after calling do, you don't have any way to get the data, so this is not so useful for select statements.

For select statements executing only once, you could use one of the select* functions on the database handle.  These return all of the data at once.
0
StevenMilesAuthor Commented:
Hi, Adam,

I'm not so sure that the "prepare" statement generates errors.
If I run the following code:

---- code start
$sth = $dbh->prepare ("SELCT name, wins, losses FROM teams");
# (note the misspelled "SELECT"
print STDERR "Just after prepare statement: \n $DBI::errstr \n $DBI::err \n sth:$sth ";

print STDERR "Just before execute statement\n";
$sth -> execute();
print STDERR "Just after execute statement\n";
---- code end


...I get the following output:


----start output
Just after prepare statement:
sth:DBI::st=HASH(0xab56580)

Just before execute statement
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELCT name, wins, losses FROM teams' at line 1 at /home/www/mydomain/root/cgi-bin/mysql_test_read.pl line 40 (#2)
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELCT name, wins, losses FROM teams' at line 1 at /home/www/mydomain/root/cgi-bin/mysql_test_read.pl line 40.
Just after execute statement
----end output


And if I run:
unless ($sth = $dbh->prepare ("SELCT name, wins, losses FROM teams")) {
      print STDERR "select statement failed";
      exit;
}

... "select statement failed" doesn't print to STDERR, and processing of the program continues.

Am I seeing something wrong?

Also, what are the "select*" functions?  You're not referring to the fetchall functions, are you?

--Steve

0
Adam314Commented:
In general, it is possible for a prepare statement to fail.  Some databases might wait until they try to execute the statement before they fail, which is what is happening here.  I'm not sure if MySQL will ever fail on a prepare, but it's good practice to test anyways - you might be using a different database at some point.

The select* functions are similar to the fetchall functions, but you can use them directly on the database handle, rather than on a statement handle after it's been executed.  See here, and the following 6 functions:
    http://search.cpan.org/~timb/DBI-1.607/DBI.pm#selectrow_array


#If $dbh is your database handle
 
########## Get 1 row
my @row = $dbh->selectrow_array('SELECT a,b,c FROM t');
 
#OR
my $sth = $dbh->prepare('SELECT a,b,c FROM t');
$sth->execute;
my @row = $sth->fetchrow_array;
 
########## Get all rows
my $rows = $dbh->selectall_arrayref('SELECT a,b,c FROM t');
 
#OR
my $sth = $dbh->prepare('SELECT a,b,c FROM t');
$sth->execute;
my $rows = $sth->fetchall_arrayref;

Open in new window

0
Adam314Commented:
In the code I just posted... If you don't have RaiseError set to true, you would want to check for errors after those statements.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.