Create a database using a stored procedure

Hello Angellll, thank you for your quick reply. I tried the syntax you suggets, no luck:

DELIMITER//;
CREATE PROCEDURE sp_create_database_and_user(IN name VARCHAR(50))
BEGIN
    PREPARE STMP FROM 'CREATE DATABASE ' + name;
    EXECUTE STMP;
    DEALLOCATE PREPARE STMP;
END//
DELIMITER;//

I am getting

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER//' at line 1
SQLState:  42000
ErrorCode: 1064
Error occured in:
DELIMITER/

If I try

CREATE PROCEDURE sp_create_database_and_user(IN name VARCHAR(50))
BEGIN
    PREPARE STMP FROM 'CREATE DATABASE ' + name;
    EXECUTE STMP;
    DEALLOCATE PREPARE STMP;
END

I am getting the following error

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ name;EXECUTE STMP;DEALLOCATE PREPARE STMP;END' at line 3
SQLState:  42000
ErrorCode: 1064

I am using mysql version 5.1.37. Any other thoughts?

Hello ushastry, still no luck :(:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$
 
DROP PROCEDURE IF EXISTS sp_create_database_and_user$$
 
CREATE P' at line 1
SQLState:  42000
ErrorCode: 1064
Error occured in:
DELIMITER $$
 
DROP PROCEDURE IF EXISTS sp_create_database_and_user$$
 
CREATE PROCEDURE sp_create_database_and_user(IN name VARCHAR(50))
BEGIN
        SET @tmp_sql= CONCAT("CREATE DATABASE ",name)

@theGhost_k8:

I am using version 5.1.37. It's suppoed to be fixed after version 5.0.x or am I wrong?

Which MySQL version you have? stored procedures are supported only for  >MySQL5.0

I have 5.x here at my end and the stored procedure which i posted works fine

Right I just thought of something. I am using a java client to connect to my database. I will try to conenct using command line and see if this makes any difference. I will let you know soon.

@girionis: yes in case of 5.1 it should work.
@ushastry: your code does work on 5.1.33; and for obvious reasons not working on 5.0.22/85

Let me check on 5.0.x and would let you know..

Right, ushastry your code does work when I try it from command line, so I guess there was a problem with the client I used. I will do a split and allocate most points to you.

Thanks again :)

Manual says CREATE/DROP DATABASE SQL statements can be used in prepared statements only in MySQL 5.1.12 and not before that.

5.0.22 - 0.83/85

==================

The following SQL statements can be used in prepared statements: ALTER TABLE, CALL, COMMIT, CREATE INDEX, CREATE TABLE, DELETE, DO, DROP INDEX, DROP TABLE, INSERT, RENAME TABLE, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE are also supported as of MySQL 5.0.23.

>5.1
====

The following SQL statements can be used in prepared statements: ALTER TABLE, CALL, COMMIT, CREATE INDEX, CREATE TABLE, DELETE, DO, DROP INDEX, DROP TABLE, INSERT, RENAME TABLE, REPLACE, SELECT, SET, UPDATE, and most SHOW statements.

As of MySQL 5.1.10, the following additional statements are supported:

ANALYZE TABLE
OPTIMIZE TABLE
REPAIR TABLE

As of MySQL 5.1.12, the following additional statements are supported:

CACHE INDEX
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
{CREATE | DROP} DATABASE
{CREATE | RENAME | DROP} USER
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
| LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT
REVOKE
KILL
LOAD INDEX INTO CACHE
RESET {MASTER | SLAVE | QUERY CACHE}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
INSTALL PLUGIN
UNINSTALL PLUGIN

Other statements are not yet supported.

how do you RUN that code? the DELIMITER stuff is for mysql command line, if you use some mysql tools, you won't need that part...

Thanks again everyone for your help.

Yes I realised that after a few tries. Thanks again for your suggestions.