jeff867 has asked for the wisdom of the Perl Monks concerning the following question:

Hello All,

I am using DBI for the first time and am able to use fetchrow_array without any problems. It returns data as I would expect.

My problem arises when I attempt to use fetchrow_hashref. This does not appear to work correctly. I am using Win2K with an Access db. 

use DBI;
use DBD::ODBC;

my $DSN = 'perltest'; 
my $dbh = DBI->connect("dbi:ODBC:$DSN", '','') || die "$DBI::errstr\n"
+; 

my $sql;
# there are three rows that match this select statement
$sql = "SELECT * FROM contacts WHERE phone = '8675309'";
$sth = $dbh->prepare($sql);
$sth->execute;
# this works fine as it displays the first row of data
@row = $sth->fetchrow_array;
print "@row<br>\n";

# this does not work - displays a blank line
$hashrow = $sth->fetchrow_hashref;
print "$hashrow{phone} <br>\n";

$sth->finish();
$dbh->disconnect();

[download]
I have gone through piles of other examples on this site and others, but still no luck - is it my code? am I blind and missing something??

Any help you could offer would be appreciated

-Jeff

Replies are listed 'Best First'.
(jeffa) Re: DBI and fetchrow_hashref
by jeffa (Bishop) on May 08, 2002 at 00:18 UTC
    You have no syntactical errors in your code, but there are a couple of safeguards that might help you to find what is going wrong. First - use strict! This will catch errors in variables names and types, which you don't appear to have. Next, don't use 'SELECT *'. Instead, explicitly list the field names you are selecting. This will help prevent you from trying to access mispelled field names.

    But, both of these do not appear to hamper your code at all - instead, i'll wager that there is only one record with the the phone number 8673509 (Jenny!).

    Try this instead - take out the WHERE clause and run your code again, i'll bet you get something this time. If not then replace the fetchrow_array fetch with the fetchrow_hashref fetch.

    Also, add use strict; at the top with your other use statements and add my to each new occurance of a variable. You have one for $DSN, $dbh, and $sql - add them to $sth, @row, and $hashrow - it is just good practice to do so.

    <UPDATE>
    usrbinperl++ and jsprat++ This is all the more reason to always, always use strict: because the code would not even compile due to the fact that you forgot two simple characters: - and > (jeffa needs to quit wagering, he keeps losing) ;)
    </UPDATE>

    Lastly, use Data::Dumper to inspect what fetchrow_hashref returns. Quick and easy: 

    use DBI;
use Data::Dumper;
use strict;

my $dbh = DBI->connect(
   qw(DBI:vendor:database:host user pass),
   {RaiseError=>1}
);

# setting RaiseError to true will cause your script to
# die with an error message from all database errors

my $sth = $dbh->prepare("
   select * from mp3.songs
   limit 200
");
$sth->execute;

my $hashrow = $sth->fetchrow_hashref;
print Dumper $hashrow;

# or just
print Dumper $sth->fetchrow_hashref;

    [download]

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]
Re: DBI and fetchrow_hashref
by usrbinperl (Initiate) on May 08, 2002 at 00:27 UTC
    At first glance, it appears the line

    print "$hashrow{phone} <br>\n";

    should be:

    print "$hashrow->{phone} <br>\n";

    Try that and see if it works
[reply]
[d/l]
[select]
      Thank you jeffa and usrbinperl

      Your combined assistance has allowed me to resolve the issue.

      Jeff

[reply]
Re: DBI and fetchrow_hashref
by jsprat (Curate) on May 08, 2002 at 00:30 UTC
    Try 'use strict', it will tell you where your error is.

    You have two different variables, $hashrow and %hashrow. %hashrow is a hash, $hashrow is a reference to a hash.

    The line print "$hashrow{phone}" attempts to print the value of %hashrow that corresponds to the 'phone' key (which has not been initialized). To dereference the hash, you need to use $hashrow->{phone}

    perldoc perlref would be helpful, I'm sure.

[reply]
[d/l]
[select]
Re: DBI and fetchrow_hashref
by mephit (Scribe) on May 08, 2002 at 05:22 UTC
    fetchrow_hashref() returns just that: a reference to a hash. You're trying to access an actual hash. Instead, try

     print $hashrow->{phone}, "\n";

    The Data::Dumper module is useful in cases like this. You can

     print Dumper ($hashrow);

    to see the structure of the reference, and figure out how to get at the data.

    HTH

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom