How to execute sql file from java

Question

I have an ORACLE SQL sctipt with several queries and tables, and I wan to run that script from my java program at the starting of the program to ensure that everything is on the right place. I found a code to run the script, but it doesn't work for some reason. Can anyone provide me samples so that I can follow it.

This is what I found :

try {
    String line;
    Process p = Runtime.getRuntime().exec ("psql -U sas -d oracle -h @localhost -f Lab_05_Tables.sql");
    BufferedReader input =new BufferedReader(new InputStreamReader(p.getInputStream()));

    while ((line = input.readLine()) != null) {        
        System.out.println(line);
    }
    input.close();
}
catch (Exception err) {
    err.printStackTrace();
}

But it doesn't work though.

Error

java.io.IOException: Cannot run program "psql": CreateProcess error=2, The system     
cannot find the file specified

Answer 1

It would be much better, if you have resources, to port SQLs from your script into Java program itself.

See Java JDBC tutorial.

Answer 2

You are trying to run external program from your code, which is not a good solution.

If you use Ant, you can use sql task in Ant: http://ant.apache.org/manual/Tasks/sql.html

If you don't use Ant, you should just use the regular jdbc connection.

Answer 3

It is possible you're running EnterpriseDB Postgres Plus Advanced Server, which would allow you to run Oracle code using psql. The filename "Lab_05_Tables.sql" suggests an educational environment, so forgive me if it seems I'm unduly treating you as a novice.

Before getting this to work in Java, you should get your executed statement to run by itself at the command line.

psql -U sas -d oracle -h @localhost -f Lab_05_Tables.sql

Lots of things may be wrong here and you need to figure that out. Your command line suggests that you are expecting to run the java program on the same server that is running your database server and has the database client installed, and that you're using the user name "sas" to log into a database named "oracle" with no password required. If any of that is not correct then you should find help getting set up on your database before working on your Java app. I would omit the "-h @localhost".

If you're certain all that's working and the scope of your problem is just getting Java to execute your command, then you may just need to add psql to your PATH environment variable.