I dunno about mysql, but in Oracle you can do the following:
select column_name from user_tab_columns where table_name = 'blah'
where 'blah' is the name of the table whose columns you need to
have. Make sure that your table name is in all upper case, because this
is how Oracle keeps them in this column.
Once you run through this query, you should be able to step through it and
get each column. You can also select the column length, it's nullable
attributes, and other stuff from it as well... at a SQL prompt, describe
the view to see what it holds...
user_tab_columns is an administrator view in Oracle, but normal users
should be able to select from it.
--K
On 23 Feb 2001, Tony Foiani wrote:
#>>>>> "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.
#
--
Ken Speich
Manager, Network Surveillance Tools
[EMAIL PROTECTED] 301-598-0500 x2637
"How can you shoot the devil in the back? What if you miss?"
