Why does Perl DBI require a prepare statement before execution?

Question

I am currently working on a bit of Perl script and I am very new to it. I ran into a problem with Perl because I am accustomed to Php's syntax of MySQL where you create the statement you want and then execute it and then it sends the information to the server-side.

However, with Perl and the DBI module, it requires that you create the statement, prepare the statement, and then execute the statement sending the information to the server-side.

Is it because Perl is similar to a high-level programming language and has a practical compiler inside of it that it requires this prepare statement before execution?

I am using a MySQL update statement, is it specific on the statements (update vs select)?

When I say 'create the statement' I mean something like:

$query = "UPDATE table SET column = value";

Maybe I haven't done enough research on the topic?

Answer 1

You actually don't have to use prepare directly (there are some shortcuts.) But using it allows you to tell the database driver about a single statement which can be repeatedly executed without the database having to recompile it every time. For example

my @records = ( [ 1, 2, 3 ], [ 4, 5, 6 ], [ 7, 8 9 ] );
my $sth = $dbh->prepare( "INSERT INTO foo VALUES ( ?, ?, ? )" );
foreach my $record( @records ) { 
    $sth->execute( @$record );
}

This will execute the same prepared statement three times, one for each set of three values in @records. This also demonstrates the use of ? placeholders, which you should always use instead of interpolating variables directly into SQL strings.

If you only need to execute a statement once, you can combine the prepare and execute statements into a single step:

$dbh->do( "INSERT INTO foo VALUES ( ?, ?, ? )", undef, 1, 2, 3 );

The first argument is the SQL string (preferable with placeholders) the seconds is an optional attributes hash (left undef here) and the remaining params are what gets substituted in for the placeholders.