5.3.1. Bind Values Versus Interpolated Statements

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'" );
    ...

5.3.2. Bind Values and Data Typing

To help the driver work out what sort of data is being supplied in a bind value, you can supply an additional argument that specifies the datatype. For example, the following code will bind the appropriately typed bind values to the statement for execution in the database:

use DBI qw(:sql_types);

$sth = $dbh->prepare( "
            SELECT meg.name, meg.location, st.site_type, meg.mapref
            FROM megaliths meg, site_types st
            WHERE name = ?
            AND id = ?
            AND mapref = ?
            AND meg.site_type_id = st.id
          " );
### No need for a datatype for this value. It's a string.
$sth->bind_param( 1, "Avebury" );

### This one is obviously a number, so no type again
$sth->bind_param( 2, 21 );

### However, this one is a string but looks like a number
$sth->bind_param( 3, 123500, { TYPE => SQL_VARCHAR } );

### Alternative shorthand form of the previous statement
$sth->bind_param( 3, 123500, SQL_VARCHAR );

### All placeholders now have values bound, so we can execute
$sth->execute(  );

If a type is specified, the driver should take that as a strong hint about what to do. But it is just a hint. Some drivers don't pay any attention to the specified type. Of those that do, most only use it to differentiate between strings, numbers, and LONG/LOB types. This is a relatively new area for the DBI and drivers, and one that's advancing slowly.

5.3.3. Binding Input and Output Parameters

An additional argument stating the maximum length of the value to be returned must also be specified. If the returned value exceeds this value, the execute( ) call will fail. Therefore, if you aren't sure how large the return value might be, you should be pessimistic and supply a large value for this parameter. The only cost of doing so is using more memory than you need to.

An Oracle-specific example showing how bind_ param_inout( ) works revolves around the following stored procedure, which returns the nearest integer values to a given input value:

-- Example stored procedure written in Oracle PL/SQL
PROCEDURE ceiling_floor (value IN NUMBER, c OUT NUMBER, f OUT NUMBER) IS
BEGIN
    c := CEIL(value);
    f := FLOOR(value);
END;

The DBI code to get these return values out of this procedure can be written as follows:

### The variables to be populated as return values...
my $ceiling;
my $floor;

$sth = $dbh->prepare( "BEGIN ceiling_floor( ?, ?, ? ); END;" );
$sth->bind_param( 1, 42.3 );
$sth->bind_param_inout( 2, \$ceiling, 50 );
$sth->bind_param_inout( 3, \$floor, 50 );
$sth->execute(  );

print "Stored procedure returned $ceiling, $floor\n";

You can use both bind_ param( ) and bind_ param_inout( ) on the same statement handle quite happily. Of course, if you use bind_ param( ) when a return value is expected, that return value will be lost.

There is one quite subtle difference between bind_ param( ) and bind_ param_inout( ) that's worth pointing out. When you call bind_ param( ), the bind value you supply is copied and can't be changed without calling bind_ param( ) again. However, when you call bind_ param_inout( ), it is the reference that's copied. The actual value that the reference points to is not read until execute( ) is called.

5.3.4. Binding Values Without bind_ param( )

Furthermore, the do( ) method described above, and the selectrow_array( ) and selectall_arrayref( ) methods, which we'll discuss shortly, all call execute( ) one way or another, and also accept a list of bind values.

The following code illustrates passing a bind value to the execute( ) method:

$sth = $dbh->prepare( "
            SELECT name, location, mapref
            FROM megaliths
            WHERE name = ? OR description LIKE ?
          " );
$sth->execute( "Avebury", "%largest stone circle%" );
...

When specifying bind values in this manner, explicit data typing of the supplied values is not possible. In some cases, the underlying driver will correctly guess the type, but in most cases all values are passed to the database as SQL_VARCHAR values. However, if you have previously called bind_ param( ) or bind_ param_inout( ) for some or all placeholders with an explicitly specified datatype, that datatype will be used instead. For example:

$sth->prepare( "
            SELECT name, location, mapref
            FROM megaliths
            WHERE id = ?
          " );
$sth->bind_param( 1, 42, SQL_INTEGER );
$sth->execute( 123456 );
...