How to find MySQL database exist or not

[edi]

Hi Monks, How to check whether a MySQL database exist or not?

use DBI;
eval { $dbh = DBI->do('dbi:mysql: xxxxx','root','')
 or die "Connection Error: $DBI::errstr\n"} ;



    if ($dbh == 'undef') {

           print "Table  does not exist.\n";
    }
    else {
        print "table exist";
    }

Thank you in advance!

[rcaputo]

Query MySQL's internal tables for the information:

1) macbookpoe:~% mysql -BN -e 'select count(1) from information_schema
+.TABLES where TABLE_SCHEMA="test" and TABLE_NAME="moo"'

1

1) macbookpoe:~% mysql -BN -e 'select count(1) from information_schema
+.TABLES where TABLE_SCHEMA="test" and TABLE_NAME="moue"'

0

[roboticus]

edi:

I'd make the following change:

if (defined $dbh) { print "Table does not exist.\n"; }

The version you have checks whether $dbh is 0. (Had you used eq instead of ==, it would have checked whether $dbh contained the string 'undef'.)

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[JavaFan]

I would not make the same change. Why bother wrapping an "X or die" inside an eval, then check whether X is false afterwards?

if ($dbh = DBI->do('dbi:mysql: xxxxx','root','')) {
    print "Table exists\n"
} 
else {
    print "Table does not exists";
}

Of course, all it checks is whether one can connect - not whether a database exists (title question) or whether a table exists (message printed).

[roboticus]

JavaFan:

Good point. I just saw the $blah == 'undef' and was immediately horrified.

;^)

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[choroba]

And, once we are at nitpicking, it checks it after the program starts, not before (per spec). :)

[edi]

thank you JavaFun for the reply. but it is giving an error as follows: Name "main::dbh" used only once: possible typo at C:/Program Files..... Can't locate auto/DBI/do.al in @INC

[edi]

 
use DBI;
eval { $dbh = DBI->do('dbi:mysql:xxxxxxx','root','')
 or die "Connection Error: $DBI::errstr\n"
} ;

if (defined $dbh) { print "Table does not exist.\n"; } 
else { print "Table exist";}

code will print "Table exist" whether the database exist or not. Please help.

[Marshall]

I think that you are mixing up a couple of things.

There is a dataset name ($dsn) which is a mySQL database on a particular host name and port number. That has to exist or you are going "nowhere"!

If the $dsn exists, then you can attempt to connect to it. This is where the user name and passwords are required.

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"
       || die "DB data set name failed $!\n";
#
# second step #
#
my $dbh = DBI->connect($dsn, $user, $password) 
                || die "DB connect failed $!\n";

The $dsn and the final connected handle, $dbh are very different things.

[Corion]

Have a look at the Catalog Methods of DBI. With these, you can easily find out what databases, tables and columns exist.

[Anonymous Monk]

First you connect, then you do