Java SQLSyntaxErrorException at executeUpdate

Question

I am creating a database that keeps track of Spiderman comic books. I am getting a SQLExecption at line 28:

stmt.executeUpdate(createstring);"

So I assume there is something wrong with the syntax of my SQL that is in the createstring but nothing jumps out at me.

Below is code

import java.sql.*;
import javax.swing.*;

public class SpidermanDatabase {

    public static void main(String[] args) 
    {
        String url = "jdbc:ucanaccess://c:/users/jeff/comics.accdb";
        
        Connection con;
        String createstring;
        createstring = "CREATE TABLE Spider-Man (" +
                       "ComicName varchar(40), " +
                       "IssueDate varchar(40), " +
                       "IssueName varchar(40), " +
                       "MintCond varchar(40), " +
                       "IssueValue double(2,2), " +
                       "IssueNum int)";
        Statement stmt;
        try
        {
            con = DriverManager.getConnection(url, "", "");
            stmt = con.createStatement();
            stmt.executeUpdate(createstring);
            
            JOptionPane.showMessageDialog(null, "Spider-Man table created", "SQL Statement Confirmation",
                    JOptionPane.INFORMATION_MESSAGE);
            stmt.close();
            con.close();
            System.exit(0);
        }
        catch(SQLException ex)
        {
            System.out.println("SQLException");
            System.out.println(ex.getMessage());
            ex.printStackTrace();
        }
    }
}

Image attached is full error message

Answer 1

It appears to me that you are using Microsoft Access database. It also appears that you are using ucanaccess JDBC driver to connect to that database.

Your problem is that you are using an illegal character in the name of the table that you are trying to create. According to Microsoft documentation, identifiers, such as database table names, cannot contain a dash (-) (also known as a hyphen) – unless you enclose the identifier in either square brackets, i.e. [], or quotation marks.

Hence you should change the SQL create table string to the following:

createstring = "CREATE TABLE [Spider-Man] (" +
               "ComicName varchar(40), " +
               "IssueDate varchar(40), " +
               "IssueName varchar(40), " +
               "MintCond varchar(40), " +
               "IssueValue double, " +
               "IssueNum int)";

Also note that the double data type cannot have scale and precision qualifiers. Hence I also removed the (2,2) part from the data type for column IssueValue.

Remember that after this you must always write [Spider-Man] (or "Spider-Man") as the table name in all SQL statements. Alternatively, you could replace the dash with and underscore (_) which is a legal character in an identifier and thus do away with the need for square brackets (or quotation marks), i.e. name the table Spider_Man.

EDIT

Although the above CREATE TABLE statement is valid SQL, it appears that ucanaccess JDBC driver cannot handle it. The only way I got it to work was to replace the dash with an underscore.

Here is my java code for creating the database table Spider_Man.
(Note that the below code uses try-with-resources.)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcTst0 {

    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/users/jeff/comics.accdb";
        String createstring = "CREATE TABLE Spider_Man (" +
                              "ComicName varchar(40), " +
                              "IssueDate varchar(40), " +
                              "IssueName varchar(40), " +
                              "MintCond varchar(40), " +
                              "IssueValue double, " +
                              "IssueNum int)";
        try (Connection con = DriverManager.getConnection(url, "", "");
             Statement stmt = con.createStatement()) {
            stmt.executeUpdate(createstring);
            System.out.println("Spider_Man table created");
        }
        catch (SQLException xSql) {
            xSql.printStackTrace();
        }
    }
}