CODEWITHSUNDEEP
javapostgresqlpsql
vinod hy
vinod hy

Reputation: 895

running psql command in a java program

I have a requirement where using java code I need to create/delete/alter postgres tables.

I have written a program as below:

public static void main(String[] args) throws IOException {
        System.out.println("Hello World!");
        Process p = Runtime.getRuntime().exec("psql -U postgres -d testdb -h localhost -p 5433 -f D:\test.sql");
    }

test.sql file looks like below,

Create TABLE MyTable1
(
    VersionNumber VARCHAR(32) NOT NUll
);

Create TABLE MyTable2
(
    VersionNumber VARCHAR(32) NOT NUll
);

Create TABLE MyTable3
(
    VersionNumber VARCHAR(32) NOT NUll
);

problem:

If I run the same psql command:

psql -U postgres -d testdb -h localhost -p 5433 -f D:\test.sql

in command line, it asks for password and the tables get created.

But in java program, there is not provision to provide password. Please let me know on how to achieve it.

Upvotes: 2

Views: 5167

Answers (3)

Gen Eva
Gen Eva

Reputation: 49

Regarding to a_horse_with_no_name answer and using Java-code launch of psql at Windows OS and using ProcessBuilder following slight modification works for me:

    ProcessBuilder builder = new ProcessBuilder();
    String connUrl = "postgresql://user:password@host:port/dbname";
    String sqlFileToProcess = "--file=Disk:\\path\\to\\file.sql";
    builder.command("psql.exe", sqlFileToProcess, connUrl);
    builder.directory(new File("Disk:\\Path\\to\\psql\\containing\\folder"));
    Process process = builder.start();
    int exitCode = 0;
    try {
         exitCode = process.waitFor();
         int len;
         if ((len = process.getErrorStream().available()) > 0) {
             byte[] buf = new byte[len];
             process.getErrorStream().read(buf);
             System.err.println("Command error:\t\""+new String(buf)+"\"");
         }
    } catch (InterruptedException e) {
         e.printStackTrace();
    }
   assert exitCode == 0;

Don`t know why, but if:

-f Disk:\\path\\to\\file.sql

as argument in Java-code throws:

Command error:  "unrecognized win32 error code: 123 psql: error:  Disk:/path/to/file.sql: Invalid argument
"

(pay attention to redirection of slashes and backslashes in otput and input)

Upvotes: 0

user330315
user330315

Reputation:

You can use a connection URL instead: 

psql -f d:\test.sql postgresql://postgres:password@localhost:5433/testdb

Upvotes: 2

Aleks G
Aleks G

Reputation: 57346

First of all, it would be better to actually use JDBC to connect to the database and run your SQL statements. If you're set on using command-line psql, you can use PGPASSWORD environment variable to set the password:

String command = "psql -U postgres -d testdb -h localhost -p 5433 -f D:\test.sql";
String[] envVars = { "PGPASSWORD=yourpassword" };
Process p = Runtime.getRuntime().exec(command, envVars);

You can read the password from the stdin if necessary. But, again, it's better to do this over JDBC.

Upvotes: 0

Related Questions