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

How do I check to see if a MySQL Database TABLE exists?

My Database connects when I use $dbh as I have it pre-declared in a global variable.


Would it be something like this:?
$sth = $dbh->prepare (qq{ SELECT * FROM table_name });
    $sth->execute();
    $row = $sth->fetchrow_hashref();
    $sth->finish();
if ($sth) {
#exists
} else {
# does NOT exist
}

[download]
Is that how to do it?

If I do that, and the table does NOT exist, won't it cause an Internal Error?

thx,
Richard

Replies are listed 'Best First'.
Re: MySQL Database Check...
by steves (Curate) on Jan 02, 2003 at 05:19 UTC

    DBI has some newer table methods (tables and table_info) that may be useful here. Short of that, you can either eval the prepare call or set the DBI RaiseError attribute to false and check for errors.

    Search the DBI docs above for RaiseError and you'll find it's all very well documented. Support for the table methods may be database specific ... check the DBD::mysql docs for specifics.

[reply]
Re: MySQL Database Check...
by cchampion (Curate) on Jan 02, 2003 at 10:03 UTC
    You can just run a grep on the result of the DBI tables method.
    Besides being fast, it is also portable, since it is not MySQL specific.
    The core instruction you need is 
    print "table exists" if grep {"tablename" eq $_ } $dbh->tables;

    [download]
    Below, there is a complete example.

    #!/usr/bin/perl -w
use DBI;
use strict;

my $dbh = DBI->connect("DBI:mysql:mysql;host=localhost",
        "username","password", {RaiseError => 1})
        or die "can't connect\n";


#
# Checks for a table in a given database
#
sub table_exists {
    my ($dbh, $table) = @_;
    return grep {$table eq $_} $dbh->tables
}

my $mytable = "user";

if (table_exists($dbh,$mytable)) {
    print "table $mytable exists";
}
else {
    print "table $mytable does not exist";
}

$dbh->disconnect();

    [download]
[reply]
[d/l]
[select]
Re: MySQL Database Check...
by tachyon (Chancellor) on Jan 02, 2003 at 05:21 UTC

    The 'SHOW TABLES' MySQL command will return a list of all the tables in your database. No error checking for brevity. Obviously you can just load this into a hash for a quick lookup. 

    require DBI;
my $dbh = DBI->connect( ..blah.. );
my $sth = $dbh->prepare('SHOW TABLES');
$sth->execute();
while (my ($table) = $sth->fetchrow_array ) {
    print "$table\n";
}

    [download]

    cheers

    tachyon

    s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[reply]
[d/l]

      Maybe this works in mySQL, but as a general rule it does not work in DBI because SHOW TABLES is not SQL. I use Oracle and in Oracle, SHOW TABLES is a SQL*Plus command but does not work via DBI prepare/execute calls. I believe this is why the DBI table methods were added -- to insulate these database specifics and allow table queries to exist across all databases in a consistent manner.

[reply]

        I did specify it was MySQL specific (and yes it does work ;-) but as you state using the DBI table methods or even just wrapping in an eval or changing RaiseError will be more portable options.

        cheers

        tachyon

        s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[reply]
        While you are 100% correct steves, the OP did specifically state that this would be for MySQL. In which case tachyon was not wrong in mentioning 'SHOW TABLES' IMHO.

        -- vek --
[reply]

Back to Seekers of Perl Wisdom