Create DB statement - error on DB name

Question

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

DBName don't have to contain empty spaces + executeUpdate() is wrong method call. — drgPP
– drgPP, Commented Sep 11, 2015 at 7:50
ok thanks, so how can I create DB with that DBName (with empty spaces)? — Frank
– Frank, Commented Sep 11, 2015 at 7:56
@drgPP why is executeUpdate is wrong ? thats how you create the db - int Result=statement.executeUpdate("CREATE DATABASE databasename"); is clearly how you create the db from java. do not see whats wrong here — Frederic Henri
– Frederic Henri, Commented Sep 11, 2015 at 8:06
@FredericHenri you are right, it works, the API says the following: Parameter sql - an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement. I have not read the second part about DDL statement. I wanted to edit my comment. — drgPP
– drgPP, Commented Sep 11, 2015 at 8:09

Frederic Henri · Accepted Answer · 2015-12-11 15:52:08Z

4

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`";

edited Dec 11, 2015 at 15:52

answered Sep 11, 2015 at 8:01

Frederic Henri

54k12 gold badges119 silver badges141 bronze badges

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

3 Comments

drgPP Over a year ago

This demonestrates my assumption was false. Great example.

Frank Over a year ago

I tried that, but it does not work in java. I says:

check the manual that corresponds to your MySQL server version for the right syntax to use near ''DB name 2015-02-01'' at line 1

Frederic Henri Over a year ago

@Frank, I might check later, do you use ` (backtick) or ' (quote), it seems you have use quote while backtick needs to be used

Collectives™ on Stack Overflow

Create DB statement - error on DB name

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related