do vs execute?

[replic]

Hello everyone,
assuming i want to write some values into my database of choice i found two ways to do this.

my $sql = "INSERT INTO table (foo, bar, foobar) VALUES (?, ?, ?)";
my $sth = $dbh->prepare($sql);

foreach(sort keys %hash)
{	
	$sth->execute($_, $hash{$_}, $some_other_value);	
}

as opposed to this one:

foreach(sort keys %hash)
{	
        $dbh->do("INSERT INTO table (foo, bar, foobar) VALUES ('$_', '$hash{$_}', '$some_other_value')");
}

Speedwise they are both pretty much the same. My question is now:
Is one of them the "better" or "right" way to do it?

[d5e5]

I wouldn't call either of them wrong. For multiple inserts in a loop, I prefer the prepare + execute with place holders rather than the do, for a couple of reasons. In theory it makes more sense to prepare the insert statement once outside your loop and then execute it inside the loop for each set of values. You say it makes no difference in speed for MySQL, so there is no need to change scripts that already run satisfactorily ("If it ain't broke, don't fix it"). However if one day you migrate to a different database system and want to run the same scripts maybe it will make a difference, since some databases may optimize execution of previously prepared sql better than MySQL does.

Another reason. Look what happens in the following example of using $dbh->do in a loop. It works for some names but not the last one because it contains an apostrophe, whereas preparing the sql beforehand and passing the values to place holders avoids this problem.

#!/usr/bin/perl
use strict; 
use warnings; 
use DBI;

my $dbh=DBI->connect('dbi:mysql:daniweb','david','dogfood') ||
   die "Error opening database: $DBI::errstr\n";

$dbh->do("CREATE TABLE IF NOT EXISTS people (fname VARCHAR(20),
                                lname VARCHAR(20))");

while (<DATA>){
    chomp;
    my ($fname, $lname) = split;
    my $sql = "insert into `people` (fname, lname) values ('$fname', '$lname')";
    print $sql, "\n";
    $dbh->do($sql) or die "do failed: " . $dbh->errstr(); 
}

# Close connection
undef($dbh);

__DATA__
John Doe
Jane Smith
Martin O'Hara

Outputs

DBD::mysql::db do 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 'Hara')' at line 1 at /home/david/Programming/Perl/temp01.pl line 17, <DATA> line 3.
insert into `people` (fname, lname) values ('John', 'Doe')
insert into `people` (fname, lname) values ('Jane', 'Smith')
insert into `people` (fname, lname) values ('Martin', 'O'Hara')
do 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 'Hara')' at line 1 at /home/david/Programming/Perl/temp01.pl line 17, <DATA> line 3.

See? The above fails, but replacing the do with prepare, place holders and execute works fine.

.
.
.
my $sth = $dbh->prepare("insert into `people` (fname, lname) values (?, ?)")
             or die "prepare failed: " . $dbh->errstr();  

while (<DATA>){
    chomp;
    my ($fname, $lname) = split;
    $sth->execute($fname, $lname) or die "execute failed: " . $sth->errstr(); 
}

# Close connection
$sth->finish();
undef($dbh);

__DATA__
John Doe
Jane Smith
Martin O'Hara

[replic]

Thanks for clearing that up for me. I was just a little unsure which of the two to use since i am still learning. Much appreciated.