DBI "drop table" next obstacle

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

first of all thanxs for all the help given in the last thread. it is working perfectly (almost). the next problem is that if i want to drop the table through DBI

connect to the db...

$i = $dbh -> prepare(drop table X);
$i -> execute();
[download]

it will do it if there is a Table X, but if there isn't one it checks in MySql, MySql returns error and my perl code stops(what i certainly don't want). how can i tell perl to check if that table exists and if exists than to drop it,or if it doesn't just to move on. thnx . Robert

Comment on DBI "drop table" next obstacle Download Code

Replies are listed 'Best First'.
Re: DBI "drop table" next obstacle by Corion (Patriarch) on Apr 13, 2008 at 17:39 UTC
MySQL has a special syntax that will tie your soul to MySQL but otherwise work: `drop table X if exists;` [download] Personally, I prefer a Perl solution to this problem: `sub tableExists { my ($dbh, $tableName) = @_; my $exists = 0; #my $cursor = $this->{dbh}->table_info('%','%',$tableName,'TABLE') +; my $res = eval { my $cursor = $dbh->prepare("select * from $tableName where 1 = + 0"); $cursor->execute(); $cursor->finish; 0E0 }; return defined $res; }` [download] The `->table_info` method would be the real DBI way, but unfortunately, `->table_info` isn't implemented in all cases where I needed it.	[reply] [d/l] [select]
Re: DBI "drop table" next obstacle by jasonk (Parson) on Apr 13, 2008 at 17:47 UTC
Or you could take the easy way out... `my $i = $dbh->prepare( 'drop table X' ); eval { $i->execute };` [download] www.jasonkohles.com We're not surrounded, we're in a target-rich environment!	[reply] [d/l]
Re: DBI "drop table" next obstacle by ikegami (Patriarch) on Apr 13, 2008 at 17:45 UTC
Change `$sth->execute();` [download] to `local $sth->{RaiseError} = 0; # Don't throw an exception on error. local $sth->{PrintError} = 0; # Don't display error messages. $sth->execute();` [download]	[reply] [d/l] [select]
Re: DBI "drop table" next obstacle by moritz (Cardinal) on Apr 13, 2008 at 18:06 UTC
In mysql you can say DROP TABLE IF EXISTS `table_name`;	[reply] [d/l]