Mysql Prepare Statement error while executing

Question

Hi All, I am trying to create a table though prepare statement but it is giving me syntax error. well if o try to execute the same statement individually then it work fine.

here's my statement -

SET @Stmt1 = Concat('DROP TABLE IF EXISTS ',DB,'.`county`;\n'
'CREATE TABLE IF NOT EXISTS ',DB,'.`County`
(
  `CountyID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `CountyName` VARCHAR(45) NOT NULL,
  `CountyCode` VARCHAR(30) NOT NULL,
   PRIMARY KEY (`CountyID`)
)');

Prepare stmt2 From @stmt1;
Execute stmt2;

please can anyone tell me what m i missing in this statement? It is giving me an error on this line

'CREATE TABLE IF NOT EXISTS ',DB,'.`County`
(
  `CountyID` INT UNSIGNED NOT NULL AUTO_INCREMENT,

Answer 1

are you not missing a comma between the two strings in the concat?

should be

SET @Stmt1 = Concat('DROP TABLE IF EXISTS ',DB,'.county;\n', 'CREATE TABLE IF NOT EXISTS ',DB,'.County ( CountyID INT UNSIGNED NOT NULL AUTO_INCREMENT, CountyName VARCHAR(45) NOT NULL, CountyCode VARCHAR(30) NOT NULL, PRIMARY KEY (CountyID) )');

Answer 2

http://dev.mysql.com/doc/refman/5.1/en/prepare.html says:

The text [of the preparable statement] must represent a single SQL statement, not multiple statements.

So you'll have to execute your DROP TABLE statement first, and then prepare and execute the CREATE TABLE statement separately.