http://qs1969.pair.com?node_id=7556


in reply to What are placeholders in DBI, and why would I want to 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(<<SQL); insert into my_table (id, name) values ('15', 'Foo') SQL
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(<<SQL); insert into my_table (id, name) values (?, ?) SQL
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.

In order to benefit from 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.

So your final code would look something like this:

my $sth = $dbh->prepare_cached(<<SQL); insert into my_table (id, name) values (?, ?) SQL $sth->execute('15', 'Foo');
The other reason to use placeholders is that DBI will automatically take care of any quoting issues for you. You don't need to enclose the ?'s in single quotes, and you don't need to call DBI's quote method on them.

For more information, read the DBI manpage and A Short Guide to DBI.