Programming the Perl DBI

Binding Values Without bind_ param( )

Calling bind_ param( ) for each placeholder can be rather long-winded and tedious when you have many placeholders, so the DBI provides a simpler way to do it via the execute( ) method. When you call execute( ), you can simply give it a list of values, and execute( ) will call bind_ param( ) on each one for you.

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

will result in the value of 123456 being supplied to the database as the bind value with the type ...