hey guys,
im doing a databases project and im a little stuck
this statement wont work(its part of a jdbc) and i get the following error messahe, any suggestions as to why?

stmt.executeUpdate("CREATE TABLE LOCATION (" +
						"serial VARCHAR(10)," +
						"notice TINYINT(10),"+
						"intransit ENUM('T','D')," +
						"dtnoticeissued DATETIME," +
						"lat NUMERIC(8,4)," +
						"long NUMERIC(8,4)," +
						"comment STRING(100)" +
						"FOREIGN KEY(serial) REFERENCES PACKAGE(serial) ON DELETE CASCADE" +
						")Engine=InnoDB;");

OR

CREATE TABLE LOCATION (
	serial VARCHAR(),
	notice TINYINT(),
	inTransit ENUM('T','D'),
	dateTimeNoticeIssued DATETIME(),
	lat NUMERIC(4,8),
	long NUMERIC(4,8),
	comment VARCHAR(100),
	FOREIGN KEY(serial) REFERENCES PACKAGE(serial) ON DELETE CASCADE
)Engine=InnoDB;

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 'long NUMERIC(8,4),comment STRING(100)FOREIGN KEY(serial) REFERENCES PACKAGE(seri' at line 1

For starters your datatypes should be float/decimal/double when using numeric values

THIS WILL WORK

CREATE TABLE IF NOT EXISTS `location` (
  `serial` int(10) NOT NULL,
  `notice` tinyint(10) NOT NULL,
  `intransit` enum('T','D') NOT NULL,
  `dtnoticeissued` datetime NOT NULL,
  `lat` float(8,4) NOT NULL,
  `long` float(8,4) NOT NULL,
  `comment` varchar(100) NOT NULL
) ENGINE=InnoDB;

F.Y.I. - the default values for float are (10, 4)

o ok cool cheers mate, thanks 4 the help! :)

"long" is a keyword. Escaping it with the back tick fixed your error.

yeh that was also another error which i finally figured out, cheers 4 the help

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.