Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to execute .sql file using java

 
kiran manohar
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi all,
i am using sql server and oracle. Through java program i want to execute
.sql script file which has its own set of commands. i want to know how to
execute that the sql file.
Thanks in advance
Kiran.
 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I see two ways of doing this.
1) Execute the .sql file using Oracle's command line client (if there is one, that is) and the Runtime.exec() method.
2) Execute the individual statements within the .sql file by reading the .sql file and executing the pieces of SQL using java.sql.Statement. You'll have to do a bit parsing here (for example, semi-colons should be omitted and COMMIT calls should not be executed using Statement).
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Lasse Koskela:
I see two ways of doing this.
1) Execute the .sql file using Oracle's command line client (if there is one, that is) and the Runtime.exec() method.
2) Execute the individual statements within the .sql file by reading the .sql file and executing the pieces of SQL using java.sql.Statement. You'll have to do a bit parsing here (for example, semi-colons should be omitted and COMMIT calls should not be executed using Statement).

I agree with Lasse that you should parse the .SQL file. Just beware of any semi-colons in your data that might cause it to think that it is a delimiter, instead of data. But that should be fairly easy to ensure, since everything is self contained ( not dynamic data ) within the file. The easiest way might be to read the file in to a String and use the String.split(";") method to break up the individual statements. Then loop the resulting String array, executing each individual sql string.
Jamie
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Lasse Koskela:
I see two ways of doing this.
1) Execute the .sql file using Oracle's command line client (if there is one, that is) and the Runtime.exec() method.
2) Execute the individual statements within the .sql file by reading the .sql file and executing the pieces of SQL using java.sql.Statement. You'll have to do a bit parsing here (for example, semi-colons should be omitted and COMMIT calls should not be executed using Statement).

I agree with Lasse that you should parse the .SQL file. Just beware of any semi-colons in your data that might cause it to think that it is a delimiter, instead of data. But that should be fairly easy to ensure, since everything is self contained ( not dynamic data ) within the file. The easiest way might be to read the file in to a String and use the String.split(";") method to break up the individual statements. Then loop the resulting String array, executing each individual sql string.
Jamie
 
kiran manohar
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,
Thanks for responding my question.
i will try.
Regards
kiran
 
Francois Le Droff
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you should also have a look at:
http://sqljc.sourceforge.net/


sqljc is a java program to connect through JDBC to any database engine (with JDBC support) to make simple queries and to execute sql files.

sqljc can execute commands defined by user. These commands are aliases to sqljc internal commands. User can define these aliases in the configuration file. An internal parser provides a simple mechanism to substitute variables in the command; those variables are passed at command invocation time (at execution time).

sqljc can execute SQL-ANSI sql files.


Cheers!
Fran�ois

www.droff.com
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic