Re: Displying field names and data

[Scott Deindorfer]

To get *just* the fieldnames ( which is sometimes difficult because it varies depending on the database implementation ) do this ( for MySQL ):

my $sql = qq~SHOW COLUMNS FROM table~;
my @cols = map "$_->[0]", @{$db->selectall_arrayref( $sql )};
print map "$_\n", @cols;

The good thing about this is it preserves the database order of the fields.

In Oracle you could do something like this:

my $sql = qq~SELECT column_name, data_type
FROM sys.user_tab_columns
WHERE table_name = 'table'
ORDER BY column_id~;

$meta = $db->selectall_arrayref( $sql );

you will surely need that "ORDER BY" clause to preserve the database order of the fields

if you want data in addition to fieldnames, simply use "$href = $sth->fetchrow_hashref", instead of "@row = $sth->fetchrow_array" like this:

$sql = qq~SELECT * FROM table LIMIT 1~;
my $sth = $db->prepare( $sql );
$sth->execute;

my $href;
while ( $href = $sth->fetchrow_hashref ) {
print map "$_: $href->{$_}\n", keys %$href;
}

the column names are in $_.

fetchrow_hashref() is pretty constant accross all db implementantions, but there is a problem...the fields are not returned in their database order, and sorting them alphabetically will obviously only make matters worse.

If you need the fields in database order, use the first method, and then use a second query to get the data.