Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Execute .sql file using java

 
Sirish Kumar Gongal Reddy
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
D Rog
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Sirish Kumar Gongal Reddy
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Theo Arril
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
how about PostgreSQL ?
 
Harsha Muthy
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Theo Arril - Awesome code. I tried this and it works for me. Thanks a ton, you really saved me.
 
Tom Enders
Greenhorn
Posts: 4
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the ranch Neo
I suspect this means your script is wrong, do you understand the error you are getting?
 
flo wer
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic