Problem with trying to Insert values into User

[James Dekker]

Created a SQL script, InsertUser.sql, on MySQL 5...

INSERT INTO 'user'

('id', 'emailAddress', 'lastAccessTime', 'login_name', 'password', 'registrationDate', 'verified')

VALUES

(1,'[e-mail]','2008-01-13 14:49:01','mj','abc123','2006-02-01', 0x00),
(2,'[e-mail]','2008-01-01 00:00:00','mario','pass','2007-02-01', 0x01),
(3,'[e-mail]','2008-01-13 14:49:01','sk8trgrl','password','2008-02-01', 0x00),
(4,'[e-mail]','2006-10-05 00:00:00','ridley','mypassword','2006-06-11', 0x01),
(5,'[e-mail]','2008-02-25 00:00:00','kerrr','pwd','2008-01-12', 0x00),
(6,'[e-mail]','2008-01-13 14:49:01','astra','pwd','2008-01-13', 0x00),
(7,'[e-mail]','2008-09-15 00:00:00','cameron','90210','2008-09-12', 0x01),
(8,'[e-mail]','2008-02-25 00:00:00','stephen','low','2008-03-15', 0x00),
(9,'[e-mail]','1999-07-26 00:00:00','che','password','1999-04-09', 0x01),
(10,'[e-mail]','2008-01-13 14:49:01','remy','password','2007-06-21', 0x00);

This is the error I receive:


/* MySQL 01:00:12 */ 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 ''id', 'emailAddress', 'lastAccessTime', 'login_name', '
password', 'registrationD' at line 3;


Any help is most appreciated...

[Campbell Ritchie]

Have you missed out VALUES?

[James Dekker]

I don't understand your question... I am MySQL n00b (very sorry to say)...

Copied this script word to word, symbol by symbol out of book...

Don't you see the VALUES declaration?

Thanks for the response...

[Campbell Ritchie]

You have got VALUES in. Sorry. I looked in the wrong place. Try removing all the quote marks in the () after "into user" and before "VALUES".

[Cameron Wallace McKenzie]

That's, like, on page 196 of my book Hibernate Made Easy! My assumption is that most people that buy my technical books rarely get past the first chapter. I'm not sure if anyone has ever read that far?

I'm wondering if the formatting for the book may be chocking MySQL. Maybe the carriage return added after or before 'VALUES' throws off the editor?

I just saw the message, so I'll be testing it.

Heh...

(1,'[e-mail]','2008-01-13 14:49:01','mj','abc123','2006-02-01', 0x00),

That was written over a year ago, but still topical today. Michael Jackson never gets old.

-Cameron McKenzie

[Cameron Wallace McKenzie]

So, that SQL was what Hibernate output to the console after running the Java code given for creating Users. It looks like you need to strip a few single quotes out of there to get it to run in the MySQL editor. Here's a single line that can be put into the MySQL query window and work as is:

 
INSERT INTO examscam.user
(id,emailAddress,lastAccessTime,login_name,password,registrationDate,verified)
VALUES (4,'[e-mail]','2008-01-13 14:49:01','mj','abc123','2006-02-01',0x00);   

The carriage returns have no harming effect. Notice the first brackets () have no single quotes around id, emailAddress, etc. I also preceeded User with examscam, and took the quotes off of user. Some databases need quotes around user as it is a reserved word for most databases.

Here's the full code I used in the MySQL query browser to get things working:

  
INSERT INTO examscam.user 
(id,emailAddress,lastAccessTime,login_name,password,registrationDate,verified) VALUES 
(1,'[e-mail]','2008-01-13 14:49:01','mj','abc123','2006-02-01',0x00), 
(2,'[e-mail]','2008-01-01 00:00:00','mario','pass','2007-02-01',0x01), 
(3,'[e-mail]','2008-01-13 14:49:01','sk8trgrl','password','2008-02-01',0x00), 
(4,'[e-mail]','2006-10-05 00:00:00','ridley','mypassword','2006-06-11',0x01),  
(5,'[e-mail]','2008-02-25 00:00:00','kerrr','pwd','2008-01-12',0x00),  
(6,'[e-mail]','2008-01-13 14:49:01','astra','pwd','2008-01-13',0x00), 
(7,'[e-mail]','2008-09-15 00:00:00','cameron','90210','2008-09-12',0x01), 
(8,'[e-mail]','2008-02-25 00:00:00','stephen','low','2008-03-15',0x00),  
(9,'[e-mail]','1999-07-26 00:00:00','che','password','1999-04-09',0x01), 
(10,'[e-mail]','2008-01-13 14:49:01','remy','password','2007-06-21',0x00);   

The option used in the book was to just type the following into your Java editor and run it, which is the approach I'm assuming most people used. I updated the errata page on my website:

			

			{
			User u = new User();
			u.setLoginName("mj");
			u.setPassword("abc123");
			u.setEmailAddress("[e-mail]");
			u.setVerified(false);
			u.setLastAccessTime(new java.util.Date());
			u.setRegistrationDate(new GregorianCalendar(2006,01,01));
			session.saveOrUpdate(u);
			}{
			User u = new User();
			u.setLoginName("mario");
			u.setPassword("pass");
			u.setEmailAddress("[e-mail]");
			u.setVerified(true);
			u.setLastAccessTime(Date.valueOf("2008-1-1"));
			u.setRegistrationDate(new GregorianCalendar(2007,01,01));
			session.saveOrUpdate(u);
			}{
			User u = new User();
			u.setLoginName("sk8trgrl");
			u.setPassword("password");
			u.setEmailAddress("[e-mail]");
			u.setVerified(false);
			u.setLastAccessTime(new java.util.Date());
			u.setRegistrationDate(new GregorianCalendar(2008,01,01));
			session.saveOrUpdate(u);
			}
			{
			User u = new User();
			u.setLoginName("ridley");
			u.setPassword("mypassword");
			u.setEmailAddress("[e-mail]");
			u.setVerified(true);
			u.setLastAccessTime(new java.util.Date());
			u.setLastAccessTime(Date.valueOf("2006-10-5"));
			u.setRegistrationDate(new GregorianCalendar(2006,5,11));
			session.saveOrUpdate(u);
			}{
			User u = new User();
			u.setLoginName("kerri");
			u.setPassword("pwd");
			u.setEmailAddress("[e-mail]");
			u.setVerified(false);
			u.setLastAccessTime(Date.valueOf("2008-2-25"));
			u.setRegistrationDate(new GregorianCalendar(2007,12,12));
			session.saveOrUpdate(u);
			}{
			User u = new User();
			u.setLoginName("astra");
			u.setPassword("pwd");
			u.setEmailAddress("[e-mail]");
			u.setVerified(false);
			u.setLastAccessTime(new java.util.Date());
			u.setRegistrationDate(new GregorianCalendar());
			session.saveOrUpdate(u);
			}{
			User u = new User();
			u.setLoginName("cameron");
			u.setPassword("90210");
			u.setEmailAddress("[e-mail]");
			u.setVerified(true);
			u.setLastAccessTime(Date.valueOf("2008-9-15"));
			u.setRegistrationDate(new GregorianCalendar(2008,8,12));
			session.saveOrUpdate(u);
			}{
			User u = new User();
			u.setLoginName("stephen");
			u.setPassword("low");
			u.setEmailAddress("[e-mail]");
			u.setVerified(false);
			u.setLastAccessTime(Date.valueOf("2008-2-25"));
			u.setRegistrationDate(new GregorianCalendar(2008,02,15));
			session.saveOrUpdate(u);
			}{
			User u = new User();
			u.setLoginName("che");
			u.setPassword("password");
			u.setEmailAddress("[e-mail]");
			u.setVerified(true);
			u.setLastAccessTime(Date.valueOf("1999-7-26"));
			u.setRegistrationDate(new GregorianCalendar(1999,3,9));
			session.saveOrUpdate(u);
			}{
			User u = new User();
			u.setLoginName("remy");
			u.setPassword("password");
			u.setEmailAddress("[e-mail]");
			u.setVerified(false);
			u.setLastAccessTime(new java.util.Date());
			u.setRegistrationDate(new GregorianCalendar(2007,05,21));
			session.saveOrUpdate(u);
			}
    

Hibernate Made Easy Book Errata

And here's the source code if anyone is looking for it:

Source code from the book Hibernate Made Easy

Thanks for the Monday Morning assignment!

-Cameron McKenzie