dbiplace - Why to use DBI placeholders

Published at: 2000.08.18 23:38:26

NAME
SYNOPSIS
DESCRIPTION
AUTHOR
SEE ALSO

Describes what placeholders are in DBI, and why you should use them.

Say that you're executing the same SQL statement over and over; for example, perhaps you're importing data from a tab-separated data file into a database. Your insert statement is going to look the same each time; the only thing that will be different between statements is the actual values you're inserting.

For example, you want to insert 100 records into a table with two columns, so you write your insert statement like this:

    my $sth = $dbh->prepare("insert into my_table (id, name) values ('15', 'Foo')");

The '15' and 'Foo' are literal values that you've placed into your SQL statement; they will be different (presumably) for each record that you're inserting. So you would have to prepare a similar statement 100 times (once for each record).

Placeholders allow you to prepare the statement once; instead of using literal values, you just use '?', like this:

    my $sth = $dbh->prepare("insert into my_table (id, name) values (?, ?)");

When you want to actually execute this statement, you give execute the values to insert:

    $sth->execute('15', 'Foo');

This means that the database has to do much less work. Instead of compiling your statement 100 times, it only has to compile it once.

Of course, in order to reap the benefits of this newfound efficiency, you'll have to modify your code slightly. The best way to do this is to use the prepare_cached method instead of prepare. prepare_cached does the same thing as prepare, but first looks to see if the statement has already been compiled; if it has, it returns the compiled version.

AUTHOR

Benjamin Trott, ben@rhumba.pair.com