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

Dear fellow monks,

I want to know what is the simplest way to obtain the field names of a database table from DBI. I do it the following way but it looks rather messy: 
$sth = $dbh->prepare('select * from MyTable where 1=0')
       || die $dbh->errstr;

$sth->execute() || die $dbh->errstr;

$names_aref = $sth->{NAME};

[download]
Thanks in advance for any comments.

Replies are listed 'Best First'.
Re: Field names of database table from DBI
by plaid (Chaplain) on Jul 31, 2000 at 21:50 UTC
    The short answer is, no, there's not a cleaner, portable way to get a list of field names without doing some kind of select statement. If you don't mind sacrificing a little portability, you can use a driver-specific way to do it. For example, in DBD::mysql, you could do 
    my $sth = $dbh->prepare("LISTFIELDS $table");
$sth->execute;
my $names_aref = $sth->{NAME};

    [download]
    Check your appropriate DBD::driver manpage for more specific details if you choose to go this route.
[reply]
[d/l]
      I'm pretty sure LISTFIELDS doesn't work in my mysql. I've always used this: 
      my $s = $h->prepare("show fields from $table");
$s->execute; 
$s->bind_col(1, \$name);
push @names, $name while($s->fetch);

      [download]
[reply]
[d/l]
        SHOW COLUMNS FROM $table should also work 
        #!/usr/bin/perl

# connect to db
my $dbh = DBI->connect(bla..bla..bla);
my $sql_q = "SHOW COLUMNS FROM $table";
my $sth = $dbh->prepare($sql_q);
$sth->execute;

while (@row = $sth->fetchrow_array){
print"Field    Type     Null     Key     Default    Extra\n";
print"----------------------------------------------------\n";
print"$row[0]  $row[1]  $row[2]  $row[3] $row[4]    $row[5]\n";

}

        [download]
[reply]
[d/l]
Re: Field names of database table from DBI
by infinityandbeyond (Sexton) on Jul 31, 2000 at 21:38 UTC
    I know this is off topic (slightly), but if you're interested in simplicity, you can save typing || die $dbh->errstr; every time by adding to your DBI->connect. 
    my $dbh = DBI->connect("database","user","password","mysql", { RaiseEr
+ror => 1 });

    [download]
    The RaiseError flag will take care of that for you.
    There are other ways of getting the field names, but I'm not for certain that it is always supported.

    InfinityandBeyond
[reply]
[d/l]
Re: Field names of database table from DBI
by lhoward (Vicar) on Jul 31, 2000 at 23:43 UTC
    The DBI docs lists the table_info and tables call which get information about the tables in the DB. They are listed as experimental, so they may not be well supported by your DBD (and may even disapear in future versions of DBI). I've never used them, but they may be worth a try.
[reply]

Back to Seekers of Perl Wisdom