sid_com sid_com - 4 months ago 29
Perl Question

DBI: selectall_arrayref and columnnames

When I fetch the data this way is it possible then to access the column names and the column types or do I need an explicit

to reach this?

use DBI;

my $dbh = DBI->connect( ... );
my $select = "...";
my @arguments = ( ... );

my $ref = $dbh->selectall_arrayref( $select, {}, @arguments, );


I would do it this way:

my $sth = $dbh->prepare( $select );
$sth->execute( @arguments );
my $col_names = $sth->{NAME};
my $col_types = $sth->{TYPE};
my $ref = $sth->fetchall_arrayref;
unshift @$ref, $col_names;

rjh rjh

The best solution is to use prepare to get a statement handle, as you describe in the second part of your question. If you use selectall_hashref or selectall_arrayref, you don't get a statement handle, and have to query the column type information yourself via $dbh->column_info (docs):

my $sth = $dbh->column_info('','',$table,$column);  # or $column='' for all
my $info = $sth->fetchall_arrayref({});
use Data::Dumper; print Dumper($info);

(specifically, the COLUMN_NAME and TYPE_NAME attributes).

However, this introduces a race condition if the table changes schema between the two queries.