Postgresql – Reloading .sql file from java into PostgreSQL – Windows

javapostgresqlscriptingwindows

I am trying to reload a .sql script file into PostgreSQL from my sample Java application.

Script is successfully executed from console as well as from pgAdmin tool. But from Java, it hangs…

This is the code I am using:

C:/Program Files/PostgreSQL/9.2/bin/psql.exe -U postgres -d sampledb -f "H:/Data/samplescript_postgres.sql"

Can anyone help me? I am using PostgreSQL 9.2

Java code:

String path = "H:/Data/samplescript_postgres.sql";
final String cmd = "C:/Program Files/PostgreSQL/9.2/bin/psql.exe -U postgres -d sampledb -f " + "\""+ path + "\" ";

try {
    reloadProcess = Runtime.getRuntime().exec(cmd);
    if(null!=reloadProcess){
        if(reloadProcess.waitFor()==0){
            System.out.println("Reloaded");
        }
    }
} catch (IOException e) {
    e.printStackTrace();
} catch (InterruptedException e) {
    e.printStackTrace();
}

My pg_hba.conf file:

# TYPE  DATABASE    USER    ADDRESS         METHOD  
# IPv4  local   connections:    
host    all         all     127.0.0.1/32    md5 
# IPv6  local   connections:    
host    all         all     ::1/128         md5

Best Answer

This is probably not the best way to do what you want to do. The "proper" way would probably be to execute the SQL commands in your file via jdbc.

That said, that's a long way from where you are now, and we can probably make what you are trying to do work.

The likely problem is that there is a space in the path to psql.exe.

When you use that particular format of exec, java uses a vanilla string tokenizer to split your string into an array, each entry being separated by a space. So it's going to be trying to execute a command "c:/Program" and pass it parameters, the first of which is "Files/PostgreSQL/9.2/bin/psql.exe" and, um, that isn't going to go so well.

Use exec (String[]) instead: http://docs.oracle.com/javase/6/docs/api/java/lang/Runtime.html#exec(java.lang.String[])

This method doesn't apply the tokenizer, because it's accepting an array, so it doesn't think it needs to.

Try this:

String path = "H:/Data/samplescript_postgres.sql";
final String [] cmd = { "C:/Program Files/PostgreSQL/9.2/bin/psql.exe",
       "-U", "postgres",
       "-d", "sampledb",
       "-f", path 
     };

try {
    reloadProcess = Runtime.getRuntime().exec(cmd);
    if(null!=reloadProcess){
        if(reloadProcess.waitFor()==0){
            System.out.println("Reloaded");
        }
    }
} catch (IOException e) {
    e.printStackTrace();
} catch (InterruptedException e) {
    e.printStackTrace();
}

You can see that I've created the array manually. For readability, I've put the option "-U" and the value "postgres" on the same line, but they're separated by a comma.

Related Question