in reply to Reading DB

Greetings all,
Let me preface this post with the fact that I do not work with SQL2000 (Im more of an opensource proponent).
However if you are using DBI you can get the column names of any particular table: 
use strict;
use DBI;
my $dbh = DBI->connect('DBI:database_type:database_name','user','passw
+ord') || die DBI->errstr;
my $sth = $dbh->prepare("SELECT * FROM table_name WHERE 0=1") || die $
+dbh->errstr;
$sth->execute() || die $sth->errstr;
my @column_names = @{$sth->{NAME}};
$sth->finish();
$dbh->disconnect();

[download]
Now @column_names will contain the column names for your table.
Im sure it can be done a lot more elegantly then I have illustrated here, but thats a start.
As for a list of tables with the database...I have no suggestions. Im not sure about the behaviour of SQL2000 or even if there is support in DBI.
Hope that helps get you at least part of the way to your solution.
-injunjoel
UPDATE Added the $sth->execute();
Updated prepared query for portability(though I would hate to be the developer without access to LIMIT... InterBase anyone?)
Thanks to (jZed & mpeppler) for catching that.
-injunjoel

Replies are listed 'Best First'.
Re: Re: Reading DB
by mpeppler (Vicar) on Nov 06, 2003 at 21:44 UTC
    While you should be commended to point at a lowest common denominator solution it should be pointed out that the LIMIT keyword is non-standard (and your code snippet is also missing the $sth->execute call :-).

    It shouldn't be necessary to even fetch a single row for this. A WHERE clause that evaluates to false achieves this with minimal impact on the server: 

    use strict;
use DBI;
my $dbh = DBI->connect('DBI:database_type:database_name','user','passw
+ord') || die DBI->errstr;
my $sth = $dbh->prepare("SELECT * FROM table_name WHERE 0=1") || die $
+dbh->errstr;
$sth->excecute || die $sth->errstr;
my @column_names = @{$sth->{NAME}};
$sth->finish();  # may be unnecessary
$dbh->disconnect();

    [download]

    Update Fixed typo in WHERE clause...

    Michael

[reply]
[d/l]
[select]
      The snippet doesn't work I can't print anything thing and WHERE 0=1 1 is not ODBC friendly.
[reply]
        Oops... remove that trailing "1" and you should be fine.

        Michael

[reply]
Re: Re: Reading DB
by jZed (Prior) on Nov 06, 2003 at 21:40 UTC
    In general your code is a good way to approach getting column names, but you get a bit tripped up on some of the specifics at least if the code has any hope of being portable. 

    my $sth=$dbh->prepare("SELECT * FROM table_name LIMIT 1")...
my @column_names = @{$sth->{NAME}};

    [download]

    1. LIMIT is not standard SQL and doesn't exist for many RDBMSs, unfortunately even "WHERE 1=0" isn't usable everywhere

    2. $sth->{NAME} is not necessarily valid until after an execute.

    3. It's best to specify $sth->{NAME_lc} or $sth->{NAME_uc} since different databases store the column names differently even if they ignore the case in actual SQL.

[reply]
[d/l]

In Section Seekers of Perl Wisdom