CODEWITHSUNDEEP
javamysqlsqljdbc
James Andrew
James Andrew

Reputation: 207

om.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax;

Im attempting to create 2 tables, but I dont fully understand where Im going wrong. It's obviously a syntax error, and Im new to SQL in general, can someone explain to me what Im doing wrong here?

 String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR(255) NOT NULL," +
                " BOOLEAN manager NOT NULL," +
                " BOOLEAN team_chat NOT NULL," +
                " PRIMARY KEY (uuid))";

        String query2 = "CREATE TABLE IF NOT EXISTS " + secondTableName + " (" +
                "team_name VARCHAR(255) NOT NULL," +
                " team_password VARCHAR(255) NOT NULL" +
                " BOOLEAN friendly_fire NOT NULL," +
                " VARCHAR(255) hq," +
                " VARCHAR(255) rally," +
                " PRIMARY KEY (team_name))";

This is my stacktrace:

WARN 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 'manager NOT NULL, BOOLEAN team_chat NOT NULL, PRIMARY KEY (uuid))' at line 1

Code used to execute:

    private void createTables(String tableName, String secondTableName) throws SQLException {
    try {
        checkConnection();
    } catch (Exception e) {
        e.printStackTrace();
    }

    try {

        String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR(255) NOT NULL," +
                " BOOLEAN manager NOT NULL," +
                " BOOLEAN team_chat NOT NULL," +
                " PRIMARY KEY (uuid))";

        String query2 = "CREATE TABLE IF NOT EXISTS " + secondTableName + " (" +
                "team_name VARCHAR(255) NOT NULL," +
                " team_password VARCHAR(255) NOT NULL" +
                " BOOLEAN friendly_fire NOT NULL," +
                " VARCHAR(255) hq," +
                " VARCHAR(255) rally," +
                " PRIMARY KEY (team_name))";

        PreparedStatement preparedStatement = connection.prepareStatement(query);
        PreparedStatement preparedStatement2 = connection.prepareStatement(query2);

        preparedStatement.execute();
        preparedStatement2.execute();
        main.getLogger().log(Level.INFO, "Tables " + tableName + " and " + secondTableName + " were successfully created.");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        close();
    }

}

Upvotes: 0

Views: 1797

Answers (2)

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79807

The column name has to come before the data type. So

BOOLEAN manager NOT NULL,
BOOLEAN team_chat NOT NULL,

should be 

manager BOOLEAN NOT NULL,
team_chat BOOLEAN NOT NULL,

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30809

The problem is presence of MAX in the table definition. VARCHAR expects a number as an argument so you either need to define MAX as variable and pass it (from Java) or just replace MAX with a number, e.g.:

String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR( + " + MAX + ") NOT NULL," +
                " BOOLEAN manager NOT NULL," +
                " BOOLEAN team_chat NOT NULL," +
                " PRIMARY KEY (uuid))";

OR

String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR(255) NOT NULL," +
                " BOOLEAN manager NOT NULL," +
                " BOOLEAN team_chat NOT NULL," +
                " PRIMARY KEY (uuid))";

If you do not know the max length then you can use LONGTEXT as type.

Also, we need to fix the ordering of type and column names, it needs to be manager BOOLEAN and not BOOLEAN manager, e.g.:

    String query = "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                "uuid CHAR(36) NOT NULL," +
                " team_name VARCHAR(255) NOT NULL," +
                " manager BOOLEAN NOT NULL," +
                " team_chat BOOLEAN NOT NULL," +
                " PRIMARY KEY (uuid))";

    String query2 = "CREATE TABLE IF NOT EXISTS " + secondTableName + " (" +
            "team_name VARCHAR(255) NOT NULL," +
            " team_password VARCHAR(255) NOT NULL" +
            " friendly_fire BOOLEAN  NOT NULL," +
            " hq VARCHAR(255)," +
            " rally VARCHAR(255)," +
            " PRIMARY KEY (team_name))";

Upvotes: 2

Related Questions