[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-03
7
Medium Priority
?
2,003 Views
Last Modified: 2008-11-10
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


0
Comment
Question by:StevenMiles
  • 4
  • 2
7 Comments
 
LVL 13

Assisted Solution

by:marchent
marchent earned 200 total points
ID: 22869873
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
 
LVL 39

Accepted Solution

by:
Adam314 earned 800 total points
ID: 22879764

#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
 

Author Comment

by:StevenMiles
ID: 22921920
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 39

Expert Comment

by:Adam314
ID: 22922933
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
 

Author Comment

by:StevenMiles
ID: 23096162
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
 
LVL 39

Expert Comment

by:Adam314
ID: 23097554
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
 
LVL 39

Expert Comment

by:Adam314
ID: 23097567
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question