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
[download]

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
[download]

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

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

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');
[download]

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.

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.