CODEWITHSUNDEEP
javamysqljdbc
Frank
Frank

Reputation: 2173

Create DB statement - error on DB name

I'm trying to create a DB programmatically using:

String DBName = "DB name 2015-02-01";
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
statement = connection.createStatement();
statement.executeUpdate("CREATE DATABASE " + DBName);

but I get this error:

GRAVE: null
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 2015-02-01' at line 1

What am I doing wrong?

How can I do to create DB with that DBName?

Thanks

Upvotes: 2

Views: 256

Answers (1)

Frederic Henri
Frederic Henri

Reputation: 53703

I would agree this is bad practice to have space in name but its not forbidden as others can say. the restrictions are documented here http://dev.mysql.com/doc/refman/5.0/en/identifiers.html and only a space at the end is forbidden

you can even mess with table names with space - for example the following works:

fhenri@machine:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.22 Homebrew

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database `db with space`;
Query OK, 1 row affected (0,00 sec)

mysql> use `db with space`;
Database changed
mysql> create table `table with space` (column1 VARCHAR(100) NOT NULL);
Query OK, 0 rows affected (0,03 sec)

mysql> select * from `table with space`;
Empty set (0,00 sec)

mysql>

From Java, you need to add the ` character (backtick character, not to confused with simple quote ')

String DBName = "`DB name 2015-02-01`";

Upvotes: 4

Related Questions