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";
}

[download]
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.

Replies are listed 'Best First'.
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.

[reply]
      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

[reply]
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.
[reply]
      I have tried that and it has the same result. The doc I read said you could indicate the column by location.
[reply]
Re: selectall_hashref
by smokemachine (Hermit) on Jan 04, 2006 at 23:01 UTC
    try while ( ($k,$v) = each %$data_all ) {
[reply]
[d/l]
      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.
[reply]
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');

    [download]
[reply]
[d/l]
      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?
[reply]
        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 :)

[reply]
[d/l]
        What version of DBI do you have?
[reply]
        Why does this stuff always happen to me?

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

[reply]
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";

    [download]
    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).

    [download]
     Update: nevermind...see correct answer below.
[reply]
[d/l]
[select]
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;
}

    [download]
    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!
[reply]
[d/l]
      I resorted back to my old way. Thank you for your help.
[reply]
        That is because you must include the name of the column in uppercase letters, instead of 'Col1', use 'COL1'.
[reply]

Back to Seekers of Perl Wisdom