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:#####', '#####', '#####', { RaiseError => 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.
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 number (counting from 1).Update: nevermind...see correct answer below.
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.
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.
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');
Why does this stuff always happen to me?
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 :)
perl -MDBI -e 'print $DBI::VERSION . "\n"'
my $dbh = DBI->connect('DBI:Sybase:#####', '#####', '#####', { RaiseError => 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!