JDBC: Create table query syntax error

Question

I am trying to execute a create query using JDBC. I have a method which creates the query and then I execute it but its showing me syntax error. Below is the stack trace :

com.mysql.jdbc.exceptions.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 'supplierId varchar,supplierUrl varchar,totalActivities varchar,activityName varc' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1343)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1260)

Now the query generated is this :

create table demo ( ID INTEGER PRIMARY KEY AUTO_INCREMENT,supplierName varchar,supplierId varchar,supplierUrl varchar,totalActivities varchar,activityName varchar,activityPrice varchar,tourCode varchar,starRating varchar,totalReviews varchar,geography varchar,duration varchar,category varchar,subCategory varchar);

And below is the method which is generating this query :

private static String getCreateTableQuery(String tableName, String columnData) {
    StringBuilder sqlStatement = new StringBuilder("");

    sqlStatement.append("create table " + tableName + " ( ID INTEGER PRIMARY KEY AUTO_INCREMENT,");

    String[] columns = columnData.split(">");  // columns are separated by >
    for (int i = 0; i < columns.length; i++) {
        sqlStatement.append(columns[i] + " varchar");
        if (i != columns.length - 1) { // no commas after last column
            sqlStatement.append(",");
        }
    }
    sqlStatement.append(");");
    return sqlStatement.toString();
}

And this is how am executing the query :

SessionImpl sessionImpl = (SessionImpl) getSessionFactory().openSession(); 
    Connection conn = (Connection) sessionImpl.connection();

    Statement statement = (Statement) conn.createStatement();
    statement.executeUpdate(query);
    sessionImpl.close();
    conn.close();

Am unable to understand the syntax error. Can someone please explain?

I'm nearly positive varchar requires an argument for its max length: `varchar(20)`, for instance. — yshavit, Oct 17 '16 at 05:16

score 0 · Answer 1 · edited May 23 '17 at 11:45

0

In Mysql you need to define a length to the varchar. Take a look here:

Why does VARCHAR need length specification?

I don't see a problem with your Java code. Fix your create table statement and you'll probably be fine.

edited May 23 '17 at 11:45

Community

1
1

answered Oct 17 '16 at 05:22

Hernan Velasquez

2,770
14
21

Jay Prakash · Accepted Answer · 2016-10-17T05:45:02.143

I think you have to pass max length for varchar fields:

Please check this your query will be like that:

create table demo ( ID INTEGER PRIMARY KEY AUTO_INCREMENT,supplierName varchar(255),supplierId varchar(255),supplierUrl varchar(255),totalActivities varchar(255),activityName varchar(255),activityPrice varchar(255),tourCode varchar(255),starRating varchar(255),totalReviews varchar(255),geography varchar(255),duration varchar(255),category varchar(255),subCategory varchar(255));

Here is insert Query:

insert into demo
    ( supplierName, supplierId, supplierUrl, totalActivities, activityName, 
    activityPrice, tourCode, starRating, totalReviews, geography, duration, 
    category, subCategory)
    values
    (supplierName, supplierId, supplierUrl, totalActivities, activityName, 
    activityPrice, tourCode, starRating, totalReviews, geography, duration, 
    category, subCategory)

Yeah. It worked. What would be the INSERT query be like for this table? — roger_that, Oct 17 '16 at 05:38

JDBC: Create table query syntax error

2 Answers2