• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Execute .sql file using java

 
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 472
Objective C Ubuntu Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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;
}
 
Greenhorn
Posts: 2
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 !!
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
how about PostgreSQL ?
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@Theo Arril - Awesome code. I tried this and it works for me. Thanks a ton, you really saved me.
 
Greenhorn
Posts: 4
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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)"
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic