Running SQL script files with Java

[Jooky68]

I have two files that contain a bunch of SQL statments. I need to run them through a Java class. Problem is that I can't figure out a way to run a script file through the JDBC. One file contains create statments and the other has insert statments. I have tried to read the files into a string and than use the executeupdate or execute command, but that would not work either. It seems I might need a way of seperating each SQL statment, which would be possible, but I find it hard that there is not an easier way. So if anyone knows an easier way, it would be greatly appreciated.

 

[sedj]

My first opinion on this is that should a client be doing so much db-centric structure manipulation ? I would say no. If I were you, I would write the scripts as Stored Procedures in the database, and then use a CallableStatement to call these procedures. This is the cleanest, most efficient and safest way of doing things like this.

However, if this is not possible, then I would loop through the .sql file using a BufferedReader, and add each SQL statement (or line) to a Statement object using the addBatch() method.
When all statements are completed, I would then use exectuteBatch() to run the SQL.

But, I would really go with the first option, as th second really is quite messy.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Jooky68]

I am using MySQL, so I cannot create stored procedures(Version 5.0 has them, but that is a current Alpha release). I wish it did because that would make things much easier. I tried the above executeBatch() method, but It give me back an error saying that I have a syntax error with my command(SQL command that is). The script file works perfectly when run from the command prompt or within MySQL. I have have printed out what the file is after being read in from the file and it matches the SQL script file perfectly that I can see.

Anyone have any suggestions?

 

[sedj]

Are you sure you have each SQL statement contained on each line ?

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[Jooky68]

Yup, even took out all but one statement, which was one one line, which worked. Than added one more command and it wouldn't work. Still same thing. I know it is not a good method, but I just ended up looking through the file and after each readline executeUpdate on the line, which works.

Thanks for the help though. I looked into executeBatch() and do not see why I keep getting those errors. It seems like it should work from the definition.

 

[sedj]

Can you post the SQL line and the exact error ?

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk