Getting a list of columns from a MySQL Table

[c]

I did a PM search on a similar title and what came up seemed to be even past what little I know of working with MySQL and DBI.

I am currently using:

## TEST get a list of tables
my @tables = $dbh->tables();

for my $i(sort @tables) {

  print uc $i . "\n\n";

  my $sth = $dbh->prepare("LISTFIELDS $i");

  $sth->execute;

  $sth->finish;

  sleep 1;

}

The code seems good up to the point that I try and grab the column names and list them out. Its when I make the $sth execute that it returns empty. I am sure that my table does contain columns. The code I have is almost identical to the example in the MySQL DBD README.

thanks -c

[mp]

Try using "DESCRIBE table" instead of "LISTFIELDS table". This will give you column name, type, allownull, key, default, and extra. For more information on what you get, you may want to try the following inside your for loop:

use Data::Dumper;
my $sth = $dbh->prepare("DESCRIBE $i");
$sth->execute;
while(my $info = $sth->fetchrow_hashref) {
  print Dumper($info);
}

[c]

Thanks, I saw the fetchrow_hashref routine and tried something like:

for my $i(sort @tables) {

  print uc $i . "\n\n";

  ## get a list of columns for the table
  my $sth = $dbh->prepare("LISTFIELDS $i");

  $sth->execute;

  my $row = $sth->fetchrow_hashref();

  for my $header(keys %{$row}) {

    print "  $header\n";

  }

  $sth->finish;

  sleep 1;

}

but still no dice. I'll try your code and see what it yields. Thanks! -c

[Aristotle]

Did you check $sth->errstr (or generally $DBI::errstr)? There's nothing in your example that checks for errors; do you use RaiseError?

(I think you needn't call finish() either there, btw, since $sth goes out of scope at the end of the loop anyway, but that's beside the question of course.)

Makeshifts last the longest.

[Moonie]

You may want to look at this link here.

[ehdonhon]

Take a look at DBIx::DBSchema.

[seattlejohn]

After you call $sth->execute, you should be able to get the number and names of columns via
  $sth->{NUM_OF_FIELDS}
and
  $sth->{NAME}->[$n]
where $n is an index from 0..$sth->{NUM_OF_FIELDS}-1