Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
Please show the Java code you are using to invoke the command. –  Craig Ringer Nov 27 '12 at 6:23
2  
waits for password? –  sufleR Nov 27 '12 at 7:39
    
When it 'hangs', does it produce any (error) meassages? Does it start and hang at a later point in execution or looks like doing nothing at all? –  dezso Nov 27 '12 at 7:39
    
The sql script contains 7 tables, first 2 are only loaded from java. –  Haseena Nov 27 '12 at 8:00
    
How I give password with the above command? –  Haseena Nov 27 '12 at 8:01

2 Answers 2

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.

share|improve this answer
    
Do you know any method in Java for running batch SQL against PostgreSQL? –  dezso Nov 29 '12 at 10:10
    
This looks like a pretty good tutorial: zetcode.com/db/postgresqljavatutorial –  GregHNZ Nov 30 '12 at 7:58

Using psql this way isn't quite what it was developed for, so you may be surprised when it doesn't behave the same as called from the command line.

As sufleR suggested in a comment, it is possible that psql waits for a password. But you don't have any means to provide one... So, you have to avoid using a password by adding a row to your pg_hba.conf file:

local   sampledb    postgres    trust

(Rows starting with local generally go before rows starting with host.)

If I understand you correctly, this is only a test setup, and then it is OK. In production applications it is a bad idea to connect to a database with the postgres user. Ideally, every application-database pair should have its own user (or role in database terms).

share|improve this answer
    
From commandline also I cannot give any password. But it works properly –  Haseena Nov 27 '12 at 12:22
    
Is this issue with jdbc? –  Haseena Nov 27 '12 at 12:29
    
@Haseena from command line it looks up the password in the .pgpass file. I don't think that Java can do that. (And it looks like there is nothing JDBC does in this piece of code.) Have you tried what I've suggested? –  dezso Nov 27 '12 at 12:38
    
Tried.... But when I trying to reopen the pgAdmin tool, it shows an error like : Error connecting to the server: FATAL: could not load pg_hba.conf –  Haseena Nov 27 '12 at 12:48
    
Why pgAdmin? Otherwise, you have to reload PostgreSQL config to get the new pg_hba.conf working. –  dezso Nov 27 '12 at 13:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.