Result set into array

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

Replies are listed 'Best First'.
Re: Result set into array by imp (Priest) on Nov 19, 2006 at 02:41 UTC
Your question specified that you would like the results in an array, but the example output you provided is a hash, and you used selectall_hashref in your earlier reply. If you would like to fetch all of the rows and have them in a hash then you need to specify which column should be used, as in this example: `my $hoh = $dbh->selectall_hashref($sql, $index_column);` [download] In this example the structure would be as follows: `$hoh = { 1 => {id => 1, col2=>"abc", col3=>"def", col4=>"ghi"}, 2 => {id => 2, col2=>"lmn", col3=>"opq", col4=>"rst"}, }` [download] Keep in mind that as it is returning a hashref the order of the rows will not be preserved. To preserve the order you would use selectall_arrayref as follows: `my $aoa = $dbh->selectall_arrayrefref($sql);` [download] In this example the structure would be as follows: `$aoa = [ [1,"abc", "def", "ghi"], [2,"lmn", "opq", "rst"], ]` [download] If you would like to preserve both the order and the column names, then you can use the Slice option for selectall_arrayref as follows: `my $aoh = $dbh->selectall_arrayref($sql, {Slice => {}});` [download] In this example the structure would be as follows: `$aoh = [ {id => 1, col2=>"abc", col3=>"def", col4=>"ghi"}, {id => 2, col2=>"lmn", col3=>"opq", col4=>"rst"}, ]` [download]	[reply] [d/l] [select]
Re: Result set into array by jZed (Prior) on Nov 19, 2006 at 00:46 UTC
Although you don't say, I assume you are using DBI. Try something like `my $hash = $dbh->selectall_hashref( "Select id, col2, col3, col4 From TableA". 'id' );` [download]	[reply] [d/l]
Re^2: Result set into array by novicepl (Initiate) on Nov 19, 2006 at 01:08 UTC
yes, I am using DBI. getting error msg "Can't locate object method "selectall_hashref" via package "DBI::st" at ./Test1.pl line 166. " `$sth = $dbconn->prepare($sql_statement); $sth->execute() or die "Can not execute: " . $sth->errstr; $sth->{RaiseError} = 1; my $hash = $sth->selectall_hashref($sql_statement); #this is line +166 $sth->finish; $dbconn->commit(); $dbconn->disconnect();` [download]	[reply] [d/l]
Re^3: Result set into array by shenme (Priest) on Nov 19, 2006 at 02:05 UTC
Guessing from reading the docs (!) but it looks like `selectall_hashref()` is executed using the DB handle, so your code might better be: `my $hash = $dbconn->selectall_hashref($sql_statement);` [download] You may have been led astray by the talk of preparing a statement ahead of time, but even then you still use the DB handle. `my $sth = $dbconn->prepare($sql_statement); . . . . . . . my $hash = $dbconn->selectall_hashref($sth);` [download] As for setting the valuable RaiseError flag, it looks like you'll have to do that on the DB handle when using `selectall_hashref`, perhaps something like: `{ local $dbconn->{RaiseError} = 1; my $hash = $dbconn->selectall_hashref($sql_statement); . . . . . . . }` [download] Go back and read the DBI docs whenever something goes 'wrong' - sometimes it takes me a couple "read again"s before it sinks in to my flat file head.	[reply] [d/l] [select]


Just another Perl shrine
	PerlMonks