Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

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

by kleinbiker7 (Sexton)
on Mar 27, 2002 at 18:11 UTC ( #154741=perlquestion: print w/replies, xml ) Need Help??

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

I want to be able to list the field names and their description (varchar, number, date, etc) using Perl DBI.

But I cant do it! Is there a way to do it?

This is what I am trying to do right now:

$dbh = Call our algorithm that connects us to the DB. if (!$dbh) { print "Cannot connect to database: $DBI::errstr\n"; } $sth = $dbh->prepare(q{desc userinfo}); $sth->execute(); $dbh->disconnect; exit(1);

This is what I get:
DBD::Oracle::db prepare failed: ORA-00900: invalid SQL statement (DBD: error possibly near <*> indicator at char 1 in '<*>desc userinfo') at ./get-userinfo-fields.pl line 38. Can't call method "execute" on an undefined value at ./get-userinfo-fields.pl line 39.

Is there also a way in which I can get the field names in a SELECT clause? Is there a reserved word like, for example, if I wanted to search for all the tables in a DB, I would do "select table_name from all_tables"?

Thanks!
Robert

Replies are listed 'Best First'.
(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

    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.

      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
      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
      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.

Re: How do you get Perl::DBI to do a desc table_name?
by mpeppler (Vicar) on Mar 27, 2002 at 21:00 UTC
    Recent versions of DBI have table_info() and column_info() methods. This should allow you to fetch the information you look for in a database independant way.

    and now I've got to go check if DBD::Sybase actually supports this correctly :-)

    Michael

Re: How do you get Perl::DBI to do a desc table_name?
by vek (Prior) on Mar 27, 2002 at 18:59 UTC
    You can try the NAME and the TYPE DBI statement handle objects.

    Here's how you can grab column names:
    my $names = $sth->{NAME}; #$names is an array reference for (@$names) { print $_, "\n"; }
    Here's how you can grab the datatype of the columns:
    my $types = $sth->{TYPE}; #$types is an array reference for (@$types) { print $_, "\n"; }
    The last example returns an array of integer values, the value indicates the datatype (see ANSI X3.135 for a description).

      Two minor notes:

      First, you might want to be careful about the values returned by $sth->{NAME} if you care about case-sensitivity. The DBI doesn't make them consistent or modify them at all -- they're whatever the database returns. And what the database returns can be vendor specific. The NAME_uc and NAME_lc properties do what you'd expect.

      Second, I think the integer values returned from $sth->{TYPE} correspond to the DBI SQL type constants. So you can do:

      use DBI qw( :sql_types ); ... my $types = $sth->{TYPE}; foreach my $type ( @{ $types } ) { print "VARCHAR" if ( $type == SQL_VARCHAR ); print "DATETIME" if ( $type == SQL_DATETIME ); print "BOOLEAN" if ( $type == SQL_BIT ); print "FLOAT" if ( $type == SQL_FLOAT ); ... }

      Conveniently, most of them also map to the ODBC CLI specifications. :-)

      You can find the type-to-constant mapping in the 'dbi_sql.h' header file found in your architecture-dependent perl library tree -- for example, mine is in site_perl/5.6.1/i686-linux/auto/DBI/dbi_sql.h

      Chris
      M-x auto-bs-mode

Re: How do you get Perl::DBI to do a desc table_name?
by rbc (Curate) on Mar 27, 2002 at 19:21 UTC
    desc is a sqlplus command
    You don't need DBI to do what you want
    #!/usr/bin/perl -w use strict; my $cmd="sqlplus scott/tiger\@whatever"; open SQLPLUS, "|$cmd" or die "Ah poo!:$!\n"; print SQLPLUS<<DOIT; desc user_tables DOIT close SQLPLUS;

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2019-08-25 06:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?