http://qs321.pair.com?node_id=1010999

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

Hi, I need to run a query which fetches two columns from data base and need to store in hash as key and values, I'm confused of how to store the data in hashes can anyone suggest me how to do that:(

This is the query I'm having
select seq_nbr,data_record from usage_inq ;

Replies are listed 'Best First'.
Re: Storing data into hashes
by davido (Cardinal) on Dec 31, 2012 at 06:17 UTC

    What part do you need help with? I mean have you already worked out how to connect to the database, how to prepare and execute the statement? I get that you need help assigning to the hash, but where are we starting from? It would help us to help you if we could see 20 lines of complete, executable code that demonstrate to us exactly where you're struggling.


    Dave

Re: Storing data into hashes
by roboticus (Chancellor) on Dec 31, 2012 at 06:19 UTC

    rkrish:

    If you're using DBI then it's pretty easy: the fetchrow_hashref will give you an anonymous hash of the column names with the data for a row. There's another method that can give you the entire resultset in a hash, but you have to make sure you give it a unique key or you may lose data.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

Re: Storing data into hashes
by NetWallah (Canon) on Dec 31, 2012 at 06:18 UTC
    It would help if you give us information on your OS, Database, and perl.

    The starting point of your research with perl and database access is DBI.

                 "By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and third by experience, which is the bitterest."           -Confucius

Re: Storing data into hashes
by nithins (Sexton) on Dec 31, 2012 at 06:50 UTC

    assuming you know to use DBI module, appending a sample example to do your work

    my %has; my $query ="select * from table name "; my $exe = $dbh->prepare($query); $exe->execute(); while(my $data = $exe->fetchrow_hashref){ $has{$data->{firstattribute}}=$data->{secondattribute}; }

      Hi, I have tried this but unable to see any data stored in hash. The query is valid and it is fetching the data.I have verified it.

      sub loadSeqNbr { my %SeqRec; my $SeqRecord = $lda->prepare("select seq_nbr,data_record from usa +ge_inq" ); $SeqRecord->execute() while ( my $data = $SeqRecord->fetchrow_hashref()) { $SeqRec{$data->{seq_nbr}}=$data->{data_record}; } print ("content of hash is %SeqRec \n"); }
      output is:
      content of hash is %SeqRec

        Hashes don't interpolate in strings:

        #!perl -w use strict; my %SeqRec; print ("content of hash is %SeqRec\n");

        I recommend to use Data::Dumper for debugging contents of variables:

        use Data::Dumper; my %SeqRec = (foo => 'bar'); print ("content of hash is " . Dumper \%SeqRec);

        rkrish:

        A couple things:

        • Have you put a print statement inside your while loop to verify that results are coming back?
        • Have you tried using Data::Dumper to print the contents of $data in the loop?
        • Some databases will return the column names in upper case (I'm looking at you Oracle!).

        (Printing the contents of $data when read will show you if it's a case problem or not.)

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.