MySQL DBA MySQL DBA - 2 months ago 8
MySQL Question

Mysql Prepare Statement error while executing

I am trying to create a table though prepare statement but it is giving me syntax error. Well if I try to execute the same statement individually then it works 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 am 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

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.