Checking for DB table existence using DBI/DBD

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

Replies are listed 'Best First'.
Re: Checking for DB table existence using DBI/DBD by snoopy (Curate) on Oct 13, 2005 at 23:59 UTC
The DBI table_info method is widely supported by DBD drivers and can be used to implement a preemptive test for the existance of a database table: `sub does_table_exist { my ($dbh,$table_name) = @_; my $sth = $dbh->table_info(undef, 'public', $table_name, 'TABLE'); $sth->execute; my @info = $sth->fetchrow_array; my $exists = scalar @info; return $exists; } foreach my $table ('foo', 'bar', 'items') { if (does_table_exist($dbh, $table)) { print "table $table exists\n"; } else { print "table $table does not exist\n"; } }` [download]	[reply] [d/l]
Re: Checking for DB table existence using DBI/DBD by rnahi (Curate) on Oct 13, 2005 at 21:22 UTC
You have two ways: either the one you used, or you can first get a table list (using DBI tables method) and then check the table name with grep. Something in between is shown in DBI Recipes.	[reply]
Re: Checking for DB table existence using DBI/DBD by ikegami (Patriarch) on Oct 13, 2005 at 21:39 UTC
I believe your code is equivalent to: `sub does_table_exist { my ($db_handle, $table_name) = @_; my $query_handle = $db_handle->prepare("DESC $table_name"); $query_handle->{RaiseError} = 0; $query_handle->{PrintError} = 0; return $query_handle->execute(); }` [download] You didn't take into account PrintError. The above clears RaiseError and PrintError, but only only for the sth created to execute DESC. By the way, you're not quoting the table name, so still have a bug. You'll probably save yourself some trouble using rnahi's solution.	[reply] [d/l]
Re^2: Checking for DB table existence using DBI/DBD by shemp (Deacon) on Oct 13, 2005 at 21:46 UTC
Describe doesn't need the table name quoted, at least not with the MySQL DBD. I use the most powerful debugger available: print!	[reply]
Re^3: Checking for DB table existence using DBI/DBD by ikegami (Patriarch) on Oct 13, 2005 at 22:44 UTC
It does if the table name has special characters such as spaces in it, right?	[reply]
Re^4: Checking for DB table existence using DBI/DBD by shemp (Deacon) on Oct 13, 2005 at 23:11 UTC
Re: Checking for DB table existence using DBI/DBD by herveus (Prior) on Oct 13, 2005 at 23:28 UTC
Howdy! Actually, it assumes a third thing, namely that the database supports a 'DESC' command in its flavor of SQL. yours, Michael	[reply]
Re: Checking for DB table existence using DBI/DBD by EvanCarroll (Chaplain) on Oct 13, 2005 at 22:05 UTC
A select is probably the fastest, and is more apt to being optimized away. `dbh->do(q{select 'true' from tests limit 0;})` It should return 0e0 if table exists; or an error if it doesn't. Evan Carroll www.EvanCarroll.com	[reply] [d/l]
Re^2: Checking for DB table existence using DBI/DBD by pajout (Curate) on Oct 14, 2005 at 07:55 UTC
Clever idea, but somebody will be confused reading your code - it is not evident, why you selects...	[reply]
Re^3: Checking for DB table existence using DBI/DBD by xdg (Monsignor) on Oct 14, 2005 at 13:49 UTC
Yes, but a one-line comment would solve that. -xdg Code written by xdg and posted on PerlMonks is public domain. It is provided as is with no warranties, express or implied, of any kind. Posted code may not have been tested. Use of posted code is at your own risk.	[reply]
Re^2: Checking for DB table existence using DBI/DBD by danb (Friar) on Oct 14, 2005 at 16:04 UTC
In Interchange, we use a similar query to get all the column names: `select * from tests where 6*9=42` `limit 0` isn't portable to all databases, and neither is `where false`, but the question of life, the universe, and everything is. :) --Dan	[reply] [d/l] [select]
Re: Checking for DB table existence using DBI/DBD by g0n (Priest) on Oct 14, 2005 at 07:34 UTC
Oddly enough, I had a requirement to do the same yesterday, and found that this works: `use DBI; use strict; my $db=DBI->connect("DBI:SQLite:dbname=testdb","",""); my $sth = $db->prepare("select * from nosuchtable"); if ($sth){print "Table exists\n"} else{print "No such table\n"}` [download] (i.e. without doing an execute). The code displays a warning from SQLite that the table doesn't exist, and the true test for `$sth` fails. Of course, this might be a bad way (if it is, I'd like to know so I can change it in my code). -------------------------------------------------------------- $perlquestion=~s/Can I/How do I/g;	[reply] [d/l] [select]
Re: Checking for DB table existence using DBI/DBD by McDarren (Abbot) on Oct 14, 2005 at 07:56 UTC
This is something that I find myself having to do quite often, and I've adopted an approach that is very similar to yours, eg: `# Check if this table exists on the remote site eval { $sql = "DESC $thisTable;"; $dbq = $ibisSite->prepare($sql); $dbq->execute; }; if ($@) { return($dbq->errstr); }` [download] Interesting to see some of the other responses you have. In particular, the `table_info` method by [id://snoopy] looks quite clean and transportable. --Darren	[reply] [d/l] [select]
Re: Checking for DB table existence using DBI/DBD by pajout (Curate) on Oct 14, 2005 at 07:59 UTC
Slightly OT: You have not eval, if you have RaiseError=0, you can check $DBI::err and $DBI::errstr after crucial command.	[reply]
Re: Checking for DB table existence using DBI/DBD by JamesNC (Chaplain) on Oct 14, 2005 at 11:47 UTC
Learn about EXISTS ( you need to know it so you can do an update/insert statement in the same pass anyway ) On MSSQL/Sybase I would do something like: `IF EXISTS ( SELECT 1 FROM sysobjects where name = 'some_table' and typ +e = 'U' ) -- do this if true ( maybe an update here ) SELECT 1 ELSE -- do this if false ( maybe an insert here ) SELECT 0` [download] You need to consult the docs as to how your database implements EXISTS which normally operates on a subquery and returns a boolean. JamesNC	[reply] [d/l]


Pathologically Eclectic Rubbish Lister
	PerlMonks