Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

(Ovid) Re: How do you get Perl::DBI to do a desc table_name?

by Ovid (Cardinal)
on Mar 27, 2002 at 18:19 UTC ( #154746=note: print w/replies, xml ) Need Help??


in reply to How do you get Perl::DBI to do a desc table_name?

Most databases have system tables that contain that information. You'll have to consult your documentation. However, to find just the fieldnames from a table, this hack should do it (though I probably wouldn't use something like this):

my $dbh = DBI->connect( $connection,$user, $pass,{RaiseError => 1}); my $sql = 'SELECT TOP 1 * FROM someTable'; my $sth = $dbh->prepare( $sql ); $sth->execute; my $fields = $sth->fetchrow_hashref; $sth->finish; my @fields = keys %$fields;

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

Replies are listed 'Best First'.
Re: (Ovid) Re: How do you get Perl::DBI to do a desc table_name?
by Rhose (Priest) on Mar 27, 2002 at 20:03 UTC
    Yep, Ovid is dead on here -- the information for which you are looking is stored in the Oracle data dictionary.

    I hope the following example helps. (Note: There are more fields in dba_tab_columns... you might want to look around for other goodies.) Make sure to set the ORAUSER, ORAPASS, and ORATNS constants to values appropriate to your system. (Oh, and you may not have a SCOTT.EMP table. *Smiles*)

    #!/usr/bin/perl -w use strict; #-- Use modules use DBD::Oracle; use DBI; #-- Define local constants use constant TRUE => 1; use constant FALSE => 0; use constant ORAUSER => 'my_ora_user'; use constant ORAPASS => 'secret_password'; use constant ORATNS => 'ora_db_tns_name'; #-- Define local variables my $gDBHandle; my $gSQLCmd; my $gSQLHandle; my $gTabName; my $gTabSchema; my @gFields; #-- Initialize local variables $gSQLCmd = 'SELECT column_name, ' . ' nullable, ' . ' data_type, ' . ' data_length ' . ' FROM dba_tab_columns ' . ' WHERE owner = ? ' . ' AND table_name = ? ' . ' ORDER BY column_id '; $gTabName = 'EMP'; $gTabSchema = 'SCOTT'; #-- Connect to the database $gDBHandle = DBI->connect ( 'dbi:Oracle:' . ORATNS, ORAUSER, ORAPASS, { AutoCommit => FALSE, PrintError => FALSE, RaiseError => FALSE, } ) || die 'Could not connect to Oracle ['.$DBI::errstr.' - '.$DBI::er +r.']'; #-- Get the data $gSQLHandle = $gDBHandle->prepare($gSQLCmd) || die 'Error with SQL statement ['.$DBI::errstr.' - '.$DBI::err.']' +; $gSQLHandle->execute($gTabSchema, $gTabName) || die 'Error with SQL statement ['.$DBI::errstr.' - '.$DBI::err.']' +; while (@gFields = $gSQLHandle->fetchrow_array) { print $gFields[0],"\t",$gFields[1],"\t",$gFields[2],"\t",$gFields[3] +,"\n"; } #-- Close the database connection $gDBHandle->disconnect(); #-- Exit exit; #-- End of Example
Re: (Ovid) Re: How do you get Perl::DBI to do a desc table_name?
by trs80 (Priest) on Mar 28, 2002 at 04:38 UTC
    DBI::Shell has some excellent code in it that I use for one of my utility scrips sub db_describe available in the newer distributions of DBI
Re: (Ovid) Re: How do you get Perl::DBI to do a desc table_name?
by kleinbiker7 (Sexton) on Mar 27, 2002 at 18:32 UTC
    This doesnt seem to work for me. I think select top only works for SQL Server, and I am using Oracle. Any other suggestions? Thanks! Robert

      Just drop the TOP 1 from the SQL. I added that to prevent the DB from selecting all records and doing to much work. However, having the $sth->finish call at the end should minimize the overhead by merely finishing up the statement handle without fetching the subsequent rows.

      Of course, Oracle should support some form of the TOP syntax, but I don't know Oracle.

      Cheers,
      Ovid

      Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

        Just execute a query with a false where clause:
        select * from ... where 1=0
        This will return an empty result set, but you'll still get all the column headers, names, etc.

        Michael

        OK. I think I see now. Another question: How do you get the types, like varchar2(2000), number(3), date, etc? Basically I want to get all the results from a DESC operation into my Perl script. Thanks!

        Robert

        While I am not familiar with the TOP syntax, I bet ROWNUM can be used to do the same. ROWNUM is a the number of a row as it was retrieved from the table (this differs from the display order) -- to limit results to 10 rows, you can use WHERE ROWNUM <= 10. (Please note: this takes place before sorting, so it is no good for getting the "top 10" and such.)

        SELECT * FROM scott.emp WHERE ROWNUM = 1;

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://154746]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (4)
As of 2019-08-23 13:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?