java.sql.SQLSyntaxErrorException: unexpected token: (

Question

Sorry for my bad english i'm french I need a litle help :-)

I've got a java.sql.SQLSyntaxErrorException but I can't find what's going on.

Here is the trace :

org.springframework.jdbc.datasource.init.ScriptStatementFailedException:
Failed to execute SQL script statement #1 of class path resource
[ddl/ddl-table.sql]: CREATE TABLE user_klesia(noss varchar(15),email
varchar(45),tel_fixe varchar(20),tel_mobile varchar(20)
,statut_consentement varchar(20) ,consentement_offres_date timestamp
NULL,certification_date timestamp NULL,derniere_tentative_date
timestamp NULL,id_web bigint(20) NOT NULL AUTO_INCREMENT,certifie
int(1) DEFAULT '0',date_naissance date ,origine varchar(2) DEFAULT
'K',question_secrete varchar(45),reponse_secrete
varchar(45),liferay_user_id bigint(20),nom varchar(200),prenom
varchar(200),indicateur_tel_fixe varchar(20),code_pays_fixe
varchar(4),indicateur_tel_mobile varchar(20),code_pays_mobile
varchar(4),civilite varchar(15),id_indv_ur
bigint(20),blocage_compte_date timestamp NULL,nb_tentatives
int(1),id_indv_alloc bigint(20),question_certifiante varchar(200)
,reponse_certifiante varchar(200) ,mode_certification varchar(20)
,otp_certifiant varchar(10) ,email_date_maj timestamp NULL
,tel_fixe_date_maj timestamp NULL ,tel_mobile_date_maj timestamp NULL
,id_individu bigint(20),france_connect varchar(45) ,sub varchar(255)
,sso_ima int(1),PRIMARY KEY (id_web)); nested exception is
java.sql.SQLSyntaxErrorException: unexpected token: (   
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:492)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:240)
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:48)
    at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.initDatabase(EmbeddedDatabaseFactory.java:200)
    at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.getDatabase(EmbeddedDatabaseFactory.java:157)
    at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder.build(EmbeddedDatabaseBuilder.java:270)
    at fr.koudama.batch.excellent.test.launchJob(test.java:82)

This is my SQL statement:

CREATE TABLE user_klesia(noss varchar(15),
                         email varchar(45),
                         tel_fixe varchar(20),
                         tel_mobile varchar(20),
                         statut_consentement varchar(20), 
                         consentement_offres_date timestamp NULL,
                         certification_date timestamp NULL,
                         derniere_tentative_date timestamp NULL,
                         id_web bigint(20) NOT NULL AUTO_INCREMENT,
                         certifie int(1) DEFAULT '0',
                         date_naissance date,
                         origine varchar(2) DEFAULT 'K',
                         question_secrete varchar(45),
                         reponse_secrete varchar(45),
                         liferay_user_id bigint(20),
                         nom varchar(200),
                         prenom varchar(200),
                         indicateur_tel_fixe varchar(20),
                         code_pays_fixe varchar(4),
                         indicateur_tel_mobile varchar(20),
                         code_pays_mobile varchar(4),
                         civilite varchar(15),
                         id_indv_ur bigint(20),
                         blocage_compte_date timestamp NULL,
                         nb_tentatives int(1),
                         id_indv_alloc bigint(20),
                         question_certifiante varchar(200),
                         reponse_certifiante varchar(200),
                         mode_certification varchar(20),
                         otp_certifiant varchar(10),
                         email_date_maj timestamp NULL,
                         tel_fixe_date_maj timestamp NULL,
                         tel_mobile_date_maj timestamp NULL,
                         id_individu bigint(20),
                         france_connect varchar(45),
                         sub varchar(255),
                         sso_ima int(1),
                         PRIMARY KEY (id_web));

I don't think AUTO_INCREMENT is valid in that case, [this answer](https://stackoverflow.com/questions/9345369/auto-incrementation-with-hsqldb-2-2-8-ddlutils) may help? — John M, Jul 02 '18 at 10:21
John M is right. Also, it seems that HSQL does not allow you to specify lenght in int and bigint fields. That's your unexpected token '(' in the line defining field id_web. — RubioRic, Jul 02 '18 at 10:37

score 2 · Answer 1 · edited Jul 02 '18 at 15:37

There are few things wrong with your DDL:

bigint and int cannot have size. You need to change bigint(20) for bigint and int(1) for int.
auto_increment does not exist but it's implemented as identity.

I fixed your SQL and tested it in HyperSQL 2.3.4. It works well now:

CREATE TABLE user_klesia (
  noss varchar(15),
  email varchar(45),
  tel_fixe varchar(20),
  tel_mobile varchar(20) ,
  statut_consentement varchar(20) ,
  consentement_offres_date timestamp NULL,
  certification_date timestamp NULL,
  derniere_tentative_date timestamp NULL,
  id_web bigint identity NOT NULL,
  certifie int DEFAULT '0',
  date_naissance date ,
  origine varchar(2) DEFAULT 'K',
  question_secrete varchar(45),
  reponse_secrete varchar(45),
  liferay_user_id bigint,
  nom varchar(200),
  prenom varchar(200),
  indicateur_tel_fixe varchar(20),
  code_pays_fixe varchar(4),
  indicateur_tel_mobile varchar(20),
  code_pays_mobile varchar(4),
  civilite varchar(15),
  id_indv_ur bigint,
  blocage_compte_date timestamp NULL,
  nb_tentatives int,
  id_indv_alloc bigint,
  question_certifiante varchar(200) ,
  reponse_certifiante varchar(200) ,
  mode_certification varchar(20) ,
  otp_certifiant varchar(10) ,
  email_date_maj timestamp NULL ,
  tel_fixe_date_maj timestamp NULL ,
  tel_mobile_date_maj timestamp NULL ,
  id_individu bigint,
  france_connect varchar(45),
  sub varchar(255),
  sso_ima int,
  PRIMARY KEY (id_web)
);

java.sql.SQLSyntaxErrorException: unexpected token: (

1 Answers1