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

Replies are listed 'Best First'.
Re^2: DBI: checking if a table exists in a cross-DB manner? by radiantmatrix (Parson) on Dec 02, 2004 at 22:29 UTC
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") }` [download] 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)}`	[reply] [d/l]
Re^3: DBI: checking if a table exists in a cross-DB manner? by thor (Priest) on Dec 02, 2004 at 22:38 UTC
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`	[reply]
Re^2: DBI: checking if a table exists in a cross-DB manner? by herveus (Prior) on Dec 03, 2004 at 12:51 UTC
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	[reply]
Re^3: DBI: checking if a table exists in a cross-DB manner? by thor (Priest) on Dec 03, 2004 at 13:15 UTC
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`	[reply]
Re^4: DBI: checking if a table exists in a cross-DB manner? by herveus (Prior) on Dec 03, 2004 at 13:19 UTC
Howdy! OK. But SQLite does not appear to support it, to name one very useful non-big name. yours, Michael	[reply]
Re^5: DBI: checking if a table exists in a cross-DB manner? by thor (Priest) on Dec 03, 2004 at 13:44 UTC
Re^4: DBI: checking if a table exists in a cross-DB manner? by htoug (Deacon) on Dec 03, 2004 at 14:02 UTC
Depends on what you call 'the big names'. FWIW Ingres doesn't support it - to name a rather 'big' open-source RDBMS. Instead you have `MODIFY table TO TRUNCATE`. But that's not SQL-9X either.	[reply]
Re^5: DBI: checking if a table exists in a cross-DB manner? by thor (Priest) on Dec 03, 2004 at 21:13 UTC