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

Is it possible to get the fields of a table without executing a query first. You need a statement handle to use  $sth->{NAME} but what if I don't want to execute a query and just want the field names. Should I execute a  DESC Tablename or a trivial select as my query or is there a function I can call to get the fields in a table without any data?

Replies are listed 'Best First'.
Re: obtain fields in table without query
by Abigail-II (Bishop) on Jul 08, 2002 at 12:44 UTC
    In general, to get the names of a table, you do have to contact the database and ask for the names. And that means, doing a query. Somehow, the information has to get to get to you.... Unless of course you have the names by some other means, or if you have cached them. But you do not speak of such a thing, which means you got to make a query.

    Abigail

[reply]
Re: obtain fields in table without query
by RMGir (Prior) on Jul 08, 2002 at 13:04 UTC
    From perldoc DBI, for DBI-1.28: 
           ""table_info"" NEW
           Warning: This method is experimental and may change.

             $sth = $dbh->table_info( $catalog, $schema, $table, $type
+ );
             $sth = $dbh->table_info( $catalog, $schema, $table, $type
+, \%attr );
             $sth = $dbh->table_info( \%attr ); # old style

           Returns an active statement handle that can be used to
           fetch information about tables and views that exist in
           the database.

    [download]

    --
    Mike
[reply]
[d/l]
Re: obtain fields in table without query
by hakkr (Chaplain) on Jul 08, 2002 at 12:51 UTC
    Thanks I thought as much.
    Obviously the data has to get to me but I'd prefer if i could ask for it directly instead of having the overhead of doing a meaningless select.
    Seems silly doing a query just to throw away the data. Guess I'll just have to make my query as trivial as possible. Some sort of wrapper for DESCRIBE queries might be nice to see in DBI to solve this.

    UpdateCheers Mike creating a statement without a query like that sounds what I'm looking for

[reply]
      You can run a "desc table" query using DBI, although you will get some different results on different databases.

      MySQL returns a row of data for each column in the table -- (name, datatype, NULL (YES|NO), key, default, auto-increment).

      Oracle sees this as an invalid SQL statement.
[reply]
Re: obtain fields in table without query
by valdez (Monsignor) on Jul 08, 2002 at 15:57 UTC

    An other solution can be DBIx::SystemCatalog, which I discovered uses DBI and the same method pointed out by RMGir.

    OT: while searching, I discovered also SQL::Catalog, that seems interesting

    Ciao, Valerio

[reply]

Back to Seekers of Perl Wisdom