mysql spring data error (QUERY)

Question

I made this using Spring Data. I can't figure whats the problem with this sql statement.This query was auto generated by Spring data. It says.

#1064 - 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 'database bit not null, designation 
varchar(255), equipment varchar(255), hardwar' at line 1

My query:

create table joborder (id bigint not null auto_increment, 
                       barPrinter bit not null, 
                       birPermit bit not null, 
                       cabling bit not null, 
                       cashDrawer bit not null, 
                       cashierPrinter bit not null, 
                       ccccods bit not null, 
                       configuration bit not null, 
                       cpu bit not null, 
                       database bit not null, 
                       designation varchar(255), 
                       equipment varchar(255), 
                       hardwareActionTaken varchar(255), 
                       hardwareOthers bit not null, 
                       hardwareRemarks varchar(255), 
                       invoiceNumber varchar(255), 
                       keyboard bit not null, 
                       kitchenPrinter bit not null, 
                       logNumber varchar(255), 
                       magneticCardReader bit not null,
                       mallInterface bit not null, 
                       menu bit not null, 
                       monitor bit not null, 
                       mouse bit not null, 
                       operatingSystem bit not null, 
                       othersActionTaken varchar(255), 
                       othersOthers bit not null, 
                       othersRemarks varchar(255), 
                       powerSupply bit not null, 
                       reportProblemTask varchar(255), 
                       scanner bit not null, 
                       softwareActionTaken varchar(255), 
                       softwareOthers bit not null, 
                       softwareRemarks varchar(255), 
                       storeLocation varchar(255), 
                       storeRepresentative varchar(255), 
                       technician varchar(255), 
                       timeIn varchar(255), 
                       timeOut varchar(255), 
                       transightECLM bit not null, 
                       transightHQ bit not null, 
                       transightPOS bit not null, 
                       uat bit not null, 
                       store_id bigint, 
          primary key (id))

try changing the database column name to something else may be — Rat-a-tat-a-tat Ratatouille, Mar 25 '14 at 05:09

score 3 · Accepted Answer · answered Mar 25 '14 at 05:10

Use this query

CREATE TABLE `joborder` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `barPrinter` BIT(1) NOT NULL,
  `birPermit` BIT(1) NOT NULL,
  `cabling` BIT(1) NOT NULL,
  `cashDrawer` BIT(1) NOT NULL,
  `cashierPrinter` BIT(1) NOT NULL,
  `ccccods` BIT(1) NOT NULL,
  `configuration` BIT(1) NOT NULL,
  `cpu` BIT(1) NOT NULL,
  `database` BIT NOT NULL,
  `designation` VARCHAR(255) DEFAULT NULL,
  `equipment` VARCHAR(255) DEFAULT NULL,
  `hardwareActionTaken` VARCHAR(255) DEFAULT NULL,
  `hardwareOthers` BIT(1) NOT NULL,
  `hardwareRemarks` VARCHAR(255) DEFAULT NULL,
  `invoiceNumber` VARCHAR(255) DEFAULT NULL,
  `keyboard` BIT(1) NOT NULL,
  `kitchenPrinter` BIT(1) NOT NULL,
  `logNumber` VARCHAR(255) DEFAULT NULL,
  `magneticCardReader` BIT(1) NOT NULL,
  `mallInterface` BIT(1) NOT NULL,
  `menu` BIT(1) NOT NULL,
  `monitor` BIT(1) NOT NULL,
  `mouse` BIT(1) NOT NULL,
  `operatingSystem` BIT(1) NOT NULL,
  `othersActionTaken` VARCHAR(255) DEFAULT NULL,
  `othersOthers` BIT(1) NOT NULL,
  `othersRemarks` VARCHAR(255) DEFAULT NULL,
  `powerSupply` BIT(1) NOT NULL,
  `reportProblemTask` VARCHAR(255) DEFAULT NULL,
  `scanner` BIT(1) NOT NULL,
  `softwareActionTaken` VARCHAR(255) DEFAULT NULL,
  `softwareOthers` BIT(1) NOT NULL,
  `softwareRemarks` VARCHAR(255) DEFAULT NULL,
  `storeLocation` VARCHAR(255) DEFAULT NULL,
  `storeRepresentative` VARCHAR(255) DEFAULT NULL,
  `technician` VARCHAR(255) DEFAULT NULL,
  `timeIn` VARCHAR(255) DEFAULT NULL,
  `timeOut` VARCHAR(255) DEFAULT NULL,
  `transightECLM` BIT(1) NOT NULL,
  `transightHQ` BIT(1) NOT NULL,
  `transightPOS` BIT(1) NOT NULL,
  `uat` BIT(1) NOT NULL,
  `store_id` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

you have used "DATABASE" word as column name, so i have applied back ticks(`) to all the column names — PravinS, Mar 25 '14 at 05:17

score 2 · Answer 2 · answered Mar 25 '14 at 05:25

Database is a reserved keyword

you have to use like this Databse

Try this

create table joborder (id bigint not null auto_increment, 
                       barPrinter bit not null, 
                       birPermit bit not null, 
                       cabling bit not null, 
                       cashDrawer bit not null, 
                       cashierPrinter bit not null, 
                       ccccods bit not null, 
                       configuration bit not null, 
                       cpu bit not null, 
                       `database` bit not null, 
                       designation varchar(255), 
                       equipment varchar(255), 
                       hardwareActionTaken varchar(255), 
                       hardwareOthers bit not null, 
                       hardwareRemarks varchar(255), 
                       invoiceNumber varchar(255), 
                       keyboard bit not null, 
                       kitchenPrinter bit not null, 
                       logNumber varchar(255), 
                       magneticCardReader bit not null,
                       mallInterface bit not null, 
                       menu bit not null, 
                       monitor bit not null, 
                       mouse bit not null, 
                       operatingSystem bit not null, 
                       othersActionTaken varchar(255), 
                       othersOthers bit not null, 
                       othersRemarks varchar(255), 
                       powerSupply bit not null, 
                       reportProblemTask varchar(255), 
                       scanner bit not null, 
                       softwareActionTaken varchar(255), 
                       softwareOthers bit not null, 
                       softwareRemarks varchar(255), 
                       storeLocation varchar(255), 
                       storeRepresentative varchar(255), 
                       technician varchar(255), 
                       timeIn varchar(255), 
                       timeOut varchar(255), 
                       transightECLM bit not null, 
                       transightHQ bit not null, 
                       transightPOS bit not null, 
                       uat bit not null, 
                       store_id bigint, 
          primary key (id))

score 1 · Answer 3 · answered Mar 25 '14 at 05:41

you should try this: and check this (http://sqlfiddle.com/#!2/4817b)

CREATE TABLE joborder 
(
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  barPrinter BIT(1) NOT NULL,
  birPermit BIT(1) NOT NULL,
  cabling BIT(1) NOT NULL,
  cashDrawer BIT(1) NOT NULL,
  cashierPrinter BIT(1) NOT NULL,
  ccccods BIT(1) NOT NULL,
  configuration BIT(1) NOT NULL,
  cpu BIT(1) NOT NULL,
  `database` BIT NOT NULL,
  designation VARCHAR(255) DEFAULT NULL,
  equipment VARCHAR(255) DEFAULT NULL,
  hardwareActionTaken VARCHAR(255) DEFAULT NULL,
  hardwareOthers BIT(1) NOT NULL,
  hardwareRemarks VARCHAR(255) DEFAULT NULL,
  invoiceNumber VARCHAR(255) DEFAULT NULL,
  keyboard BIT(1) NOT NULL,
  kitchenPrinter BIT(1) NOT NULL,
  logNumber VARCHAR(255) DEFAULT NULL,
  magneticCardReader BIT(1) NOT NULL,
  mallInterface BIT(1) NOT NULL,
  menu BIT(1) NOT NULL,
  monitor BIT(1) NOT NULL,
  mouse BIT(1) NOT NULL,
  operatingSystem BIT(1) NOT NULL,
  othersActionTaken VARCHAR(255) DEFAULT NULL,
  othersOthers BIT(1) NOT NULL,
  othersRemarks VARCHAR(255) DEFAULT NULL,
  powerSupply BIT(1) NOT NULL,
  reportProblemTask VARCHAR(255) DEFAULT NULL,
  scanner BIT(1) NOT NULL,
  softwareActionTaken VARCHAR(255) DEFAULT NULL,
  softwareOthers BIT(1) NOT NULL,
  softwareRemarks VARCHAR(255) DEFAULT NULL,
  storeLocation VARCHAR(255) DEFAULT NULL,
  storeRepresentative VARCHAR(255) DEFAULT NULL,
  technician VARCHAR(255) DEFAULT NULL,
  timeIn VARCHAR(255) DEFAULT NULL,
  timeOut VARCHAR(255) DEFAULT NULL,
  transightECLM BIT(1) NOT NULL,
  transightHQ BIT(1) NOT NULL,
  transightPOS BIT(1) NOT NULL,
  uat BIT(1) NOT NULL,
  store_id BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
ENGINE=INNODB DEFAULT CHARSET=latin1;

mysql spring data error (QUERY)

3 Answers3