One topic we have mentioned in our discussion of the preparation of statement handles is bind values. You may also have come across the phrases placeholders , parameters , and binding . What are these things?
A bind value is a value that can be bound to a placeholder declared within an SQL statement. This is similar to creating an on-the-fly SQL statement such as:
$sth = $dbh->prepare( "
SELECT name, location
FROM megaliths
WHERE name = " . $dbh->quote( $siteName ) . "
" );but instead of interpolating the generated value into the SQL statement, you specify a placeholder and then bind the generated value to that. For example:
$sth = $dbh->prepare( "
SELECT name, location
FROM megaliths
WHERE name = ?
" );
$sth->bind_param( 1, $siteName );The bind_ param( )
method is the call that actually associates the supplied value with
the given placeholder. The underlying database will correctly parse
the placeholder and reserve a space for it, which is “filled
in” when bind_ param( ) is called. It is
important to remember that bind_ param( ) must be
called before
execute( );
otherwise, the missing value will not have been filled in and the
statement execution will fail.
It’s equally simple to specify multiple bind values within one
statement, since bind_ param( ) takes the index,
starting from 1, of the parameter to bind the given value to. For
example:
$sth = $dbh->prepare( "
SELECT name, location
FROM megaliths
WHERE name = ?
AND mapref = ?
AND type LIKE ?
" );
$sth->bind_param( 1, "Avebury" );
$sth->bind_param( 2, $mapreference );
$sth->bind_param( 3, "%Stone Circle%" );You may have noticed that we haven’t called the quote( ) method on the values. Bind values are passed to the
database separately from the SQL statement,[48] so there’s no need to “wrap up” the
value in SQL quoting rules.
Some database drivers can accept placeholders in the form of
:1, :2, and so on, or even
:name or :somevalue, but this
is not guaranteed to be portable between databases. The only
guaranteed portable placeholder form is a single question mark,
?. Of course, if the underlying database in
question doesn’t support binding, the driver may fail to parse
the statement completely.
So, why use bind values? What’s the real differences between these and interpolated on-the-fly SQL statements?
On the face of it, there’s no obvious difference. Interpolated statement creation uses Perl’s string-handling functionality to create a complete SQL statement to send to the database. The bind values are sent to the database after the SQL statement, but just before it’s executed. In both cases, the same result is achieved.
The actual difference lies in the way that databases handle bind values, assuming that they do. For example, most large database systems feature a data structure known as the "Shared SQL Cache,” into which SQL statements are stored along with additional related information such as a query execution plan .
The general idea here is that if the statement already exists within the Shared SQL Cache, the database doesn’t need to reprocess that statement before returning a handle to the statement. It can simply reuse the information stored in the cache. This process can increase performance quite dramatically in cases where the same SQL is executed over and over again.[49]
For example, say we wished to fetch the general information for 100 megalithic sites, using the name as the search field. We can write the following SQL to do so:
SELECT name, location, mapref FROM megaliths WHERE name = <search_term>
By using interpolated SQL, we would actually issue 100 different SQL statements to the database. Even though they are almost identical, they are different enough for the database to re-parse the statement and not use the cached information. By using a bind value, the same piece of SQL and the same “execution plan” will be reused over and over again, even though a different bind value is supplied for each query.
Therefore, for databases that support it, using bind values with prepared statement handles can quite dramatically increase the performance of your applications and the efficiency of your database. This is especially significant when trying to insert many records.
That said, there are good reasons to use interpolated SQL statements instead of bind values. One of these reasons could be simply that your database doesn’t support bind values! A more complex reason is that your database may have restrictive rules about what parts of an SQL statement may use placeholders.
In the examples listed above, we’ve illustrated the use of bind values to supply conditions for the query. For the sake of badness, say we wanted to iterate through a list of database tables and return the row count from each one. The following piece of code illustrates the idea using an interpolated SQL statement:
foreach $tableName ( qw( megaliths, media, site_types ) ) {
$sth = $dbh->prepare( "
SELECT count(*)
FROM $tableName
" );
$sth->execute( );
my $count = $sth->fetchrow_array( );
print "Table $tableName has $count rows\n";
}By using an interpolated statement, this code would actually execute correctly and produce the desired results, albeit at the cost of parsing and executing four different SQL statements within the database. We could rewrite the code to use bind values, which would be more efficient (theoretically):
$sth = $dbh->prepare( "
SELECT count(*)
FROM ?
" );
$sth->bind_param( 1, $tableName );
...On most databases, this statement would actually fail to parse at the
prepare( ) call, because placeholders can
generally be used only for literal values. This is because the
database needs enough information to create the query execution plan,
and it can’t do that with incomplete information (e.g., if it
doesn’t know the name of the table).
Additionally, the following code will fail, since you are binding more than just literal values:
$sth = $dbh->prepare( "
SELECT count(*)
FROM megaliths
?
" );
$sth->bind_param( 1, "WHERE name = 'Avebury'" );
...Of course, your driver might just support this sort of thing, but don’t rely on it working on other database systems!
[48] This is not strictly true, since some drivers emulate placeholders by doing a textual replacement of the placeholders with bind values before passing the SQL to the database. Such drivers use Perl’s internal information to guess whether each value needs quoting or not. Refer to the driver documentation for more information.
[49] I’ve known a case where the database spent over a minute just trying to work out a “good enough” query execution plan for a complex SQL query. In cases like this, reuse of the processed statement handle makes for a very large improvement in performance.