Beefy Boxes and Bandwidth Generously Provided by pair Networks BBQ
The stupid question is the question not asked
 
PerlMonks  

selectall_hashref

by mnlight (Scribe)
on Jan 04, 2006 at 22:42 UTC ( #521043=perlquestion: print w/ replies, xml ) Need Help??
mnlight has asked for the wisdom of the Perl Monks concerning the following question:

I came across the selectall_hashref by way of some advice I recieved on a previous post.
It looks like something that might work for a script I am writing.
I am trying to test it and learn exactly how it works.
I wrote this code
my ($k, $v); my %data_all; my $dbh = DBI->connect('DBI:Sybase:#####', '#####', '#####', { RaiseE +rror => 1, AutoCommit => 1}); my $sql = "select Col1, Col2, Col3 from TABLE where Col4 = 1"; my $data_all = $dbh->selectall_hashref($sql, 1); while ( ($k,$v) = each %data_all ) { print "$k => $v\n"; }
I get this error.

DBI::st=HASH(0x1027b0)->_prepare(...): attribute parameter '1' is not a hash ref at /nms/site/perl-5.6.0/lib/site_perl/5.6.0/sun4-solaris/DBD/Sybase.pm line 95.

The document I read said that the second parameter is "the column to use as key, indicated by position".
I want Col1 to be the key.

Comment on selectall_hashref
Download Code
Re: selectall_hashref
by runrig (Abbot) on Jan 04, 2006 at 22:56 UTC
    The document I read said that the second parameter is "the column to use as key, indicated by position".

    What document was that? Look at the docs for fetchall_hashref, it works the same way (except you have to call prepare and execute), and has an example:

    $dbh->{FetchHashKeyName} = 'NAME_lc'; $sth = $dbh->prepare("SELECT FOO, BAR, ID, NAME, BAZ FROM TABLE"); $sth->execute; $hash_ref = $sth->fetchall_hashref('id'); print "Name for id 42 is $hash_ref->{42}->{name}\n";
    Update: I do also see this for fetchall_hashref, so you may be right (in some other version of documentation from mine), but try it both ways:
    The $key_field parameter can also be specified as an integer column nu +mber (counting from 1).
    Update: nevermind...see correct answer below.
Re: selectall_hashref
by smokemachine (Hermit) on Jan 04, 2006 at 23:01 UTC
    try while ( ($k,$v) = each %$data_all ) {
      Still not working.
      If I remove the second parameter selectall_hashref($sql) I recieve this error
      "Bad index while coercing array"
      With a second parm, either col location or col name I get the original error.
Re: selectall_hashref
by jZed (Prior) on Jan 04, 2006 at 23:32 UTC
    What is "1" as the second parameter to selectall_hashref()? That parameter should be the name of the column you want to use as a key, e.g. 'Col1', not an integer.
      I have tried that and it has the same result. The doc I read said you could indicate the column by location.
Re: selectall_hashref
by Errto (Vicar) on Jan 04, 2006 at 23:57 UTC

    I hate to be one to blame bugs in the system, but in this case it looks like it might be a bug in DBD::Sybase because I tried essentially the same code with DBD::Oracle and did not get an error like that. Make sure you have upgraded to the latest versions. Then try it using a named column as the index indstead of a number, just to better isolate the problem. If this doesn't fix it you might want to report the bug. (note that mpeppler is something of a regular here)

    However, please also follow smokemachine's advice because otherwise once this error is dealt with you fill still find that you don't have any data being returned.

      It's not a bug in DBD::Sybase - I just tried this and it worked for me.

      So I'm guessing (as others have) that the OP has an old version of DBI.

      Michael

Re: selectall_hashref
by McDarren (Abbot) on Jan 05, 2006 at 00:59 UTC
    Have you tried breaking it down and using fetchall_hashref instead? EG:
    my $sql = "select Col1, Col2, Col3 from TABLE where Col4 = 1"; my $dbq = $dbh->prepare($sql); $dbq->execute or die "Could not execute $sql:$!\n"; my $ref = $dbq->fetchall_hashref('Col1');
      Just tried doing the fetchall_hashref and got this error. "Can't locate object method "fetchall_hashref" via package DBI::st"
      Why does this stuff always happen to me?
        Which version of DBI do you have?

        From perldoc DBI::Changes, it appears that support for the fetchall_hashref method was added in version 1.20 (August 2001). If you don't have at least this version then you may want to grab the latest version.

        Cheers,
        Darren :)

        What version of DBI do you have?
        Why does this stuff always happen to me?

        Trust me - you're not the only one...

Re: selectall_hashref
by BaldPenguin (Friar) on Jan 05, 2006 at 06:32 UTC
    When I pull a query like that, I normally do it like:
    my $dbh = DBI->connect('DBI:Sybase:#####', '#####', '#####', { RaiseE +rror => 1, AutoCommit => 1}); my $sql = "select Col1, Col2, Col3 from TABLE where Col4 = 1"; my $data_all = $dbh->selectall_arrayref($sql, { 'Columns' => {} } ); for my $row ( @$data_all ) { print "$_ => $row->{$_}\n" for keys %$row; }
    Will that do what you are looking for.

    Don
    WHITEPAGES.COM | INC
    Everything I've learned in life can be summed up in a small perl script!
      I resorted back to my old way. Thank you for your help.
        That is because you must include the name of the column in uppercase letters, instead of 'Col1', use 'COL1'.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://521043]
Approved by Tanktalus
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (5)
As of 2014-04-20 02:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (485 votes), past polls