Solved MySQL Table Creation

Discussion in 'Spigot Plugin Development' started by Willim, May 26, 2018.

  1. Not sure what I am missing from this error or why I can't seem to figure it out but when I try to create this table it throws off the error:

    Code (Text):

    [22:22:08 WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VARCHAR(100),email VARCHAR(100),ip VARCHAR(100),eAuth VARCHAR(100),gAuth VARCHAR' at line 1
     
    The Code:
    Code (Text):

    public void createTable() {
        if (!isConnected()) {
            connect();
        }
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("CREATE TABLE IF NOT EXISTS Players (uuid VARCHAR(100),key VARCHAR(100),email VARCHAR(100),ip VARCHAR(100),eAuth VARCHAR(100),gAuth VARCHAR(100),verified VARCHAR(100),PRIMARY KEY (uuid))");
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
     

    But if I do the following (as an example) it works perfectly fine. I am not to sure on why it's throwing off an error.
    Code (Text):

    PreparedStatement ps = MySQL.getConnection().prepareStatement("CREATE TABLE IF NOT EXISTS Players (UUID VARCHAR(100),NameVARCHAR(100),Coins INT(100),PRIMARY KEY (UUID))");
    ps.executeUpdate();
     
     
  2. You need to add ";" at the end
     
  3. Code:
    Code (Text):

    public void createTable() {
        if (!isConnected()) {
            connect();
        }
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("CREATE TABLE IF NOT EXISTS Players (uuid VARCHAR(100),key VARCHAR(100),email VARCHAR(100),ip VARCHAR(100),eAuth VARCHAR(100),gAuth VARCHAR(100),verified VARCHAR(100),PRIMARY KEY (uuid));");
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
     
    Error:
    Code (Text):

    [22:46:23 WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VARCHAR(100),email VARCHAR(100),ip VARCHAR(100),eAuth VARCHAR(100),gAuth VARCHAR' at line 1
     
     
  4. Im not sure try adding " ' " to Players give an space by each comma and in the last field i think you are wrong, you need to set the kind of field and the primary key
     
  5. Still nothing the same error as before
     
  6. electronicboy

    IRC Staff

    key is a reserved word and cannot be used as a column name, and you should probably read up on data types properly... Misusing data types is one of the many paths to poor database performance.
     
    • Like Like x 1
    • Agree Agree x 1
  7. Thank you, and I'll take a look into data types much appreciated.