14.12.1. Problem

14.12.3. Discussion

"Prepare once, execute often" is one secret to DBI success. By separating preparation from execution, the database server can parse and optimize queries once and then execute them many times. Most databases can do this even when the queries contain placeholders for values to be filled when the query is executed.

$sth = $dbh->prepare('SELECT id,login FROM People WHERE middle_initial = ?');
$sth->execute('J');

If you have multiple parameters to bind, pass more values to execute:

$sth = $dbh->prepare('SELECT * FROM Addresses WHERE House = ?
                      AND Street LIKE ?');
$sth->execute('221b', 'Baker%');

You don't have to do the binding and the execution in one step. The bind_param function binds without executing:

$sth = $dbh->prepare('SELECT id,login FROM People WHERE middle_initial = ?');
$sth->bind_param(1, 'J');
$sth->execute( );

The first argument to bind_param is the placeholder number (starting from 1) in the statement:

$sth = $dbh->prepare('SELECT * FROM Addresses WHERE House = ?
                      AND Street LIKE ?');
$sth->bind_param(1, '221b');
$sth->bind_param(2, 'Baker');

You can give an optional third argument to bind_param that identifies the data type of the value and thus whether to quote it:

$sth->bind_param(1, 'J', SQL_CHAR);

If you want to use this type argument, you must import the types explicitly or import all of them:

use DBI qw(SQL_CHAR SQL_INTEGER);
use DBI qw(:sql_types);

List all types with:

foreach (@{ $dbi::EXPORT_TAGS{sql_types} }) {
  printf "%s=%d\n", $_, &{"DBI::$_"};
}

You do not need to quote the values you pass to bind or to a binding execute. DBI automatically quotes them if they are to be used as strings.

The major limitation to binding is that you often can't use placeholders for table or column names. That is, you can't prepare this query:

SELECT ?,? FROM ? WHERE ? = ?

Remember, the goal of separate prepare and execute is to let the database server optimize the query. There's precious little information to do any optimization on in that query!

A smaller limitation is that each placeholder can represent only a single scalar value. Consider this query:

SELECT id,login FROM People WHERE name IN (?)

You can prepare this query without a problem, but you can't bind more than one value to the placeholder.

14.12.4. See Also