Column names when using selectall_arrayref

How do I get the column names as a list to match the order of the
rows returned when using select/fetchall_arrayref and using an ARRAY
slice? I'm not having luck finding it in the docs.

I don't know the column names ahead of time -- I'm passed a query and
want to return the data in the column order specified in the query.
And also return the list of column names.

Thanks,


--
Bill Moseley
moseley@hank.org

Re: Column names when using selectall_arrayref

------=_Part_40542_5764019.1146722293429
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 5/3/06, Bill Moseley wrote:

> How do I get the column names as a list to match the order of the
> rows returned when using select/fetchall_arrayref and using an ARRAY
> slice? I'm not having luck finding it in the docs.
>
> I don't know the column names ahead of time -- I'm passed a query and
> want to return the data in the column order specified in the query.
> And also return the list of column names.
>

Doesn't fetchall_arrayref return you a reference to an array of rows, each
row of which is itself an array. So, you fall back on $sth->{NAMES} for th=
e
list of column names.

Your subject line only mentions selectall_arrayref - so maybe you're really
planning to use that. If so, you need to read the fine print about if
selectall_arrayref being passed a prepared statement handle - and then use
that and $sth->{NAMES} again.


--
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_40542_5764019.1146722293429--

Re: Column names when using selectall_arrayref

Hi Bill,

"Bill Moseley" wrote:
> How do I get the column names as a list to match the order of the
> rows returned when using select/fetchall_arrayref [...] I'm not
> having luck finding it in the docs.

See the "Statement Handle Attributes" section of the DBI docs:
http://search.cpan.org/~timb/DBI-1.50/DBI.pm#Statement_Handl e_Attributes

Although I'm not sure if this could work for $dbh->selectall_arrayref(),
if you didn't mind preparing and executing the statement handle in
advance (and your backend database's DBD driver supports it -- MySQL
does), then you can use the NAME attribute of the statement handle to
get back the actual column names that will be returned in the query
result *before* you actually fetch any rows (but after you execute the
query) like this:

# assuming a little test db like this:
create table test (
id int not null,
name text,
age int,
primary key (id)
);
insert into test
(1, 'david', 39),
(2, 'goliath', 1764)
;

You can prepare your query and execute it but not fetch anything yet:

my $sth=$dbh->prepare("select * from test");
$sth->execute or die $sth->errstr;

and now the statement handle can tell you its ->{NAME}'s:

print Dumper($sth->{NAME});

as an arrayref of column names, in the order that they'll be returned
(once you fetch them):

$VAR1 = [
'id',
'name',
'age'
];

and here comes the fetched data:

print Dumper($sth->fetchall_arrayref)'

$VAR1 = [
'id',
'name',
'age'
];
$VAR1 = [
['39', 'david'], ['1764', 'goliath']
];

> ...and using an ARRAY slice?

d'oh! You like throwing those curve balls don't you?

> I don't know the column names ahead of time -- I'm passed a query and
> want to return the data in the column order specified in the query.
> And also return the list of column names.

Okay well, now that you know the names in the query *before* you sliced
it up at fetch-time with an arrayref, so now you just need to slice the
NAME's arrayref the same way. If, for instance, you passed an arrayref
slice to get back the third and second columns, in that order:

my @names = @{$sth->{NAME}};
my @slice = (2,1);

print Dumper (@names[@slice]);

$VAR1 = 'age';
$VAR2 = 'name';

the same way the fetched data got sliced:

print Dumper($sth->fetchall_arrayref(\@slice))'

$VAR1 = [
['39', 'david'],
['1764', 'goliath']
];

Hope this helps!

-dave