Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Use of fetchall_hashref

by Ammu (Novice)
on Sep 07, 2012 at 11:20 UTC ( #992289=perlquestion: print w/ replies, xml ) Need Help??
Ammu has asked for the wisdom of the Perl Monks concerning the following question:

Our requirement is to fetch some rows from a database table and put it in a new hashtable with a specified column as key.

$dbh = DBI->connect("dbi:ODBC:Driver={SQL Server};Server=<Servername>; +UID=<username>;PWD=<pswd>") ; my $sth = $dbh->prepare("select id,name,job,location from <databasenam +e>.employee_relation"); $sth->execute; my $hash = $sth->fetchall_hashref('id'); print Dumper($hash);
When i am using Dumper i will be able to get all tha entries for hashtable.But what if i need to get the value corresponding to a certain key. I tried print "@{$hash{'E200'}}\n"; This if for printing the values corresponding to the id E200.But its not showing any output.Please help.

Comment on Use of fetchall_hashref
Select or Download Code
Replies are listed 'Best First'.
Re: Use of fetchall_hashref
by choroba (Canon) on Sep 07, 2012 at 12:06 UTC
    $hash->{id} contains the value. If you want to dereference it as an array, wrap it into @{ ... }.
    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
Re: Use of fetchall_hashref
by Corion (Pope) on Sep 07, 2012 at 12:14 UTC
    If you only need a certain value for the "id" column, it's best to only query that value from the databases instead of reimplementing the search in Perl.
Re: Use of fetchall_hashref
by runrig (Abbot) on Sep 07, 2012 at 14:47 UTC
    Your $hash is a reference to a hash, not a hash. So you would need, e.g.:
    print Dumper $hash->{E200}; # Or for a certain column print "$hash->{E200}{job}\n";

      hi we tried this print Dumper $hash->{E200}; But it is returning something like $VAR1 = undef; please help me out :( tried this also print "$hash->{E200}{job}\n"; But it not printing any value .

        Then all I can say is look at the output of:
        print Dumper $hash;
        and see what the output is, see if 'E200' is in there, etc.
Re: Use of fetchall_hashref
by philiprbrenan (Monk) on Sep 07, 2012 at 13:12 UTC
Re: Use of fetchall_hashref
by frozenwithjoy (Curate) on Sep 07, 2012 at 21:29 UTC
    This isn't a direct answer to your problem, but Darin McBride just wrote a demo for DBD::CSV that also covers the problem of restructuring the hashref result from fetchall_hashref. It is a good, fast read. The relevant part is:
    my %totals; for my $r (@$res) { $totals{ $r->{AcNo}}{$r->{Type} } = $r->{SUM}; }
Re: Use of fetchall_hashref
by Ravi V (Scribe) on Sep 07, 2012 at 15:20 UTC
    Have you tried Sybase::Simple module ..... it has methods which directly returns data in form of hashes

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://992289]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (13)
As of 2015-07-29 22:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (269 votes), past polls