aspose file tools*
The moose likes JDBC and the fly likes Execute .sql file using java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Reply locked New topic
Author

Execute .sql file using java

Sirish Kumar Gongal Reddy
Ranch Hand

Joined: Oct 25, 2004
Posts: 109
Hello,

How can I execute .sql(List of .sql files in a resides in local directory)files using java. What I need to do is,

1) Read .sql files one by one from local directory and execute them in oracle environment using java. If it throughs any execption I need to capture that exception.

Note: My .sql file will includes lot of database scripts each file will take around 15 to 20 min to execute.

Many thanks,
Sirish
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

Does the file contain data definition (DDL) scripts, like CREATE TABLE or CREATE PROCEDURE, BEGIN .... END?
Or does it only data manipulation (DML) commands, like INSERT INTO, DELETE FROM and UPDATE?


OCUP UML fundamental and ITIL foundation
youtube channel
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

I have a small project wich is capable to execute SQL script files with different statements including view, sp creations. Merhaps you find it useful

http://sourceforge.net/project/platformdownload.php?group_id=212014


Retire your iPod and start with HD Android music player Kamerton | Minimal J2EE container is here | Light weight full J2EE stack | and build tool | Co-author of "Windows programming in Turbo Pascal"
Sirish Kumar Gongal Reddy
Ranch Hand

Joined: Oct 25, 2004
Posts: 109
Hello,

Thanks for replies. Yes my scripts only includes DML operations like drop the tables and load tables with default tables and check all tables are created properly or not like stuff. No procedure or functions.

My .sql scripts do kind of security check for database before deploying my actual application.

Many thanks,
Sirish
Sirish Kumar Gongal Reddy
Ranch Hand

Joined: Oct 25, 2004
Posts: 109
Hello,

I got the solution. Here is the method I wrote and I found some inputs from sun forums. Thanks Again!

public boolean executeDBScripts(String aSQLScriptFilePath, Statement stmt) throws IOException,SQLException {
boolean isScriptExecuted = false;
try {
BufferedReader in = new BufferedReader(new FileReader(aSQLScriptFilePath));
String str;
StringBuffer sb = new StringBuffer();
while ((str = in.readLine()) != null) {
sb.append(str + "\n ");
}
in.close();
stmt.executeUpdate(sb.toString());
isScriptExecuted = true;
} catch (Exception e) {
System.err.println("Failed to Execute" + aSQLScriptFilePath +". The error is"+ e.getMessage());
}
return isScriptExecuted;
}
Theo Arril
Greenhorn

Joined: Feb 25, 2010
Posts: 2
Hello,
I tried your solution but I got many errors.

First of all you must know that there's no direct way to execute an SQL script file directly through JDBC
You can't load a SQL file into one statement and excute it.

You will have to write your own (very little) parser to split the script in individual statements and execute them one by one.
Splitting in Statements is the biggest challenge here ; so here is my solution :


DatabaseReseter.java


That's my solution, so try and tell me about it !!
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30938
    
158

Theo Arril wrote:First of all you must know that there's no direct way to execute an SQL script file directly through JDBC
You can't load a SQL file into one statement and excute it.

Through JDBC, no. But you can run it as an Oracle command line through Runtime.exec


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Theo Arril
Greenhorn

Joined: Feb 25, 2010
Posts: 2
how about PostgreSQL ?
Harsha Muthy
Greenhorn

Joined: Mar 10, 2010
Posts: 1
@Theo Arril - Awesome code. I tried this and it works for me. Thanks a ton, you really saved me.


- Harsha
Tom Enders
Greenhorn

Joined: Mar 27, 2010
Posts: 4
Theo Arril wrote:Hello,
I tried your solution but I got many errors.

First of all you must know that there's no direct way to execute an SQL script file directly through JDBC
You can't load a SQL file into one statement and excute it.

You will have to write your own (very little) parser to split the script in individual statements and execute them one by one.
Splitting in Statements is the biggest challenge here ; so here is my solution :



That's my solution, so try and tell me about it !!


Your parser does not work on a file that contains transactions. I found this when I tried to run it on a file to create a database which contained triggers. So, I rewrote the parser. Here is the code, the comments should be enough to explain it. So use this just replace the split line in Theo's solution with the following code.
neo Infinite
Greenhorn

Joined: Apr 16, 2012
Posts: 1
Theo Arril wrote:Hello,
I tried your solution but I got many errors.

First of all you must know that there's no direct way to execute an SQL script file directly through JDBC
You can't load a SQL file into one statement and excute it.

You will have to write your own (very little) parser to split the script in individual statements and execute them one by one.
Splitting in Statements is the biggest challenge here ; so here is my solution :


DatabaseReseter.java


That's my solution, so try and tell me about it !!


Thanks for the great solution first!
I tried using it, but the problem is that my .sql file has INSERT queries for multiple tables. And so I am setting the values for each table's primary keys
however it gives an error. Could you please suggest something here?

Please see the stack trace below:
"*** Driver loaded
*** Error : com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
***
*** Error :
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'################################################

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
at com.eurexchange.clear.tradeinquiry.service.TradeInquirySampleDatabase.setUpDatabase(TradeInquirySampleDatabase.java:71)
at com.eurexchange.clear.tradeinquiry.service.TradeInquiryServiceImplTest.before(TradeInquiryServiceImplTest.java:39)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:27)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)"
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Welcome to the ranch Neo
I suspect this means your script is wrong, do you understand the error you are getting?
flo wer
Greenhorn

Joined: May 17, 2012
Posts: 1
Hi there, I found your code an it was pretty helpful. I extended it, so that it can now handle files with multiple queries and comments.
All the class does is to provide a method for reading an sql file and returning a list of queries which can than be executed. As result sets can not be collected in a list that easy, you should take the returned query strings and execute them where you need the result set.

ATTENTION: Since comments are filtered out, you must not use comment signs within column names etc.



To execute the queries, you have to do the setup for the JDBC Connection etc. After that do for example:
 
 
subject: Execute .sql file using java