Re: export CSV file through Java JDBC

At 07:20 AM 4/15/2008, you wrote:
>Date: Mon, 14 Apr 2008 09:41:41 -0400
>From: Emi Lu <emilu(at)encs(dot)concordia(dot)ca>
>To: pgsql-sql(at)postgresql(dot)org
>Subject: export CSV file through Java JDBC
>Message-ID: <48035F15(dot)4030302(at)encs(dot)concordia(dot)ca>
>
>Good morning,
>
>Running the following command from command line is ok, but cannot
>export
>a table into a csv file through java JDBC code.
>
>Please help!
>
>
>JAVA code:
>===================
> public static void exec(String command)
> {
> try{
> Process p = Runtime.getRuntime().exec(command);
> p.waitFor();
> p.destroy();
> }catch(Exception e) {
> System.err.println("exec command Error: " +
> e.getMessage());
> }
> }
>
>
>
>SQL Command:
>=================
>psql -U username -d dbName -c "\copy tableName to 'result.csv' with
>CSV "
>
>When call exec(commands);
>
>Nothing happens, result.csv was not created at all?
>
>Thanks a lot!

A couple of thoughts. First, you aren't passing the password in, so
that seems like a problem. Of course, psql won't let you specify a
password on the command line but last I looked you can set an ENV var
before running psql: "PGPASSWORD=[your password here]"

Second, you don't specify a server/port, which means your Pg server is
localhost:5432?

Third, you are not specifying a path to pgsql, so you have to be sure
that it can be found in the path. Now this can be tricky: your Java
application may be running in a context DIFFERENT from your command
prompt. The user/env your Java app is running in will determine what
path vars are available to it - it may not be able to find psql. Try
running "which psql > /tmp/which.txt" in your code above and see what
happens (assuming you're on a box with "which" installed).

Fourth (minor), you don't specify column names in your export which
could result in variable results depending on the create statement -
it's better to specify to guarantee the same results every time.

Fifth, try capturing STDERR and STDOUT, so that if psql or command
shell generate errors you'll know what they are. Maybe Java gives you
that in e.getMessage or maybe you need to put it in your psql command
line.

I'm doing exactly the same thing you are doing but in Ruby/ActiveRecord
so I know this works. It works for me on Windows and Linux, fwiw.

I don't know enough Java to know if the command you are running is the
standard "shell execute" command in Java. If it's not, that's what you
want so change your code that way. You just want java to shell out to
the OS command processor. Be sure when you set your command shell env
var, that this env var persists long enough so that when you run your
psql command it's still in effect. For example this psuedo code might
not work b/c two different child shells are run:

system.exec("export PGPASSWORD=pass1234");
system.exec("psql my command here");

I think you want something more like this psuedo code:

system.set_environment("PGPASSWORD")="pass1234";
system.exec("psql my command here");

I hope this helps,

Steve