byterock

In part 12 we had a look at a look at the array_execute and now we are going to look at bind_param's Evil Twin bind_col.

Bind what?

DBI's master chef designed it from the outset for 'SPEED', to get quickly to the point any extra under the hood calls to DBI will slow things down, bind_col eliminates some of the under the hood call to DBI and will make your assignment magically disappear

Lets that this example;

$sql = 'SELECT city, state_province, country_id FROM locations'; $sth = $dbh->prepare($sql); $sth->execute();

while (my $row = $sth->fetchrow_arrayref()){
my $r_city = $row->[0];
my $r_state = $row->[1];
my $r_country = $row->[2];
print "$r_city,$r_state,$r_country";
};

Remembering that is it best to put our bind_col assignment after the execute we change the code to this;

$sql = 'SELECT city, state_province, country_id FROM locations'; $sth = $dbh->prepare($sql); $sth->execute(); my ($r_city, $r_state,$country);

$sth->bind_col(1,$r_city);
$sth->bind_col(2,$r_state);
$sth->bind_col(3,$r_country);

while ( $sth->fetchrow_arrayref()){
print "$r_city,$r_state,$r_country";
};

and there you have it. You code will run faster and it is a little easier on the eyes.

How About a Little Less

Don't like the extra three lines how about just one. Give this a try;

..
$sth->bind_columns($r_city, $r_state,$country);
...

You Did Say Twin Didn't You

Yes bind_col has all the same attributes as the bind_col so you can use bind_col to format your fields as you like. So to get the correct date format you can give this a try

$sth->bind_col(1, undef, { TYPE => SQL_DATETIME });

and your field should come out as 'YYYY-MM-DD HH:MM:SS' rather that what ever funky format the native RDBMs is sending back.

Just one little note the 'undef' is there for backward compatibly with older versions of DBI.