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); 
}

[download]

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
}

[download]

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)}

In reply to DBI: checking if a table exists in a cross-DB manner? by radiantmatrix

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.