• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2071
  • Last Modified:

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


0
StevenMiles
Asked:
StevenMiles
  • 4
  • 2
2 Solutions
 
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
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now