Programming the Perl DBI

Bind Values and Data Typing

Perl is a loosely typed language, in that you have strings and you have numbers. Numbers can be strings and strings can, on occasion, be numbers. You can perform arithmetic on strings. It can all be very confusing for us, so you can imagine how the driver feels when confronted with bind values.

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

The use DBI qw(:sql_types); statement asks for the standard SQL types to be imported as names, actually subroutines, that return the corresponding standard SQL integer type value. SQL_VARCHAR, for example, returns 12. If you don’t ...