CODEWITHSUNDEEP
javamysql
Plasma
Plasma

Reputation: 43

SQLException You have an error in your SQL Syntax

I'm relatively new to MySQL and I'm trying to make Discord bot that saves a discord user's id and a key to a MySQL database. When the bot starts, it loads the MySQL and connects to the database then tries to create a table if it doesn't exist using the following:

try {
    PreparedStatement ps = this.connection.prepareStatement("create table if not exists keys (discord bigint(250), key bigint(250));");
    ps.execute();
} catch (SQLException ex) {
    ex.printStackTrace();
}

When I try to run that, I get the following error:

java.sql.SQLSyntaxErrorException: 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 'bigint(250))' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:388)
    at main.java.bot.discord.utils.MySQL.update(MySQL.java:86)
    at main.java.bot.discord.utils.MySQL.init(MySQL.java:28)
    at main.java.bot.discord.utils.MySQL.<init>(MySQL.java:20)
    at main.java.bot.discord.Main.boot(Main.java:61)
    at main.java.bot.discord.Main.run(Main.java:56)
    at main.java.bot.discord.Main.main(Main.java:32)

To connect to the database I am using:

Class.forName("com.mysql.cj.jdbc.Driver");
this.connection = DriverManager.getConnection("jdbc:mysql://" + this.host + ":" + this.port + "/" + this.database + "?useSSL=false serverTimezone=America/New_York", this.username, this.password);

The MySQL Driver I'm using is the mysql-connector v8.0.11 Like I said, I'm new to MySQL so I may be doing something completely wrong. Please let me know if I am. Thanks.

EDIT: Line 86 in the MySQL class is where I am executing the PreparedStatement.

Upvotes: 2

Views: 2032

Answers (5)

Adam Nathaniel Davis
Adam Nathaniel Davis

Reputation: 425

As mentioned in previous replies, both key and keys are reserved words. You can't use them in table names or column names without special handling. However, previous replies stated that you could use these values by annotating them as [keys]/[key] or "keys"/"key". This is incorrect.

In MySQL, the backtick is used to create a literal value that can then be used for table or column names. That would look like this:

create table if not exists `keys` (discord bigint(250), `key` bigint(250));

However, even though it is technically possible to use reserved words when you encapsulate them in backticks, this is very bad practice. Please don't do it. The vast majority of all SQL statements are written without backticks. If you create a table in such a way that one-or-more of the columns will require backticks, this tends to lead to many SQL bugs.

Upvotes: 0

arczinosek
arczinosek

Reputation: 133

I think that value 250 to BIGINT is too large. Try to not use this or set to smaller value. Remember that this value informs only about display width (in your case 250 characters) not about max value or used bytes.

And for reserved words I always use ` character to escape

Upvotes: 0

Amith Kumar
Amith Kumar

Reputation: 4884

As per MYSQL documentation, you can't use reserved words for table name, column name or any other identifiers. Out here you are using keys as table name & key as column name and both are reserved words.

Also I noticed you are using ; delimiter, remember in a query for most databases this doesn't work through JDBC driver, as it is usually not part of the statement syntax itself, but a terminator for command line or script input to separate statements.

Upvotes: 0

t..
t..

Reputation: 1101

if you need to use key as the name you can do [key] or "Key", as others mentioned it is one of the reserved words.

Upvotes: 0

Elliott Frisch
Elliott Frisch

Reputation: 201447

key is a reserved word, change the column name to something else.

Upvotes: 2

Related Questions