Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

DBI: checking if a table exists in a cross-DB manner?

by radiantmatrix (Parson)
on Dec 02, 2004 at 19:54 UTC ( [id://411923]=perlquestion: print w/replies, xml ) Need Help??

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

I am aware that many RDBMS packages provide a mechanism to check for the existance of a table via SQL. I've also read if table exists (DBI) on the subject, but it does not provide an answer for me. I need to examine whether or not a table exists in a way that is portable to all (or at least most) DBI-able RDBMS.

At least, that's what I think I need to do... My logic looks like this in psuedo-code:

sub _recreate_table { my $table_name = shift; drop_table($table_name) if table_exists($table_name); create_table($table_name, %schema); }

The reason for this is that I need to purge all records from a table, and then insert data from a flat file into a cleanly-created table. When the files change, %schema changes as well. DELETE FROM $table_name is too slow (approx 1.5M records per table).

This works, but assumes that any error means the table just didn't exist:

$dbh->{RaiseError} = 1; eval { $sth->do("DROP TABLE $table_name") }; if ($@) { ## Do nothing }

That's a Bad Thing™, IMO, partly because I'd like to provide the option of prompting the user with something like "This table exists, are you sure you wish to drop it?" when the table is already there. Any ideas on a better way to approach this challenge, and one that will work with pretty much any DB supported by DBI?

radiantmatrix
require General::Disclaimer;
s//2fde04abe76c036c9074586c1/; while(m/(.)/g){print substr(' ,JPacehklnorstu',hex($1),1)}

Replies are listed 'Best First'.
Re: DBI: checking if a table exists in a cross-DB manner?
by simonm (Vicar) on Dec 02, 2004 at 20:11 UTC
    I've got some functionality along this line in DBIx::SQLEngine -- feel free to copy out the relevant bits or use the module as a whole:
    my $sqldb = DBIx::SQLEngine->new( $dbh ); print $sqldb->detect_table($tablename) ? 'Y' : 'N';

    Basically I just try to select from the table and see if it succeeds; depending on which kind of database is in use, a different syntax is used to make a trivial query like "select * from $tablename limit 1" or "select * from $tablename where 1 = 0".

    See the sql_detect_table() method in various "Driver::Foo" subclasses in the DBIx::SQLEngine distribution for the different queries.

Re: DBI: checking if a table exists in a cross-DB manner?
by htoug (Deacon) on Dec 03, 2004 at 11:29 UTC
    You should use the $dbh->table_info(...) method.

    It will return a statement handle, that iterates the table(s) you define, thus alloowing easy check whether a table exists.

    Your call would be something like

    $sth=$dbh->table_info("", $schema, $table_name, "TABLE"); if ($sth->fetch) { # table exists } else { # doesn't }
    This will work on all DBD's (as soon as the authors have implemented the table_info method - most have). If yours doesn't work, then please contact your DBD-author and help him get it done.
      Thank u, is very simple and works!
Re: DBI: checking if a table exists in a cross-DB manner?
by thor (Priest) on Dec 02, 2004 at 20:05 UTC
    DELETE FROM $table_name is too slow
    That's why we have TRUNCATE TABLE $table_name. Delete is a logged operation, whereas truncate is not. You should see a significant speedup in truncating over deleting. I know that this doesn't address the issue of the schema changes that you talk about, but that's what ALTER TABLE is for...:).

    thor

    Feel the white light, the light within
    Be your own disciple, fan the sparks of will
    For all of us waiting, your kingdom will come

      On its face, your suggestion seems great (and it is much faster than DELETE FROM, which is useful to me for another project -- thanks). However, it still doesn't address the case of a table that doesn't already exist.

      So again, I'm left with:

      eval { $dbh->do("TRUNCATE TABLE $table_name") }

      With no reliable way to determine if an error means the table didn't exist or if it means there was actually a problem. Any ideas on how to solve for that case?

      radiantmatrix
      require General::Disclaimer;
      s//2fde04abe76c036c9074586c1/; while(m/(.)/g){print substr(' ,JPacehklnorstu',hex($1),1)}

        Let's see what we can do. What about "select * from $table_name where 9=0"? For most db drivers that I know, that will either fail in the prepare or the execute if the table doesn't exist. You should be able to trap that error and act accordingly. Moreover, it won't actually fetch any data, so it's good from a data throughput standpoint. :)

        thor

        Feel the white light, the light within
        Be your own disciple, fan the sparks of will
        For all of us waiting, your kingdom will come

      Howdy!

      'TRUNCATE TABLE' is not, so far as I can tell, standard SQL, and isn't necessarily going to be available. The index to the SQL-92 standard has no entry for 'truncate'. I don't know about newer SQL standards, but SQL-92 seems to be still widely used as a basis.

      yours,
      Michael
        You're absolutely correct. But AFAIK, all of the big names support it. I've yet to use a database that didn't have it.

        thor

        Feel the white light, the light within
        Be your own disciple, fan the sparks of will
        For all of us waiting, your kingdom will come

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (3)
As of 2024-03-19 04:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found