Perl Guru Forums: Perl Programming Help: Beginner: a method to check for existing mysql tables

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: Beginner:
a method to check for existing mysql tables

mjgray06
Novice

Jul 3, 2006, 6:43 AM

Post #1 of 7 (587 views)

a method to check for existing mysql tables	Can't Post

I've messed around and figured i'd try this method to check for existing table records, any feedback?

Code

 
 
sub table_exists { 
  my ($dbname,$table) = @_;  
 
  eval { 
     local $dbname->{'PrintError'} = 0; 
     local $dbname->{'RaiseError'} = 1; 
     $dbname->do(qq{SELECT * FROM $table WHERE 1 = 0}); 
    }; 
 
    return $@ ? 0 : 1; 
} 
 
 
use: 
 
 if (table_exists($dbh, "articles")) { 
  #table found 
 } else {  
  #table doesn't exist 
 }

davorg
Thaumaturge / Moderator

Jul 4, 2006, 6:11 AM

Post #2 of 7 (582 views)

Re: [mjgray06] a method to check for existing mysql tables [In reply to]	Can't Post

That all sounds a bit fragile to me. You're assuming that if you have any kind of error on your select then it's because the table doesn't exist. There are plenty of other errors that you could get.

You'd be better off querying the table metadata to find out if your table exists. In MySQL 5, you can use INFORMATION_SCHEMA to get this information. In earlier versions you can parse the output from the show tables command.

Or if cross-db portability is important to you, then you can use DBI's own catalogue methods.

--
Dave Cross, Perl Hacker, Trainer and Writer
http://www.dave.org.uk/
Get more help at Perl Monks

mjgray06
Novice

Jul 4, 2006, 4:13 PM

Post #3 of 7 (577 views)

Re: [davorg] a method to check for existing mysql tables [In reply to]	Can't Post

So how would i go about using the INFORMATION_SCHEMA method?

davorg
Thaumaturge / Moderator

Jul 6, 2006, 5:44 AM

Post #4 of 7 (568 views)

Re: [mjgray06] a method to check for existing mysql tables [In reply to]	Can't Post

I'm not sure what you're having trouble with. I gave you a link to the documentation for INFORMATION_SCHEMA. This schema contains a table called INFORMATION_SCHEMA.TABLES which contains details of all of the tables in your database. You just query that table to find if a table exists with the name that you are looking for.

--
Dave Cross, Perl Hacker, Trainer and Writer
http://www.dave.org.uk/
Get more help at Perl Monks

mjgray06
Novice

Jul 6, 2006, 4:07 PM

Post #5 of 7 (565 views)

Re: [davorg] a method to check for existing mysql tables [In reply to]	Can't Post

This is always printing table doesn't exist, when it does.

Code

 my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host", 
                       $user, $pw, {'RaiseError' => 1}); 
 
 eval { $dbh->do(qq{SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'articles'}); }; 
 
 print "table doesn't exist" if $@; 
 
 $dbh->disconnect;

davorg
Thaumaturge / Moderator

Jul 7, 2006, 6:13 AM

Post #6 of 7 (560 views)

Re: [mjgray06] a method to check for existing mysql tables [In reply to]	Can't Post

Ok. You need to think a bit more carefully about what it is that you're trying to do. Why do you think that "eval" and $@ is the best way to go about it? That approach is appropriate when you are calling code that might call "die".

You have two situations here. Either the table exists or it doesn't. In the first case your select will return one row and in the second case your select will return no rows. In neither of those cases will an error be generated - so under normal operation $@ will never be set and that code will always act as though the table exists.

That leaves you with two problems. Firstly you need a better way to find out if the table exists or not and secondly you need to know why your "eval" is always setting $@ (i.e. why you're always getting an error).

For the first problem, can I suggest that you forget about checking for errors (as I don't think that will work) and instead think about counting the number of rows that are returned (which will, hopefully, be either zero or one).

As for the second problem - well I can only guess. You'll need to print the value of $@ to see what has gone wrong. My guess (and it's only a guess) would be that you're not using MySQL version 5 and that therefore you don't have an INFORMATION_SCHEMA.

--
Dave Cross, Perl Hacker, Trainer and Writer
http://www.dave.org.uk/
Get more help at Perl Monks

mjgray06
Novice

Jul 7, 2006, 8:38 AM

Post #7 of 7 (557 views)

Re: [davorg] a method to check for existing mysql tables [In reply to]	Can't Post

So, down to our last option, I could do this for what it's worth?

Code

  $dbh->do(qq{CREATE TABLE IF NOT EXISTS tablename});

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives