MySQL create and use new database with variable

Question

I want to create and use a database/schema by using a variable, so I can change it when I want to run a script on a different Schema.

I have something like this:

SET @VarSchema = 'test';
SET @Front = 'CREATE DATABASE IF NOT EXISTS ';
SET @EndPart = ';';
SET @Stmt = CONCAT(@Front,@VarSchema,@EndPart);
PREPARE stmt FROM @Stmt;
EXECUTE stmt;

SET @Front = 'USE ';
SET @EndPart = ';';
SET @Stmt = CONCAT(@Front,@VarSchema,@EndPart);
PREPARE COMMAND FROM @Stmt;
EXECUTE COMMAND;

But this gives me this error: Error Code: 1295. This command is not supported in the prepared statement protocol yet

EDIT: It complains about this statement:

SET @Front = 'USE ';
SET @EndPart = ';';
SET @Stmt = CONCAT(@Front,@VarSchema,@EndPart);
PREPARE COMMAND FROM @Stmt;
EXECUTE COMMAND;

Is there any way I can do this?

Which statement is it complaining about?

Barmar
– Barmar

2014-09-01 10:39:56 +00:00
Commented Sep 1, 2014 at 10:39 — Barmar
– Barmar, Commented Sep 1, 2014 at 10:39
The second one, ill edit it to show that.

Phillip Macdonald
– Phillip Macdonald

2014-09-01 10:41:12 +00:00
Commented Sep 1, 2014 at 10:41 — Phillip Macdonald
– Phillip Macdonald, Commented Sep 1, 2014 at 10:41

Barmar · Accepted Answer · 2014-09-01 10:42:59Z

1

Since you can't execute the USE statement with a prepared statement, you'll have to prepare all your queries so you can substitute the database name into them.

answered Sep 1, 2014 at 10:42

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phillip Macdonald Over a year ago

It does seem like a strange limitation, but if this is the way I have to do it, then i shall do it like that. Thanks!

Collectives™ on Stack Overflow

MySQL create and use new database with variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related