>>>>> "Robb" == Robb Garrioch <[EMAIL PROTECTED]> writes:
Robb> Does anyone know of a DBI method ~or~ MySQL specific way that
Robb> would retrieve the column names of a MySQL database.table
Robb> dynamically within a Perl script using DBI? or Oracle if known.
Probably the most portable way to do this is by selecting * from a
table (you can use a condition like "WHERE NULL = NULL" to avoid
actually getting any data). After that, you can use $sth->{NAME}.
>From perldoc DBI:
| Statement Handle Attributes
|
| This section describes attributes specific to statement
| handles. Most of these attributes are read-only.
|
| [...]
|
| NAME (array-ref, read-only)
| Returns a reference to an array of field names for each
| column. The names may contain spaces but should not be truncated
| or have any trailing space. Note that the names have the letter
| case (upper, lower or mixed) as returned by the driver being
| used. Portable applications should use the NAME_lc entry
| elsewhere in this documentor the NAME_uc entry elsewhere in this
| document.
|
| print "First column name: $sth->{NAME}->[0]\n";
|
| NAME_lc (array-ref, read-only)
| Like the NAME entry elsewhere in this document but always returns
| lowercase names.
|
| NAME_uc (array-ref, read-only)
| Like the NAME entry elsewhere in this document but always returns
| uppercase names.
There are also database-specific ways of doing this. Note that there
was quite recently a discussion on "DESCRIBE" on this list, which is
basically what you're looking for.
In Oracle, you can query USER_TAB_COLUMNS and friends to get
information about columns in tables (see _Oracle8i Reference_,
(Release 2, 8.1.6, A76961-01)). In MySQL, you can do similar things
with "SHOW COLUMNS FROM $table":
| $ cat foo
| #!/usr/bin/perl -wl
|
| use strict;
| use DBI;
|
| my $dbh = DBI->connect("DBI:mysql:hostname=localhost:database=Tkil_CDs",
| "tkil_ro", "", { RaiseError => 1, PrintError => 1 } );
| my $sth = $dbh->prepare("SHOW COLUMNS FROM Artist");
| $sth->execute();
| $, = "|";
| print @{ $sth->{NAME} };
| while (my $cur = $sth->fetch())
| {
| print @$cur;
| }
| $sth->finish();
| $dbh->disconnect();
|
| $ ./foo
| Field|Type|Null|Key|Default|Extra
| id|int(10) unsigned||PRI|0|auto_increment
| name|varchar(127)||||
| sort_key|varchar(127)||MUL||
t.
