Table of Contents for

MySQL in a Nutshell, 2nd Edition

This funciton associates or binds a column from a statement handle to a given variable. The values are updated when the related row is retrieved using a fetch method, without extra copying of data. Here is an example:

...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();

$sth->bind_col(1, \$title);
$sth->bind_col(2, \$author);

while($sth->fetch()) {
   print "$title by $author \n";
}

In this example, we’re specifying that the first (1) column be bound to the variable $title and the second to $author. A separate statement has to be issued for each bind. To bind multiple columns in one statement, use bind_columns().

To specify the column data type to use for the variable—this can potentially change the data—give the desired SQL standard type as the third argument:

...
my $sql_stmnt = "SELECT title, author FROM books";
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();

$sth->bind_col(1, \$title, { TYPE=>SQL_VARCHAR } );
$sth->bind_col(2, \$author, { TYPE=>SQL_VARCHAR } );

while($sth->fetch()) {
   print "$title by $author \n";
}

To get a list of SQL standard data types available on your server, run this program:

#!/usr/bin/perl -w
use DBI;

foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
   printf "%s=%d\n", $_, &{"DBI::$_"};
}