CODEWITHSUNDEEP
mysqlperl
Phil Jackson
Phil Jackson

Reputation: 10288

Perl - If table does not exist

I'm currently using the following which works but throws an error every time the table is created (as it is first trying to insert into a db which does not exist and returning false).

$result = $dbh->prepare("INSERT INTO `". $host ."` (URL) VALUES ('$href')");
if( ! $result->execute() ){
    $result = $dbh->prepare("CREATE TABLE `" . $host . "` ( `ID` INT( 255 ) NOT NULL AUTO_INCREMENT , `URL` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `ID` )) ENGINE = MYISAM ;");
    $result->execute();
    print "Host added: " . $host . "\n";
}

Could someone let me know of a more efficient way of completing this task?

EDIT - Bind

$result = $dbh->prepare("CREATE TABLE `?` If NOT EXISTS ( `ID` INT( 255 ) NOT NULL AUTO_INCREMENT , `URL` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `ID` )) ENGINE = MYISAM ;");
            $result->execute($host);
            $result = $dbh->prepare("INSERT INTO `?` (URL) VALUES ('?')");
            $result->execute($host, $href);
            print "Host added: " . $host . "\n";

is this correct?

Upvotes: 2

Views: 3234

Answers (4)

ysth
ysth

Reputation: 98528

If you expect the table to exist more often than not, the existing code is the most efficient way. Note that you can override PrintError and RaiseError (assuming one of those is what you mean by "throws an error") for a given statement handle.

Upvotes: 0

GrandmasterB
GrandmasterB

Reputation: 3455

Is this a web app? If so, I highly recommend against using a user/pass that has rights to alter the database structure. A more typical method would be to have an installer script that creates the tables up front.

Upvotes: 0

mluebke
mluebke

Reputation: 8829

Try:

CREATE TABLE tblname IF NOT EXISTS ...

Upvotes: 0

Ether
Ether

Reputation: 54014

CREATE TABLE Foo if NOT EXISTS  <schema>

Also, please use placeholders and bind values in your INSERT operations, lest you be known as Little Bobby Tables.

Upvotes: 5

Related Questions