Importing sql file using psql in a Java aplication

Question

I need to create a method that receives (databaseIp, databaseName, port, user, password, filePath). The idea is to import a sql file in database like terminal:

psql -h databaseIp -p port -U user -d databaseName  
Password for user postgres: password
databaseName=# \\i filePath
databaseName=# \\q

I use the following method to send commands to the terminal

public static String execute(String command) {
    StringBuilder sb = new StringBuilder();
    String[] commands = new String[]{"/bin/sh", "-c", command};
    try {
        Process proc = new ProcessBuilder(commands).start();
        BufferedReader stdInput = new BufferedReader(new InputStreamReader(proc.getInputStream()));

        BufferedReader stdError = new BufferedReader(new InputStreamReader(proc.getErrorStream()));

        String s = null;
        while ((s = stdInput.readLine()) != null) {
            sb.append(s);
            sb.append("\n");
        }

        while ((s = stdError.readLine()) != null) {
            sb.append(s);
            sb.append("\n");
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
    return sb.toString();
}

but It seens that it sends only one command at a time and when the psql asks for passwords the result given is incorrect. How can I send related commands to the terminal using java?

Craig Ringer · Accepted Answer · 2013-08-12 12:37:50Z

Ugh, why would you want to do this?

Use PgJDBC to communicate with PostgreSQL from Java.

If you really must invoke psql, say if you're sourcing an SQL file that uses \ commands, build a command with ProcessBuilder and invoke psql non-interactively in batch mode. You can prompt the user for their password and then pass it to psql in the PGPASSWORD environment variable so you never need to interact with the process, you just launch it and wait for it to terminate.

Something like the (untested):

 cmd = new List<String>();
 cmd.add(path_to_psql);
 cmd.add("-q"); // Quiet mode
 cmd.add("-w"); // no password prompt
 cmd.add("-X"); // ignore any user psqlrc
 cmd.add("-1"); // Run in a single transaction
 cmd.add("-v"); // Set a variable as follows:
 cmd.add("ON_ERROR_STOP=1"); // If anything goes wrong, abort the whole job
 cmd.add("-f"); // input file is:
 cmd.add(input_file_path);
 if (username_arg != null) {
    cmd.add("-U");
    cmd.add(username_arg);
 }
 // and so on for database name, etc, then:

 ProcessBuilder pb = new ProcessBuilder(cmd);
 Map<String, String> env = pb.environment();
 if (psql_password != null) {
     // User supplied a password
     env.put("PGPASSWORD", psql_password);
 }
 // ... blah blah, the usual execute it, wait for it to finish,
 // check return value, etc.

If psql fails to connect the first time with error code 2 you can prompt the user to enter their password and re-try. Unfortunately error code 2 isn't very specific; it will also be returned for errors related to invalid host names, etc, so you might want to capture psql's stderr and display that to the user along with your prompt.

Collectives™ on Stack Overflow

Importing sql file using psql in a Java aplication

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related