SQL syntax error initializing DB before unit Tests

Question

I have created a .sql for preparing my DB for testing.

here a part of the .sql which produces the error:

DROP TABLE IF EXISTS announcement;
CREATE TABLE announcement (
   id int(11) NOT NULL AUTO_INCREMENT,
   date datetime DEFAULT NULL,
   title varchar(255) DEFAULT NULL,
   content mediumtext,
   PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

LOCK TABLES announcement WRITE;
INSERT INTO announcement VALUES (1,'2015-07-29 10:59:16','Test Anno ','some text');
UNLOCK TABLES;

when executed in Mysql Workbench this scipt works fine.

however when executed via hibernate:

String sqlScript = readFile("dump.sql", Charset.forName("UTF8"));
//System.err.println(sqlScript);
Query q = em.createNativeQuery("BEGIN " + sqlScript + "END;");
q.executeUpdate();

I get:

2015-08-06 16:15:55 ERROR SqlExceptionHelper:146 - 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 'DROP TABLE IF EXISTS announcement;
CREATE TABLE announcement (
  id int(11) NO' at line 1

I am using:

Properties jpaProperties = new Properties();
jpaProperties.put("hibernate.dialect","org.hibernate.dialect.MySQL5Dialect");
jpaProperties.put("hibernate.enable_lazy_load_no_trans", true);

Can someone help me out here? thanks.

What happens if you remove the NOT NULL after int(11)? It sounds like SQL isn't liking that. With AUTO_INCREMENT on, you don't need the NOT NULL anyway. — Lee Presswood
– Lee Presswood, Commented Aug 6, 2015 at 14:25
just wondering do you need a space before END;?..... ` END;` — Ankit
– Ankit, Commented Aug 6, 2015 at 14:25
I suspect you need to execute statements in your .sql file one by one. — mustaccio
– mustaccio, Commented Aug 6, 2015 at 14:27
-LeePresswood NOT NULL needed here, does not change the behaviour though. -Ankit wouln'd harm to add it. -mustaccio I hope I will not need to do that.. would make the use of a .sql scipt useless. I'll look in to that. — bwright
– bwright, Commented Aug 6, 2015 at 14:33
@mustaccio If I only execute the first statement 'Drop table ..' I also get the same error. 2015-08-06 16:36:45 ERROR SqlExceptionHelper:146 - 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 'DROP TABLE IF EXISTS announcement; END' at line 1 — bwright
– bwright, Commented Aug 6, 2015 at 14:39

Community · Accepted Answer · 2017-05-23 12:22:28Z

0

As stated by mustaccio BEGIN and END statements are not allowed outside of stored procedueres. See: Hibernate multiple native SQL statements

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Sep 28, 2015 at 7:28

bwright

9561 gold badge11 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL syntax error initializing DB before unit Tests

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related