MySQL Syntax Error Java Table Creation

Question

I have a java file that is trying to create a table if it does exist, whenever I try to run the program I always get an error in the syntax.

CREATE TABLE IF NOT EXISTS `shirts` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR NOT NULL , `iid` INT NOT NULL , `desc` VARCHAR NOT NULL , `shelf` VARCHAR NOT NULL , `gender` VARCHAR NOT NULL , `chestsize` FLOAT(4,2) NOT NULL , `waistsize` FLOAT(4,2) NOT NULL , `seatsize` FLOAT(4,2) NOT NULL , `bicepsize` FLOAT(4,2) NOT NULL , `shirtlength` FLOAT(4,2) NOT NULL , `shirtwidth` FLOAT(4,2) NOT NULL , `sleevelength` FLOAT(4,2) NOT NULL , `collarsize` FLOAT(4,2) NOT NULL , `picture` LONGBLOB NOT NULL , `rented` BOOLEAN NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

It was copied straight from phpMyAdmin but keeps throwing the error stating:

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 'NOT NULL , `iid` INT NOT NULL , `desc` VARCHAR NOT NULL , `shelf` VARCHAR NOT NU' at line 1

And for the life of me I can't figure out why.

Where is the error in my syntax?

full java code:

public void createShirtsTable() {
    String sql = "CREATE TABLE IF NOT EXISTS `shirts` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR NOT NULL , `iid` INT NOT NULL , `desc` VARCHAR NOT NULL , `shelf` VARCHAR NOT NULL , `gender` VARCHAR NOT NULL , `chestsize` FLOAT(4,2) NOT NULL , `waistsize` FLOAT(4,2) NOT NULL , `seatsize` FLOAT(4,2) NOT NULL , `bicepsize` FLOAT(4,2) NOT NULL , `shirtlength` FLOAT(4,2) NOT NULL , `shirtwidth` FLOAT(4,2) NOT NULL , `sleevelength` FLOAT(4,2) NOT NULL , `collarsize` FLOAT(4,2) NOT NULL , `picture` LONGBLOB NOT NULL , `rented` BOOLEAN NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;";
    try (Connection conn = this.connect();
            Statement stmt = conn.createStatement()) {
        stmt.execute(sql);
        //System.out.println("Created Shirts Table"); --DEBUG
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
}

I think you need to specify the maximum length of `VARCHAR` for example `VARCHAR(100)` — Titus, Aug 27 '18 at 03:05

score 4 · Accepted Answer · answered Aug 27 '18 at 03:19

To declare a varchar field in MySQL it is necessary to pass the maximum length as parameter

CREATE TABLE IF NOT EXISTS `shirts` 
( 
     `id` INT NOT NULL AUTO_INCREMENT ,
     `name` VARCHAR(10) NOT NULL ,           --updated
     `iid` INT NOT NULL ,
     `desc` VARCHAR(10) NOT NULL ,           --updated
     `shelf` VARCHAR(10) NOT NULL ,          --updated
     `gender` VARCHAR(10) NOT NULL ,         --updated
     `chestsize` FLOAT(4,2) NOT NULL ,
     `waistsize` FLOAT(4,2) NOT NULL ,
     `seatsize` FLOAT(4,2) NOT NULL ,
     `bicepsize` FLOAT(4,2) NOT NULL ,
     `shirtlength` FLOAT(4,2) NOT NULL ,
     `shirtwidth` FLOAT(4,2) NOT NULL ,
     `sleevelength` FLOAT(4,2) NOT NULL ,
     `collarsize` FLOAT(4,2) NOT NULL ,
     `picture` LONGBLOB NOT NULL ,
     `rented` BOOLEAN NOT NULL ,
     PRIMARY KEY (`id`)

);

documentation here

see my output

    -> );
Query OK, 0 rows affected (0.04 sec)

MariaDB [test]>

I would never have guessed that VARCHAR requires a length. Thank you so much. I tried to search and couldn't find it, I did not know that it was VARCHAR that was messing up. — terturl x, Aug 27 '18 at 17:33

MySQL Syntax Error Java Table Creation

1 Answers1