On Thu, Dec 18, 2003 at 08:40:15AM -0000, Mace, Richard wrote:
> I have one table containing a respondents contact details, e.g. name,
> address fields, telephone numbers, etc. Some of the telephone number
> cells are blank/ empty. When I query in psql, using "SELECT telephone from
> sample" the return shows all columns, including the blanks (as expected).
>
> Using the Perl DBI I try to pull out telephone again and bind the result
> to a pre-defined variable $telephone. e.g.
>
> $sth = $dbh->prepare("SELECT telephone from sample");
> $sth->execute();
> $sth->bind_columns(undef, \$telephone);
You shouldn't need undef as the first argument to bind_columns(). The DBI
doc says, "For compatibility with old scripts, the first parameter will
be ignored if it is 'undef' or a hash reference."
> while ( $sth->fetch ) {
> print STDERR "$telephone\n";
> }
> $sth->finish;
>
> This fails with "Statement has no result to bind(perhaps you need to
> call execute first)"
This typically happens if execute() failed, in which case you should see
the reason if $dbh has the RaiseError or PrintError attributes turned on.
You could also do this:
$sth->execute() or die "execute failed: ", $dbh->errstr;
execute() could fail if the SQL statement has a syntax error or if one
of the referenced columns or tables doesn't exist. Are you sure the
column and table names are spelled correctly?
> This code works fine for a field that is always populated e.g. name in
> place of telephone in line 1.
>
> Is my code fatally flawed or do I need to deal with the blank cells
> differently when the data is written to the database in the beginning,
> or before the bind. Any help greatly appreciated.
DBI should return undef for NULL values; aside from checking for undef
so you don't get "Use of uninitialized value" warnings, you shouldn't
need to do anything special.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/